SQL Cross Tab Function

Question

Hi Dear All My friends,

I want to ask one thing about sql cross tab function.Currently, I am using sql 2008 express version and my table structure is like below.

UserID     Str_Value
1             A
1             B
1             C
2             A
2             B
3             D
3             E

I want to get like this .

UserID     Str_Value
1             A,B,C
2             A,B
3             D,E

I don't want to use cursor.Is there any function for that one?
Please give me the right way.I really appreciate it.

Thanks.

Best Regards,

Chong

This has been asked many many times, do a quick search with the tags sql-server and group-concat — Lamak, Mar 19 '12 at 16:29
possible duplicate of [Simulating group_concat MySQL function in MS SQL Server 2005?](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-ms-sql-server-2005) — Lamak, Mar 19 '12 at 16:30

score 3 · Answer 1 · answered Mar 19 '12 at 16:33

SELECT UserId, LEFT(Str_Value, LEN(Str_Value) - 1) AS Str_Value
FROM YourTable AS extern
CROSS APPLY
(
    SELECT Str_Value + ','
    FROM YourTable AS intern
    WHERE extern.UserId = intern.UserId
    FOR XML PATH('')
) pre_trimmed (Str_Value)
GROUP BY UserId, Str_Value

score 3 · Accepted Answer · answered Mar 19 '12 at 17:13

Hope this helps. You can comment ORDER BY T1.Str_Value if not needed and set the nvarchar(500) size as required

SELECT DISTINCT T1.UserId,
Stuff(
      (SELECT N', ' + T2.Str_Value 
       FROM t T2
       WHERE T2.userId = T1.userid
       ORDER BY T2.Str_Value
       FOR XML PATH(''),TYPE).value('text()[1]','nvarchar(500)'),1,2,N'') 
        AS Str_Value
FROM t T1

score 1 · Answer 3 · answered Mar 19 '12 at 16:34

1

Try this:

SELECT DISTINCT
    t1.UserID,
    Values = SUBSTRING((SELECT ( ', ' + t2.Str_Value)
                   FROM dbo.Users t2

                   ORDER BY 
                      t2.Str_Value
                   FOR XML PATH( '' )
                  ), 3, 4000 )FROM  dbo.Users  t1
GROUP BY t1.UserID

answered Mar 19 '12 at 16:34

Paul Grimshaw

19,894
6
40
59

`stuff(str,1,1,'')` instead of `substring` – Denis Valeev Mar 19 '12 at 17:47

score 0 · Answer 4 · answered Mar 19 '12 at 16:39

create table #temp
(
    userid int,
    str_value varchar(1)
)

insert into #temp values (1, 'A')
insert into #temp values (1, 'B')
insert into #temp values (1, 'C')

insert into #temp values (2, 'A')
insert into #temp values (2, 'B')

insert into #temp values (3, 'D')
insert into #temp values (3, 'E')

select userid, left(x.str_value, len(x.str_value) -1) as str_value
from #temp t
cross apply
(
    select str_value + ','
    FROM #temp t1
    where t.userid = t1.userid
    for xml path('')
) x (str_value)
group by userid, x.str_value

drop table #temp

SQL Cross Tab Function

4 Answers4

Linked