Can I create this in sql server?

Question

I have table like following:

Locaiton Val1   Val2
L1       1      2
L1       0      5
L1       4      1
L1       7      8
L2       1      3
L2       6      1
L2       2      11
L2       0      2
L2       6      3
L2       8      4
L2       1      1

I want to convert it like following: Location Val1 Val2

Location    Val1            Val2
L1          1,0,4,7,1,6     2,5,1,8,3,1
L2          2,0,6,7,1       11,2,3,4,1

You need to use `STUFF` – Mahedi Sabuj May 18 '17 at 11:14 — Mahedi Sabuj, May 18 '17 at 11:14

score 0 · Answer 1 · answered May 18 '17 at 11:14

This is a pain. You need to do the string concatenation trick twice:

select t.location,
       stuff( (select ',' + cast(val1 as varchar(255))
               from t t2
               where t2.location = t.location
               for xml path ('')
              ), 1, 1, ''
            ) as val1s,
       stuff( (select ',' + cast(val2 as varchar(255))
               from t t2
               where t2.location = t.location
               for xml path ('')
              ), 1, 1, ''
            ) as val2s
from (select distinct location from t) t;

Note that the ordering of the ids in each list is indeterminate. This is how SQL works. If you want them in a particular order, you need to add an ORDER BY to the subqueries. Your sample data has no obvious column for ordering.

score 0 · Accepted Answer · answered May 18 '17 at 11:15

You can use stuff as below

select [Location], stuff ((
select ',' + val1 from #Table1 where [Location] = t.[Location] 
for xml path('')
),1,1,'') as Val1,
stuff ((
select ',' + val2 from #Table1 where [Location] = t.[Location] 
for xml path('')
),1,1,'') as Val2
from #Table1 t
group by [Location]

If it is sql server 2017 or vnext you can use string_agg

select [Location], val1 = string_agg(val1,','), val2 = string_agg(val2,',')
    from #table1 
group by [Location]

score 0 · Answer 3 · answered May 18 '17 at 11:15

You can it by the following query,

    SELECT  Location
        ,STUFF((SELECT DISTINCT ','+CAST(Val1 AS NVARCHAR)
                FROM YourTable t
                WHERE t.Location = Location),1,1,'')    AS Val1
        ,STUFF((    SELECT DISTINCT ','+CAST(Val2 AS NVARCHAR)
            FROM YourTable t
            WHERE t.Location = Location),1,1,'')    AS Val2
FROM YourTable
GROUP BY Location

Can I create this in sql server?

3 Answers3