首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 数据库 > SQL Server >

SQL 行转列。解决方案

2012-03-18 
SQL 行转列。。。。急，急，急，急、、现有表[Hong_Props],表中的字段如下：PropIDPropGameTypePropArreaPropTimePro

SQL 行转列。。。。急，急，急，急、、
现有表[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



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

[解决办法]

SQL code
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)) tselect @sql = @sql + ',sum(PropsCoun) as [累计] from Hong_Props group by Props 'exec(@sql)
[解决办法]
SQL code
create table Hong_Props(PropID int,PropGameType int,PropArrea int,PropTime datetime,Props varchar(20),PropsCoun int)insert into Hong_Propsselect 1 ,1 ,1 ,'2012-02-11' ,'道具A' ,24 union allselect 2 ,2 ,2 ,'2012-02-11' ,'道具B' ,15 union allselect 3 ,1 ,1 ,'2012-02-12' ,'道具C' ,14 union allselect 4 ,2 ,1 ,'2012-02-12' ,'道具D' ,2 union allselect 5 ,2 ,2 ,'2012-02-13' ,'道具D' ,50 union allselect 6 ,1 ,2 ,'2012-02-14' ,'道具B' ,9 union allselect 7 ,2 ,2 ,'2012-02-15' ,'道具E' ,10 union allselect 8 ,1 ,1 ,'2012-02-15' ,'道具A' ,20godeclare @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)) torder by dateselect @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 行受影响)
[解决办法]
SQL code
--如果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 aset @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 aset @sql = @sql + ' ,sum(PropsCoun) 累计 from (select convert(varchar(10),PropTime,120) PropTime, Props ,PropsCoun from Hong_Props) t group by Props'exec(@sql) 
 
[解决办法]
SQL code
IF OBJECT_ID('Hong_Props') IS NOT NULLDROP TABLE Hong_PropsGOcreate table Hong_Props(PropID int,PropGameType int,PropArrea int,PropTime datetime,Props varchar(20),PropsCoun int)insert into Hong_Propsselect 1 ,1 ,1 ,'2012-02-11' ,'道具A' ,24 union allselect 2 ,2 ,2 ,'2012-02-11' ,'道具B' ,15 union allselect 3 ,1 ,1 ,'2012-02-12' ,'道具C' ,14 union allselect 4 ,2 ,1 ,'2012-02-12' ,'道具D' ,2 union allselect 5 ,2 ,2 ,'2012-02-13' ,'道具D' ,50 union allselect 6 ,1 ,2 ,'2012-02-14' ,'道具B' ,9 union allselect 7 ,2 ,2 ,'2012-02-15' ,'道具E' ,10 union allselect 8 ,1 ,1 ,'2012-02-15' ,'道具A' ,20goDECLARE @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) APIVOT(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*/
[解决办法]
SQL code
/*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*/
[解决办法]
SQL code
create table Hong_Props(PropID int,PropGameType int,PropArrea int,PropTime datetime,Props varchar(20),PropsCoun int)insert into Hong_Propsselect 1 ,1 ,1 ,'2012-02-11' ,'道具A' ,24 union allselect 2 ,2 ,2 ,'2012-02-11' ,'道具B' ,15 union allselect 3 ,1 ,1 ,'2012-02-12' ,'道具C' ,14 union allselect 4 ,2 ,1 ,'2012-02-12' ,'道具D' ,2 union allselect 5 ,2 ,2 ,'2012-02-13' ,'道具D' ,50 union allselect 6 ,1 ,2 ,'2012-02-14' ,'道具B' ,9 union allselect 7 ,2 ,2 ,'2012-02-15' ,'道具E' ,10 union allselect 8 ,1 ,1 ,'2012-02-15' ,'道具A' ,20go 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)aset @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 行受影响)
[解决办法]
探讨
 
 
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 ……

[解决办法]
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
[解决办法]
/*
现有表[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
--结果：
/*
Props2012-02-112012-02-122012-02-132012-02-142012-02-15累计
道具A240002044
道具B15009024
道具C01400014
道具D02500052
道具E00001010
*/
读书人精选
热点排行