SQL 行转列。。。。急，急，急，急、、

benben_tong 2012-02-17 11:06:02

现有表[Hong_Props],表中的字段如下：

PropID PropGameType PropArrea PropTime Props PropsCoun

1 1 1 2012-02-11 道具A 24
2 2 2 2012-02-11 道具B 15
3 1 1 2012-02-12 道具C 14
4 2 1 2012-02-12 道具D 2
5 2 2 2012-02-13 道具D 50
6 1 2 2012-02-14 道具B 9
7 2 2 2012-02-15 道具E 10
8 1 1 2012-02-15 道具A 20

先我要得到的查询效果为：

日期 2012-02-11 2012-02-12 2012-02-13 2012-02-14 2012-02-15 累计

道具A 24 / / / 20 44
道具B 15 / / 9 / 24
道具C / 14 / / / 14
道具D / 2 50 / / 52
道具E / / / / 10 10

急求高手帮忙！
急求高手帮忙！
急求高手帮忙！
急求高手帮忙！

...全文

188 10 打赏收藏转发到动态举报

写回复

用AI写文章

10 条回复

切换为时间正序

请发表友善的回复…

发表回复

一十七 2012-02-17

打赏
举报



/*

Props                2012-02-11  2012-02-12  2012-02-13  2012-02-14  2012-02-15  累计

-------------------- ----------- ----------- ----------- ----------- ----------- -----------

道具A                  24          NULL        NULL        NULL        20          44

道具B                  15          NULL        NULL        9           NULL        24

道具C                  NULL        14          NULL        NULL        NULL        14

道具D                  NULL        2           50          NULL        NULL        52

道具E                  NULL        NULL        NULL        NULL        10          10

*/

一十七 2012-02-17

打赏
举报



IF OBJECT_ID('Hong_Props') IS NOT NULL

DROP TABLE Hong_Props

GO

create table Hong_Props(

PropID int,PropGameType int,PropArrea int,PropTime datetime,Props varchar(20),PropsCoun int

)

insert into Hong_Props

select 1 ,1 ,1 ,'2012-02-11' ,'道具A' ,24 union all

select 2 ,2 ,2 ,'2012-02-11' ,'道具B' ,15 union all

select 3 ,1 ,1 ,'2012-02-12' ,'道具C' ,14 union all

select 4 ,2 ,1 ,'2012-02-12' ,'道具D' ,2 union all

select 5 ,2 ,2 ,'2012-02-13' ,'道具D' ,50 union all

select 6 ,1 ,2 ,'2012-02-14' ,'道具B' ,9 union all

select 7 ,2 ,2 ,'2012-02-15' ,'道具E' ,10 union all

select 8 ,1 ,1 ,'2012-02-15' ,'道具A' ,20

go



DECLARE @A VARCHAR(2000)

SELECT @A = ISNULL(@A+',','')+QUOTENAME(PropTime)

FROM (

SELECT DISTINCT PropTime = CONVERT(VARCHAR(10),PropTime,120) FROM Hong_Props

) AA 

EXEC('SELECT *,累计=(SELECT SUM(ISNULL(PropsCoun,0)) FROM Hong_Props WHERE Props = A.Props)

FROM (

SELECT Props,'

+@A+'

FROM (SELECT Props,PropsCoun = SUM(ISNULL(PropsCoun,0)),PropTime FROM Hong_Props GROUP BY Props,PropTime) A

PIVOT

(SUM(PropsCoun) FOR PropTime IN ('+@A+'))

PIV

) A' 

)



/*

Props	2012-02-11	2012-02-12	2012-02-13	2012-02-14	2012-02-15	累计

道具A	24	NULL	NULL	NULL	20	44

道具B	15	NULL	NULL	9	NULL	24

道具C	NULL	14	NULL	NULL	NULL	14

道具D	NULL	2	50	NULL	NULL	52

道具E	NULL	NULL	NULL	NULL	10	10

*/

dawugui 2012-02-17

打赏
举报

--如果PropTime字段为字符串型

declare @sql varchar(8000)

set @sql = 'select Props '

