急，急，急。。sql行变列问题

[cs_winnerchen]

/*
--求一行变列的SQL语句或存储过程--
-------------------------------

-原始表数据如下-----------------
--------------------------
Name Value Date
------ ------ ----------
A1 553 2011-02-19
A1 663 2011-02-18
A2 223 2011-02-19
A2 557 2011-02-18
A3 723 2011-02-19
A3 235 2011-02-18
B1 246 2011-02-18
B1 223 2011-02-19
B1 245 2011-02-20
B2 642 2011-02-19
B2 246 2011-02-18
C1 525 2011-02-19
C1 744 2011-02-18
C1 245 2011-02-20
--------------------------
现在我想把行变为列，需要得到下面这样的效果：
---------------------------------------------------------------
A1 A2 A3 B1 B2 C1 Date
------ ------ ------ ------ ------ ------ ----------
533 223 723 223 642 525 2011-02-19
663 557 235 246 246 744 2011-02-18
NULL NULL NULL 245 NULL 245 2011-02-20
---------------------------------------------------------------
还需要可以根据Name和Date查询，比如我只想要查询[Name]='A1'和'A2'并且[Date]在'2011-02-20'之前的数据，
就是下面这样的效果：
--------------------------
A1 A2 Date
------ ------ ----------
663 557 2011-02-18
533 223 2011-02-19
--------------------------

用SQL 语句或者存储过程都可以
*/

[gw6328]

最近很多转的

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

[gw6328]

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

[wing7742]

有整理贴啊 LOOK

[AcHerat]

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)

[永生天地]

看这里有没有合适的
http://blog.csdn.net/xys_777/archive/2010/06/22/5685953.aspx

[快溜]

又是行转列。

[yj258213056]

/*
谢了谢了,,呵呵,还是我自己解决了,楼上的有些已经很接近了,但是还没达到我的要求,,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)