CODEWITHSUNDEEP
sqlsql-server
user3599415
user3599415

Reputation: 283

Add multiple values to a column SQL server

enter image description here

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?

Upvotes: 1

Views: 12477

Answers (2)

HoneyBadger
HoneyBadger

Reputation: 15140

(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.

Upvotes: 1

eftpotrm
eftpotrm

Reputation: 2271

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.

Upvotes: 2

Related Questions