如何在SQL Server中的单列中存储多行数据？ [英] How to store multiple line data in single column in SQL server?

问题描述

我想在单列中存储多行数据。

i在sql server中有一个名为answer的列，我想存储这样的数据。



textline1，

tesxtline2，

等



（喜欢这个网页的答案店）多行，然后我们可以以相同的形式访问它）



我尝试了什么：



创建表tablename

（

名称varchar（20），

回答varchar（max），

）

i want to store multiple line data in singe column.
i have a column named "answer" in sql server and i want to store data like this.

textline1,
tesxtline2,
etc

(like this page answers store multiple line and after we can access that in same form)

What I have tried:

create table tablename
(
name varchar(20),
answer varchar(max),
)

推荐答案

您可以将任何字符串存储在 varchar 列中，数据库引擎不会区分多行字符（\ r \ n）。



您的数据查看器负责格式化。

You can store any string in a varchar column, the database engine does not distinguish the multi line characters (\r \n).

Your data viewer is responsible for the formatting.

简单的解决方案是：不要。

虽然这很容易做到 - 用逗号，分号或其他特殊字符分隔日期 - 这对于人来说很容易数据。一旦你想要检索它，处理它，或（gawd禁止）改变它，它很快就会成为一个PITA。而且由于你保存了一次，但是处理了很多次，因此首先正确地完成这项工作是一个好得多的好主意。

设置第二个表，带有IDENTITY Id列，返回原始表的外键，以及要保存的数据的单个条目。然后在这个表中创建多个条目，稍后使用JOIN将它们检索到第一个表格。

听起来更复杂，但是在你必须开始从逗号分隔列表中删除条目并添加之前SQL中的一个新值，而不是只发出一个DELETE和一个INSERT，或者一个UPDATE命令，你不知道你的方案有多复杂。

The simple solution is: don't.
Though it's easy enough to do - separating date with a comma, or semicolon, or other "special" character - it'#s only easy for the person saving the data. As soon as you want to retrieve it, process it, or (gawd forbid) change it, it quickly becomes a PITA to work with. And since you save it once, but process it many, many times, it's a much, much better idea to do the job properly in the first place.
Set up a second table, with an IDENTITY Id column, a foreign key back to the original table, and a single entry for the data you want to save. you then create multiple entries in this table, and retrieve them later using a JOIN onto the first table.
It sounds more complex, but until you have to start removing an entry from a comma delimited list and adding a new value in SQL instead of just issuing a DELETE and an INSERT, or an UPDATE command you don't have any idea how complex your scheme is going to make things.

你可以'正如Mehdi所说的那样。

另一种方法是将它们放在以逗号分隔的列表中，但不要。请阅读存储的优秀答案数据库列中的分隔列表真的那么糟糕吗？ - 堆栈溢出 [ ^ ]。

You can't as Mehdi has said.
Another way is place them in a comma separated list, but don't. Read this excellent answer from Is storing a delimited list in a database column really that bad? - Stack Overflow[^].

这篇关于如何在SQL Server中的单列中存储多行数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！