T-SQL / SQL Server logical select

Question

I have a table that is broken out into many rows. The table looks like the below:

Table admissions:

PersonID    Field_Value 
------------------------
  1775      UNDECIDED
  1775      PC-STJ
  1775      N/A
  1775      DEMENTIA
  1775      STJ
  1775      0
  1775      N/A
  1775      N/A
  1775      N

I to have a view that will capture the information on one line like the below.

Please note that the above is what it gives me in the database unlike the UI.

1775,  UNDECIDED, PC -STJ, N/A, DEMENTIA, STJ, 0, N/A, N/A, N

How can I capture the information on 1 row? like 1775, UNDECIDED, PC -STJ, N/A, DEMENTIA, STJ, 0, N/A, N/A, N — user44565, Apr 28 '15 at 19:26
Do you happen to have a simple example of Stuff and how I can use it? — user44565, Apr 28 '15 at 19:41
Yes....@TabAlleman linked an excellent example of this technique. I am not sure how much simpler it could be. It is almost exactly the same thing you are doing. Unless one of us coded it for you it isn't going to be an easier. — Sean Lange, Apr 28 '15 at 19:52

Sagar Joon · Answer 1 · 2015-04-29T02:36:03.653

0

Try this :

    SELECT o.PersonID , fieldvalues = STUFF(
    (SELECT ',' + field_value 
          FROM admissions i
          where i.PersonID= o.PersonID
          FOR XML PATH (''))
         , 1, 1, '')
    from admissions o. Group by  o.PersonID ;

edited Apr 29 '15 at 02:36

answered Apr 28 '15 at 19:54

Sagar Joon

1,387
14
23

You missed group by person – Giorgi Nakeuri Apr 28 '15 at 20:28

score 0 · Accepted Answer · answered Apr 28 '15 at 20:42

DECLARE @Admission TABLE (PersonID INT, Field_Value VARCHAR(25));
INSERT INTO @Admission
    VALUES  (1775,'UNDECIDED'),
            (1775,'PC-STJ'),
            (1775,'N/A'),
            (1775,'DEMENTIA'),
            (1775,'STJ'),
            (1775,'0'),
            (1775,'N/A'),
            (1775,'N/A'),
            (1775,'N');

SELECT  PersonID,
        STUFF(
                (
                    SELECT ', ' + Field_Value
                    FROM @Admission
                    WHERE PersonID = A.PersonID
                    FOR XML PATH('')
                ),
        1,2,'') AS Field_Values
FROM @Admission A
GROUP BY PersonID

Results:

PersonID    Field_Values
----------- ------------------------------------------------------
1775        UNDECIDED, PC-STJ, N/A, DEMENTIA, STJ, 0, N/A, N/A, N

T-SQL / SQL Server logical select

2 Answers2