select @sql = @sql + ' , max(case PropTime when ''' + PropTime + ''' then PropsCoun else 0 end) [' + PropTime + ']'

from (select distinct PropTime from Hong_Props) as a

set @sql = @sql + ' ,sum(PropsCoun) 累计 from Hong_Props group by Props'

exec(@sql) 



--如果PropTime字段为时间型

declare @sql varchar(8000)

set @sql = 'select Props '

select @sql = @sql + ' , max(case PropTime when ''' + PropTime + ''' then PropsCoun else 0 end) [' + PropTime + ']'

from (select distinct convert(varchar(10),PropTime,120) PropTime from Hong_Props) as a

set @sql = @sql + ' ,sum(PropsCoun) 累计 from (select convert(varchar(10),PropTime,120) PropTime, Props ,PropsCoun from Hong_Props) t group by Props'

exec(@sql)

AcHerat 2012-02-17

打赏
举报



create table Hong_Props(

PropID int,PropGameType int,PropArrea int,PropTime datetime,Props varchar(20),PropsCoun int

)

insert into Hong_Props

select 1 ,1 ,1 ,'2012-02-11' ,'道具A' ,24 union all

select 2 ,2 ,2 ,'2012-02-11' ,'道具B' ,15 union all

select 3 ,1 ,1 ,'2012-02-12' ,'道具C' ,14 union all

select 4 ,2 ,1 ,'2012-02-12' ,'道具D' ,2 union all

select 5 ,2 ,2 ,'2012-02-13' ,'道具D' ,50 union all

select 6 ,1 ,2 ,'2012-02-14' ,'道具B' ,9 union all

select 7 ,2 ,2 ,'2012-02-15' ,'道具E' ,10 union all

select 8 ,1 ,1 ,'2012-02-15' ,'道具A' ,20

go



declare @sql varchar(max)

set @sql = 'select Props'

select @sql = @sql + ',sum(case convert(varchar(10),PropTime,120) when '''+date+''' then PropsCoun else 0 end) ['+date+']'

from(

	select convert(varchar(10),PropTime,120) date

	from Hong_Props

	group by convert(varchar(10),PropTime,120)

) t

order by date

select @sql = @sql + ',sum(PropsCoun) as [累计] from Hong_Props group by Props '

exec(@sql)



drop table Hong_Props



/*************************



Props                2012-02-11  2012-02-12  2012-02-13  2012-02-14  2012-02-15  累计

-------------------- ----------- ----------- ----------- ----------- ----------- -----------

道具A                  24          0           0           0           20          44

道具B                  15          0           0           9           0           24

道具C                  0           14          0           0           0           14

道具D                  0           2           50          0           0           52

道具E                  0           0           0           0           10          10



(5 行受影响)

AcHerat 2012-02-17

打赏
举报

declare @sql varchar(max)

set @sql = 'select Props'

select @sql = @sql + ',sum(case convert(varchar(8),PropTime,112) when '''+date+''' then PropsCoun else 0 end) ['+date+']'

from(

	select convert(varchar(8),PropTime,112) date

	from Hong_Props

	group by convert(varchar(8),PropTime,112)

) t

select @sql = @sql + ',sum(PropsCoun) as [累计] from Hong_Props group by Props '

exec(@sql)

开着拖拉机泡妞 2012-02-17

打赏
举报

/*
现有表[Hong_Props],表中的字段如下：

PropID PropGameType PropArrea PropTime Props PropsCoun

1 1 1 2012-02-11 道具A 24
2 2 2 2012-02-11 道具B 15
3 1 1 2012-02-12 道具C 14
4 2 1 2012-02-12 道具D 2
5 2 2 2012-02-13 道具D 50
6 1 2 2012-02-14 道具B 9
7 2 2 2012-02-15 道具E 10
8 1 1 2012-02-15 道具A 20

先我要得到的查询效果为：

日期 2012-02-11 2012-02-12 2012-02-13 2012-02-14 2012-02-15 累计

道具A 24 / / / 20 44
道具B 15 / / 9 / 24
道具C / 14 / / / 14
道具D / 2 50 / / 52
道具E / / / / 10 10
*/
go
if OBJECT_ID('Hong_Props')is not null
drop table Hong_Props
go
create table Hong_Props(
PropID int,
PropGameType int,
PropArrea int,
PropTime date,
Props varchar(20),
PropsCoun int
)
go
insert Hong_Props
select 1 ,1 ,1 ,'2012-02-11' ,'道具A' ,24 union all
select 2 ,2 ,2 ,'2012-02-11' ,'道具B' ,15 union all
select 3 ,1 ,1 ,'2012-02-12' ,'道具C' ,14 union all
select 4 ,2 ,1 ,'2012-02-12' ,'道具D' ,2 union all
select 5 ,2 ,2 ,'2012-02-13' ,'道具D' ,50 union all
select 6 ,1 ,2 ,'2012-02-14' ,'道具B' ,9 union all
select 7 ,2 ,2 ,'2012-02-15' ,'道具E' ,10 union all
select 8 ,1 ,1 ,'2012-02-15' ,'道具A' ,20

select *from Hong_Props

go
if OBJECT_ID('p_exchange')is not null
drop proc p_exchange
go
create proc p_exchange
as
declare @str varchar(2000)
set @str=''
select @str=@str+','+quotename(PropTime)+'=max(case when PropTime='
+quotename(PropTime,'''')+' then PropsCoun else 0 end)'
from Hong_Props group by PropTime
set @str='select Props'+@str+',sum(PropsCoun) as 累计 from Hong_Props group by Props'
exec (@str)
go

