SELECT *
FROM (
SELECT 下载用户,商品,日期,
'商品'+Convert(varchar(11),
ROW_NUMBER() OVER(PARTITION BY 下载用户,日期 ORDER BY ID)
) AS col
FROM table1
) t
PIVOT (
MAX(商品)
FOR col IN ([商品1],[商品2],[商品3])
) p
下载用户 日期 商品1 商品2 商品3
-------- ---------- ----- ----- -----
b 2014/12/14 辛 干 NULL
c 2014/12/14 己 为 甲
d 2014/12/14 戊 NULL NULL
e 2014/12/14 壬 NULL NULL
b 2014/12/15 为 NULL NULL
c 2014/12/15 为 戊 NULL
e 2014/12/15 乙 NULL NULL
FROM (
SELECT 下载用户,商品,日期,
'商品'+Convert(varchar(11),
ROW_NUMBER() OVER(PARTITION BY 下载用户,日期 ORDER BY ID)
) AS col
FROM table1
) t
PIVOT (
MAX(商品)
FOR col IN ([商品1],[商品2],[商品3])
) p
下载用户 日期 商品1 商品2 商品3
-------- ---------- ----- ----- -----
b 2014/12/14 辛 干 NULL
c 2014/12/14 己 为 甲
d 2014/12/14 戊 NULL NULL
e 2014/12/14 壬 NULL NULL
b 2014/12/15 为 NULL NULL
c 2014/12/15 为 戊 NULL
e 2014/12/15 乙 NULL NULL
if object_id('tempdb..#t')is not null drop table #t
go
CREATE TABLE #t(ID INT,[下载用户] CHAR(2),[商品] CHAR(10),[日期] DATETIME,[商品2] CHAR(10),)
INSERT INTO #t(ID ,[下载用户] ,[商品] ,[日期] )
SELECT 1,'b','辛','2014-12-14' union all
SELECT 2,'b','干','2014-12-14' union all
SELECT 3,'c','己','2014-12-14' union all
SELECT 4,'c','为','2014-12-14' union all
SELECT 5,'c','甲','2014-12-14' union all
SELECT 6,'d','戊','2014-12-14' union all
SELECT 7,'e','壬','2014-12-14' union all
SELECT 8,'b','为','2014-12-15' union all
SELECT 9,'c','为','2014-12-15' union all
SELECT 10,'c','戊','2014-12-15' union all
SELECT 11,'e','乙','2014-12-15'update #t set [商品2]=[商品]
go
---方法一
declare @name varchar(500)
select @name = isnull(@name + ',','') + rtrim([商品])
from #t
GROUP BY [商品]
exec ('select * from (select [下载用户],[日期],[商品],[商品2] from #t ) a
pivot (max([商品]) for [商品2] in (' + @name + ')) b')
---方法二
SELECT B.[下载用户],B.[日期],LEFT(UserList,LEN(UserList)-1) as [商品]
FROM (SELECT [下载用户],[日期],
(SELECT rtrim([商品])+',' FROM #t WHERE [下载用户]=A.[下载用户] and [日期]=A.[日期] FOR XML PATH('')) AS UserList
FROM #t A
GROUP BY [下载用户],[日期]) B 解决完毕,结贴给分