SQL Cross Tab Function

问题:

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

回答1:

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

回答2:

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

回答3:

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

回答4:

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