有几种方法可以将数据从行转换为列.

There are a few ways that you can transform the data from rows into columns.

由于使用的是SQL Server 2008，因此可以使用PIVOT函数.

Since you are using SQL Server 2008, then you can use the PIVOT function.

我建议使用row_number()函数来辅助数据透视.如果您拥有已知数量的值，则可以对查询进行硬编码:

I would suggest using the row_number() function to assist in pivoting the data. If you have a known number of values, then you could hard-code the query:

select user, category1, category2, category3, category4
from
(
  select [user], category,
    'Category'+cast(row_number() over(partition by [user] 
                                      order by [user]) as varchar(3)) rn
  from yt
) d
pivot
(
  max(category)
  for rn in (category1, category2, category3, category4)
) piv;

请参见带演示的SQL小提琴.

对于您的情况，您说过您将有数量不确定的值，需要将其作为列.在这种情况下，您将需要使用动态SQL生成要执行的查询字符串:

For your situation you stated that you will have an unknown number of values that need to be columns. In that case, you will want to use dynamic SQL to generate the query string to execute:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME('Category'+cast(row_number() over(partition by [user] 
                                                                      order by [user]) as varchar(3))) 
                    from yt
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT [user],' + @cols + ' 
              from
              (
                select [user], category,
                  ''Category''+cast(row_number() over(partition by [user] 
                                                    order by [user]) as varchar(3)) rn
                from yt
              ) d
              pivot 
              (
                  max(category)
                  for rn in (' + @cols + ')
              ) p '

execute(@query)

请参见带有演示的SQL小提琴.两者都给出结果:

See SQL Fiddle with Demo. Both give a result:

|  USER |  CATEGORY1 | CATEGORY2 | CATEGORY3 | CATEGORY4 |
----------------------------------------------------------
| Bruce |     Laptop |      Beer |    (null) |    (null) |
| Chuck | Cell Phone |    (null) |    (null) |    (null) |
|  Jack |      Shoes |       Tie |     Glass |    (null) |
| Peggy |       Shoe |     Skirt |       Bat |       Cat |