22,300
社区成员




/*
谢了谢了,,呵呵,还是我自己解决了,楼上的有些已经很接近了,但是还没达到我的要求,,varchar 是不能用sum来聚合的,是我没说清楚,不过还是谢了,
*/
declare @sql varchar(8000)
set @sql = 'select '
select @sql = @sql +' max(case [Name] when '''+ [Name] +''' then [Value] end) as '+ [Name] +','
from (select distinct(Name) from [dbo].[Table] where Name IN('A','B')) a
select @sql = @sql + '[Date] from [dbo].[Table] where [Date] between ''2011-01-27'' AND ''2011-01-30'' group by [Date]'
exec(@sql)
declare @sql varchar(max)
set @sql = 'select convert(varchar(10),date,120)date'
select @sql = @sql + ',max(case [name] when ''' + [name] + ''' then [value] else null end)[' + [name] + ']'
from (select distinct [name] from tb)u
select @sql = @sql + ' from tb where [name] in (''A1'',''A2'') and date < ''2011-2-20'' group by convert(varchar(10),date,120)'
exec(@sql)
SELECT [DATE],[A1] AS [A1],[A2] AS[A2],[A3] AS [A3],[B1] AS [B1],[C1] AS [C1] FROM TB
PIVOT (SUM(VAL) FOR [NAME] IN ([A1],[A2],[A3],[B1],[C1])) pvt
最近很多转的
Name Value Date
SELECT [DATE],[A1] AS [A1],[A2] AS[A2],[A3] AS [A3][B1] AS [B1],[C1] AS [C1] FROM TB
PIVOT (SUM(VAL) FOR [NAME] IN ([A1],[A2],[A3],[B1],[C1])) pvt