sql server2005行列转换
0
[已关闭问题]
用这样的数据
UserID date menu
1 2008-10-10 ddd
1 2008-10-11 ddd谁
1 2008-10-12 ddd的
2 2008-10-10 ddds
2 2008-10-11 ddd是
怎么转换成
userID 2008-10-10 2008-10-11 2008-10-12
1 ddd ddd谁 ddd的
2 ddds ddd是
其他回答(3)
0
参考一下这篇吧，不过你那个似乎比这篇要更复杂一些。
http://www.cnblogs.com/eaglet/archive/2009/07/27/1531945.html
0
create table #TABLE (Area varchar(10), Date varchar(10), Count int)
go
insert into #TABLE (Area, Date, Count)
values ('BeiJing', '2007-01-01',100000)
insert into #TABLE (Area, Date, Count)
values ('GuangZhou', '2007-01-01',200000)
insert into #TABLE (Area, Date, Count)
values ('BeiJing','2007-02-19',300000)
insert into #TABLE (Area, Date, Count)
values ('GuangZhou','2007-02-19',400000)
insert into #TABLE (Area, Date, Count)
values ('BeiJing','2007-03-21',500000)
insert into #TABLE (Area, Date, Count)
values ('GuangZhou','2007-03-21',600000)
SELECT Area,
'2007-01-01'=SUM(CASE Date WHEN '2007-01-01' THEN [Count] END),
'2007-02-19'=SUM(CASE Date WHEN '2007-02-19' THEN [Count] END),
'2007-03-21'=SUM(CASE Date WHEN '2007-03-21' THEN [Count] END)
FROM #table
GROUP BY Area
SELECT * FROM
#TABLE
PIVOT(SUM([Count]) FOR Date IN (
[2007-01-01],[2007-02-19],[2007-03-21])) b
--以前写的
0
直接用PIVOT就行了啊。
declare @sql varchar(max),@sqlAll nvarchar(max)
select @sql=''
select @sql=@sql+'['+[date]+'],'
from user group by [date] ORDER BY [date]
select @sql=left(@sql,len(@sql)-1)
select @sqlAll='
select * from user s
pivot
(max(menu)
for [date] in ('+@sql+')
) as P
order by userid'
exec sp_executesql @sqlAll
假设表名为User，直接执行以上语句就能得到结果。
