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

cs_winnerchen 2011-02-23 05:38:44
/*
--求一行变列的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 语句或者存储过程都可以
*/


...全文
105 点赞 收藏 7
写回复
7 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
yj258213056 2011-02-25

/*
谢了谢了,,呵呵,还是我自己解决了,楼上的有些已经很接近了,但是还没达到我的要求,,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)
回复
快溜 2011-02-23
又是行转列。
回复
永生天地 2011-02-23
回复

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)
回复
wing7742 2011-02-23
有整理贴啊 LOOK
回复
gw6328 2011-02-23

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 2011-02-23

最近很多转的

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
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2011-02-23 05:38
社区公告
暂无公告