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_Val

Solution 1:

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

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

Solution 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)
GROUPBY UserId, Str_Value

Solution 3:

Try this:

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

                   ORDERBY 
                      t2.Str_Value
                   FOR XML PATH( '' )
                  ), 3, 4000 )FROM  dbo.Users  t1
GROUPBY t1.UserID

Solution 4:

createtable #temp
(
    userid int,
    str_value varchar(1)
)

insertinto #temp values (1, 'A')
insertinto #temp values (1, 'B')
insertinto #temp values (1, 'C')

insertinto #temp values (2, 'A')
insertinto #temp values (2, 'B')

insertinto #temp values (3, 'D')
insertinto #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)
groupby userid, x.str_value

droptable #temp