Add multiple values to a column SQL server

Question

My task it to put black, white and gray lists in the database. Whenever someone notices a new word, it should be simply added to the database. These lists have few words in them. So I am trying to add a content that contains a few words.

Above you can see my tables. I want to query a few words to one ContentID. It should look like this:

 ContentID                   Words
 1                      "Password", "Login", "Configure"
 2                      ".nl", ".be", ".com"

How do I query multiple values to one ContentID?

by using WHERE ContentID = 1 AND Words In ('Password','Login','Configure') ? - not getting your question. :( — Pedram
– Pedram, Commented Dec 23, 2015 at 12:24
@curiousguy Oh I am sorry for my hazy question. I want to store multiple values to one key? A content has an ID and a set of words. I only know how to store one value to the attribute word. But I do not know how to store multiple words in one column. I hope I explained it better. — user3599415
– user3599415, Commented Dec 23, 2015 at 12:41
Don't! This is a very bad idea, never put multiple values in a single table cell. What are you trying to accomplish? — HoneyBadger
– HoneyBadger, Commented Dec 23, 2015 at 12:55
@HoneyBadger Oh oke! My task it to put black, white and gray lists in the database. Whenever someone notices a new word, it should be simply added to the database. These lists have few words in them. So I am trying to add a content that contains a few words. And when I am done I am going to join these values(words) To a list with the correct name. I hope that I explained it well:S — user3599415
– user3599415, Commented Dec 23, 2015 at 12:59

eftpotrm · Accepted Answer · 2015-12-23 13:31:03Z

Don't do this.

Your design is flawed. Fields are designed to contain one atomic data item only. Trying to cram multiple items into a single field restricts the system's growth and makes things much harder to work with going forwards.

If you need to do something along these lines, instead create your tables like (very rough sketch!):

CREATE TABLE List(ListID int, Name nvarchar(100));
CREATE TABLE Content(ContentID int, ListID int);
CREATE TABLE ContentWords(ContentID int, Word nvarchar(100), Position int);

You can still read all the words for a single content record by joining the two tables, but there's a whole host of other things that are now open to you. If you find you particularly need to read them as a table of comma separated lists for the application, you can even do this using the FOR XML PATH('') trick - see http://sqlandme.com/2011/04/27/tsql-concatenate-rows-using-for-xml-path/ for more info.

HoneyBadger · Accepted Answer · 2015-12-23 13:36:17Z

(Cont'd from comment) You can do something like this:

DECLARE @List TABLE (ID INT, ListName VARCHAR(MAX))
DECLARE @Word TABLE (ID INT, ListID INT, Word VARCHAR(MAX))

INSERT @List (ID, ListName)
VALUES  (0 , 'White'),
        (1 , 'Black')


INSERT @Word (ID, ListID, Word)
VALUES  (0, 0, 'Good'),
        (1, 0, 'Better'),
        (2, 0, 'Best'),
        (3, 1, 'Bad'),
        (4, 1, 'Worse'),
        (5, 1, 'Worstest')


SELECT      L.ID
,           L.ListName
,           STUFF(( SELECT ',' + W.Word AS [text()]
                    FROM @Word AS W
                    WHERE w.ListID = L.ID
                    FOR XML PATH('')
                        ), 1, 1, '' ) test
FROM        @List L

Which results in:

ID  ListName    test
0   White   Good,Better,Best
1   Black   Bad,Worse,Worstest

Very roughly put, but think along these lines.

Collectives™ on Stack Overflow

Add multiple values to a column SQL server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related