--调用存储过程：
exec p_exchange
--结果：
/*
Props 2012-02-11 2012-02-12 2012-02-13 2012-02-14 2012-02-15 累计
道具A 24 0 0 0 20 44
道具B 15 0 0 9 0 24
道具C 0 14 0 0 0 14
道具D 0 2 50 0 0 52
道具E 0 0 0 0 10 10
*/

开着拖拉机泡妞 2012-02-17

打赏
举报

go
if OBJECT_ID('Hong_Props')is not null
drop table Hong_Props
go
create table Hong_Props(
PropID int,
PropGameType int,
PropArrea int,
PropTime date,
Props varchar(20),
PropsCoun int
)
go
insert Hong_Props
select 1 ,1 ,1 ,'2012-02-11' ,'道具A' ,24 union all
select 2 ,2 ,2 ,'2012-02-11' ,'道具B' ,15 union all
select 3 ,1 ,1 ,'2012-02-12' ,'道具C' ,14 union all
select 4 ,2 ,1 ,'2012-02-12' ,'道具D' ,2 union all
select 5 ,2 ,2 ,'2012-02-13' ,'道具D' ,50 union all
select 6 ,1 ,2 ,'2012-02-14' ,'道具B' ,9 union all
select 7 ,2 ,2 ,'2012-02-15' ,'道具E' ,10 union all
select 8 ,1 ,1 ,'2012-02-15' ,'道具A' ,20

select *from Hong_Props

go
if OBJECT_ID('p_exchange')is not null
drop proc p_exchange
go
create proc p_exchange
as
declare @str varchar(2000)
set @str=''
select @str=@str+','+quotename(PropTime)+'=max(case when PropTime='
+quotename(PropTime,'''')+' then PropsCoun else 0 end)'
from Hong_Props
set @str='select Props'+@str+',sum(PropsCoun) as 累计 from Hong_Props group by Props'
exec (@str)

exec p_exchange

开着拖拉机泡妞 2012-02-17

打赏
举报

[Quote=引用 2 楼 acherat 的回复:]

SQL code

create table Hong_Props(
PropID int,PropGameType int,PropArrea int,PropTime datetime,Props varchar(20),PropsCoun int
)
insert into Hong_Props
select 1 ,1 ,1 ,'2012-02-11' ,'道具A' ,24 union ……
[/Quote]

动态行列转换，也就是拼接查询语句。

勿勿 2012-02-17

打赏
举报

create table Hong_Props(

PropID int,PropGameType int,PropArrea int,PropTime datetime,Props varchar(20),PropsCoun int

)

insert into Hong_Props

select 1 ,1 ,1 ,'2012-02-11' ,'道具A' ,24 union all

select 2 ,2 ,2 ,'2012-02-11' ,'道具B' ,15 union all

select 3 ,1 ,1 ,'2012-02-12' ,'道具C' ,14 union all

select 4 ,2 ,1 ,'2012-02-12' ,'道具D' ,2 union all

select 5 ,2 ,2 ,'2012-02-13' ,'道具D' ,50 union all

select 6 ,1 ,2 ,'2012-02-14' ,'道具B' ,9 union all

select 7 ,2 ,2 ,'2012-02-15' ,'道具E' ,10 union all

select 8 ,1 ,1 ,'2012-02-15' ,'道具A' ,20

go



 

declare @str varchar(max)

set @str ='select Props'

select @str=@str+', sum(case  PropTime when '''+PropTime+''' then PropsCoun else 0 end ) ['+PropTime+']'

from (select distinct  CONVERT(varchar(10), PropTime,120)PropTime   from Hong_Props group by  Props,PropTime)a

set @str=@str+' ,sum(PropsCoun)as 累计 from (select convert(varchar(10),PropTime,120) PropTime, Props ,PropsCoun from Hong_Props) t

 group by  Props'

exec (@str)

 



 





Props                2012-02-11  2012-02-12  2012-02-13  2012-02-14  2012-02-15  累计

-------------------- ----------- ----------- ----------- ----------- ----------- -----------

道具A                  24          0           0           0           20          44

道具B                  15          0           0           9           0           24

道具C                  0           14          0           0           0           14

道具D                  0           2           50          0           0           52

道具E                  0           0           0           0           10          10



(5 行受影响)