再问一个行转列统计的问题－－3

Question

数据表结构
CREATE [RunData](
[ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[STCD] [varchar](12) COLLATE Chinese_PRC_CI_AS NOT NULL,
[MNTM] [datetime] NOT NULL,
[SUMQ] [decimal](12, 2) NULL
 )
测试数据：
Insert [RunData]
Select 'ST01','2010-04-01 00:00:00',20 union all
Select 'ST01','2010-04-01 06:00:00',30 union all
Select 'ST01','2010-04-01 12:00:00',40 union all
Select 'ST01','2010-04-01 18:00:00',50 union all
Select 'ST01','2010-04-02 00:00:00',60 union all
Select 'ST01','2010-04-02 06:00:00',70 union all
Select 'ST02','2010-04-01 00:00:00',30 union all
Select 'ST02','2010-04-01 06:00:00',35 union all
Select 'ST02','2010-04-01 12:00:00',42 union all
Select 'ST02','2010-04-01 18:00:00',50 
Go测站表：
Create [STINF](
STCD varchar(12),
STNM varchar(20)
)
测试数据：
Insert [STINF]
Select 'ST01','1#号' union all
Select 'ST02','2#号' union all
Select 'ST03','3#号' union all
Select 'ST04','4#号' 
Go传入时间，比如2010-04,
希望得到的结果是：(希望格式不要乱，好像空格不认)时间    1#号   2#号   3#号    4#号
1       30     20
2       10     
3
4
5
6 
说明：
时间为日列表：1－31
测站的值为：日最大值－日取小值
需要列出测站表有的测站，数据表中如没有该测站的数据，则值为空我的想法：
如果传入的值为 @DateTime varchar(10) --时间yyyy-MM-dd格式
1、得到日列表
select TM=number from master..spt_values
     where type='p'
     and number >=1 
     and number <= datediff(dd,@DateTime,dateadd(month,1,@DateTime))2、需要将数据表中的列转置为行，但因为需要将测站表的测站全部列出来，需要跟测站表关联3、然后就是计算写了一晚上，都没有写好，希望高手帮忙

Answer 1

CREATE table[RunData](
[ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[STCD] [varchar](12) COLLATE Chinese_PRC_CI_AS NOT NULL,
[MNTM] [datetime] NOT NULL,
[SUMQ] [decimal](12, 2) NULL
 )
 
Insert [RunData]
Select 'ST01','2010-04-01 00:00:00',20 union all
Select 'ST01','2010-04-01 06:00:00',30 union all
Select 'ST01','2010-04-01 12:00:00',40 union all
Select 'ST01','2010-04-01 18:00:00',50 union all
Select 'ST01','2010-04-02 00:00:00',60 union all
Select 'ST01','2010-04-02 06:00:00',70 union all
Select 'ST02','2010-04-01 00:00:00',30 union all
Select 'ST02','2010-04-01 06:00:00',35 union all
Select 'ST02','2010-04-01 12:00:00',42 union all
Select 'ST02','2010-04-01 18:00:00',50  
Go
 
Create table [STINF](
STCD varchar(12),
STNM varchar(20)
)
 
Insert [STINF]
Select 'ST01','1#号' union all
Select 'ST02','2#号' union all
Select 'ST03','3#号' union all
Select 'ST04','4#号'  
Goselect *
from(
select stnm,day([MNTM]) mntm,max([SUMQ]) - min(sumq) sumq
from [RunData] a,[STINF] b
where a.STCD = b.STCD
group by stnm,day([MNTM])
) p
pivot
  (  max( sumq)
    for stnm in([1#号] ,[2#号],[3#号],[4#号]))pvtdrop table [RunData],[STINF]
mntm        1#号                                     2#号                                     3#号                                     4#号
----------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
1           30.00                                   20.00                                   NULL                                    NULL
2           10.00                                   NULL                                    NULL                                    NULL(2 行受影响)

Answer 2

if object_id('[RunData]','U') is not null
    drop table [RunData];
go
CREATE table [RunData](
[ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[STCD] [varchar](12) COLLATE Chinese_PRC_CI_AS NOT NULL,
[MNTM] [datetime] NOT NULL,
[SUMQ] [decimal](12, 2) NULL
 )
Insert [RunData]
Select 'ST01','2010-04-01 00:00:00',20 union all
Select 'ST01','2010-04-01 06:00:00',30 union all
Select 'ST01','2010-04-01 12:00:00',40 union all
Select 'ST01','2010-04-01 18:00:00',50 union all
Select 'ST01','2010-04-02 00:00:00',60 union all
Select 'ST01','2010-04-02 06:00:00',70 union all
Select 'ST02','2010-04-01 00:00:00',30 union all
Select 'ST02','2010-04-01 06:00:00',35 union all
Select 'ST02','2010-04-01 12:00:00',42 union all
Select 'ST02','2010-04-01 18:00:00',50  
Goif object_id('[STINF]','U') is not null
    drop table [STINF];
go
Create table [STINF](
STCD varchar(12),
STNM varchar(20)
)
Insert [STINF]
Select 'ST01','1#号' union all
Select 'ST02','2#号' union all
Select 'ST03','3#号' union all
Select 'ST04','4#号'  
Go
/*
传入时间，比如2010-04,
希望得到的结果是：(希望格式不要乱，好像空格不认)
*/
--月份表
declare @DateTime varchar(10)
set @DateTime = '2010-04-01'
if object_id('tempdb..#','U') is not null
    drop table #;
select 
    [day] = number
into
    #
from
    master..spt_values
where
    [type] = 'P'
and
    number between 1 and datediff(day, @DateTime, dateadd(month, 1, @DateTime))--left join出结果
select 
    a.[day]
    ,[1#号]= isnull(b.[1#号],0) - isnull(c.[1#号],0)
    ,[2#号]= isnull(b.[2#号],0) - isnull(c.[2#号],0)
    ,[3#号]= isnull(b.[3#号],0) - isnull(c.[3#号],0)
    ,[4#号]= isnull(b.[4#号],0) - isnull(c.[4#号],0)    
from
    # a
left join
(
--最大值
select [day] = day(a.mntm)
    ,[1#号] = max(case when stnm = '1#号' then sumq else 0 end)
    ,[2#号] = max(case when stnm = '2#号' then sumq else 0 end)
    ,[3#号] = max(case when stnm = '3#号' then sumq else 0 end)
    ,[4#号] = max(case when stnm = '4#号' then sumq else 0 end)
from [RunData] a 
     inner join [STINF] b
on a.stcd = b.stcd
group by day(a.mntm)
) b on a.[day] = b.[day]
left join
(
--最小值
select [day] = day(a.mntm)
    ,[1#号] = min(case when stnm = '1#号' then sumq end)
    ,[2#号] = min(case when stnm = '2#号' then sumq end)
    ,[3#号] = min(case when stnm = '3#号' then sumq end)
    ,[4#号] = min(case when stnm = '4#号' then sumq end)
from [RunData] a 
     inner join [STINF] b
on a.stcd = b.stcd
group by day(a.mntm)
) c on a.[day] = c.[day]--结果
/*
day         1#号                                     2#号                                     3#号                                     4#号
----------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
1           30.00                                   20.00                                   0.00                                    0.00
2           10.00                                   0.00                                    0.00                                    0.00
3           0.00                                    0.00                                    0.00                                    0.00
4           0.00                                    0.00                                    0.00                                    0.00
5           0.00                                    0.00                                    0.00                                    0.00
6           0.00                                    0.00                                    0.00                                    0.00
7           0.00                                    0.00                                    0.00                                    0.00
8           0.00                                    0.00                                    0.00                                    0.00
9           0.00                                    0.00                                    0.00                                    0.00
10          0.00                                    0.00                                    0.00                                    0.00
11          0.00                                    0.00                                    0.00                                    0.00
12          0.00                                    0.00                                    0.00                                    0.00
13          0.00                                    0.00                                    0.00                                    0.00
14          0.00                                    0.00                                    0.00                                    0.00
15          0.00                                    0.00                                    0.00                                    0.00
16          0.00                                    0.00                                    0.00                                    0.00
17          0.00                                    0.00                                    0.00                                    0.00
18          0.00                                    0.00                                    0.00                                    0.00
19          0.00                                    0.00                                    0.00                                    0.00
20          0.00                                    0.00                                    0.00                                    0.00
21          0.00                                    0.00                                    0.00                                    0.00
22          0.00                                    0.00                                    0.00                                    0.00
23          0.00                                    0.00                                    0.00                                    0.00
24          0.00                                    0.00                                    0.00                                    0.00
25          0.00                                    0.00                                    0.00                                    0.00
26          0.00                                    0.00                                    0.00                                    0.00
27          0.00                                    0.00                                    0.00                                    0.00
28          0.00                                    0.00                                    0.00                                    0.00
29          0.00                                    0.00                                    0.00                                    0.00
30          0.00                                    0.00                                    0.00                                    0.00
警告: 聚合或其他 SET 操作消除了空值。(30 行受影响)
*/

Answer 3

happyflystone的作法，没有实现按日期选择，另外没有实现按日（1－31）排列
黎叔的结果很像我要的，但有一个问题，我的测站名称，可能我并不清楚，但你的select列表的时候，是人为指定的

Answer 4

if object_id('[RunData]','U') is not null
    drop table [RunData];
go
CREATE table [RunData](
[ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[STCD] [varchar](12) COLLATE Chinese_PRC_CI_AS NOT NULL,
[MNTM] [datetime] NOT NULL,
[SUMQ] [decimal](12, 2) NULL
 )
Insert [RunData]
Select 'ST01','2010-04-01 00:00:00',20 union all
Select 'ST01','2010-04-01 06:00:00',30 union all
Select 'ST01','2010-04-01 12:00:00',40 union all
Select 'ST01','2010-04-01 18:00:00',50 union all
Select 'ST01','2010-04-02 00:00:00',60 union all
Select 'ST01','2010-04-02 06:00:00',70 union all
Select 'ST02','2010-04-01 00:00:00',30 union all
Select 'ST02','2010-04-01 06:00:00',35 union all
Select 'ST02','2010-04-01 12:00:00',42 union all
Select 'ST02','2010-04-01 18:00:00',50  
Goif object_id('[STINF]','U') is not null
    drop table [STINF];
go
Create table [STINF](
STCD varchar(12),
STNM varchar(20)
)
Insert [STINF]
Select 'ST01','1#号' union all
Select 'ST02','2#号' union all
Select 'ST03','3#号' union all
Select 'ST04','4#号'  
Go
/*
传入时间，比如2010-04,
希望得到的结果是：(希望格式不要乱，好像空格不认)
*/
--月份表
declare @DateTime varchar(10)
set @DateTime = '2010-04-01'
if object_id('temp_of_month','U') is not null
    drop table temp_of_month;
select 
    [day] = number
into
    temp_of_month
from
    master..spt_values
where
    [type] = 'P'
and
    number between 1 and datediff(day, @DateTime, dateadd(month, 1, @DateTime))declare @sql nvarchar(4000)
declare @str nvarchar(4000)--拼接动态case when
set @sql = 'select 
    a.[day], ';
select @str = isnull(@str+',','') + quotename(STNM)
from [STINF];--print @str  
--结果
/*
[1#号], [2#号], [3#号], [4#号]
*/set @sql = @sql + @str
set @sql = @sql + ' 
from 
    temp_of_month a 
left join 
(
select 
    [day] = day(a.mntm) ' + char(10);declare my_cursor cursor scroll dynamic for 
select distinct STNM from [STINF]
open my_cursordeclare @colName nvarchar(30)
fetch next from my_cursor into @colName
while (@@fetch_status = 0)
begin
    set @sql = @sql + '    ,' + quotename(@colName) + ' = max(case when stnm = ''' + @colName 
           + ''' then sumq else 0 end) - min(case when stnm = ''' + @colName + ''' then sumq end)' + char(10)
    fetch next from my_cursor into @colName
end
fetch first from my_cursor into @colName
close my_cursor
deallocate my_cursorset @sql = @sql + 'from [RunData] a 
     inner join [STINF] b
on a.stcd = b.stcd
group by day(a.mntm)
) b 
on a.[day] = b.[day]'--查看动态sql的内容
--print @sql
/*
select 
    a.[day], [1#号],[2#号],[3#号],[4#号] 
from 
    temp_of_month a 
left join 
(
select 
    [day] = day(a.mntm) 
    ,[1#号] = max(case when stnm = '1#号' then sumq else 0 end) - min(case when stnm = '1#号' then sumq end)
    ,[2#号] = max(case when stnm = '2#号' then sumq else 0 end) - min(case when stnm = '2#号' then sumq end)
    ,[3#号] = max(case when stnm = '3#号' then sumq else 0 end) - min(case when stnm = '3#号' then sumq end)
    ,[4#号] = max(case when stnm = '4#号' then sumq else 0 end) - min(case when stnm = '4#号' then sumq end)
from [RunData] a 
     inner join [STINF] b
on a.stcd = b.stcd
group by day(a.mntm)
) b 
on a.[day] = b.[day]
*/--执行动态SQL
exec(@sql)--结果
/*
day         1#号                                     2#号                                     3#号                                     4#号
----------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
1           30.00                                   20.00                                   NULL                                    NULL
2           10.00                                   NULL                                    NULL                                    NULL
3           NULL                                    NULL                                    NULL                                    NULL
4           NULL                                    NULL                                    NULL                                    NULL
5           NULL                                    NULL                                    NULL                                    NULL
6           NULL                                    NULL                                    NULL                                    NULL
7           NULL                                    NULL                                    NULL                                    NULL
8           NULL                                    NULL                                    NULL                                    NULL
9           NULL                                    NULL                                    NULL                                    NULL
10          NULL                                    NULL                                    NULL                                    NULL
11          NULL                                    NULL                                    NULL                                    NULL
12          NULL                                    NULL                                    NULL                                    NULL
13          NULL                                    NULL                                    NULL                                    NULL
14          NULL                                    NULL                                    NULL                                    NULL
15          NULL                                    NULL                                    NULL                                    NULL
16          NULL                                    NULL                                    NULL                                    NULL
17          NULL                                    NULL                                    NULL                                    NULL
18          NULL                                    NULL                                    NULL                                    NULL
19          NULL                                    NULL                                    NULL                                    NULL
20          NULL                                    NULL                                    NULL                                    NULL
21          NULL                                    NULL                                    NULL                                    NULL
22          NULL                                    NULL                                    NULL                                    NULL
23          NULL                                    NULL                                    NULL                                    NULL
24          NULL                                    NULL                                    NULL                                    NULL
25          NULL                                    NULL                                    NULL                                    NULL
26          NULL                                    NULL                                    NULL                                    NULL
27          NULL                                    NULL                                    NULL                                    NULL
28          NULL                                    NULL                                    NULL                                    NULL
29          NULL                                    NULL                                    NULL                                    NULL
30          NULL                                    NULL                                    NULL                                    NULL
警告: 聚合或其他 SET 操作消除了空值。(30 行受影响)
*/--删除月份表
drop table temp_of_month;

Answer 5

--石头大哥的动态语句，2005及以上版本适用
if object_id('[RunData]','U') is not null
    drop table [RunData];
go
CREATE table[RunData](
[ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[STCD] [varchar](12) COLLATE Chinese_PRC_CI_AS NOT NULL,
[MNTM] [datetime] NOT NULL,
[SUMQ] [decimal](12, 2) NULL
 )
 
Insert [RunData]
Select 'ST01','2010-04-01 00:00:00',20 union all
Select 'ST01','2010-04-01 06:00:00',30 union all
Select 'ST01','2010-04-01 12:00:00',40 union all
Select 'ST01','2010-04-01 18:00:00',50 union all
Select 'ST01','2010-04-02 00:00:00',60 union all
Select 'ST01','2010-04-02 06:00:00',70 union all
Select 'ST02','2010-04-01 00:00:00',30 union all
Select 'ST02','2010-04-01 06:00:00',35 union all
Select 'ST02','2010-04-01 12:00:00',42 union all
Select 'ST02','2010-04-01 18:00:00',50  
Goif object_id('[STINF]','U') is not null
    drop table [STINF];
go
Create table [STINF](
STCD varchar(12),
STNM varchar(20)
)
 
Insert [STINF]
Select 'ST01','1#号' union all
Select 'ST02','2#号' union all
Select 'ST03','3#号' union all
Select 'ST04','4#号'  
Go--月份表
declare @DateTime varchar(10)
set @DateTime = '2010-04-01'
if object_id('temp_of_month','U') is not null
    drop table temp_of_month;
select 
    [day] = number
into
    temp_of_month
from
    master..spt_values
where
    [type] = 'P'
and
    number between 1 and datediff(day, @DateTime, dateadd(month, 1, @DateTime))declare @sql nvarchar(4000)
declare @str nvarchar(4000)select @str = isnull(@str+',','') + quotename(STNM)
from [STINF]
--print @str
--[1#号], [2#号], [3#号], [4#号]
set @sql = '
select a.[day], ' + @str + '
from
    temp_of_month a
left join
(
select *
from
(
select stnm,day([MNTM]) mntm,max([SUMQ]) - min(sumq) sumq
from [RunData] a,[STINF] b
where a.STCD = b.STCD
group by stnm,day([MNTM])
) p
pivot
  ( max(sumq)
    for stnm in('set @sql = @sql + @str + '))pvt 
) b
on a.[day] = b.mntm'--查看动态sql的内容
--print @sql
/*
select a.[day], [1#号],[2#号],[3#号],[4#号]
from
    temp_of_month a
left join
(
select *
from
(
select stnm,day([MNTM]) mntm,max([SUMQ]) - min(sumq) sumq
from [RunData] a,[STINF] b
where a.STCD = b.STCD
group by stnm,day([MNTM])
) p
pivot
  ( max(sumq)
    for stnm in([1#号],[2#号],[3#号],[4#号]))pvt 
) b
on a.[day] = b.mntm
*/--执行动态SQL
exec(@sql)--结果
/*
day         1#号                                     2#号                                     3#号                                     4#号
----------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
1           30.00                                   20.00                                   NULL                                    NULL
2           10.00                                   NULL                                    NULL                                    NULL
3           NULL                                    NULL                                    NULL                                    NULL
4           NULL                                    NULL                                    NULL                                    NULL
5           NULL                                    NULL                                    NULL                                    NULL
6           NULL                                    NULL                                    NULL                                    NULL
7           NULL                                    NULL                                    NULL                                    NULL
8           NULL                                    NULL                                    NULL                                    NULL
9           NULL                                    NULL                                    NULL                                    NULL
10          NULL                                    NULL                                    NULL                                    NULL
11          NULL                                    NULL                                    NULL                                    NULL
12          NULL                                    NULL                                    NULL                                    NULL
13          NULL                                    NULL                                    NULL                                    NULL
14          NULL                                    NULL                                    NULL                                    NULL
15          NULL                                    NULL                                    NULL                                    NULL
16          NULL                                    NULL                                    NULL                                    NULL
17          NULL                                    NULL                                    NULL                                    NULL
18          NULL                                    NULL                                    NULL                                    NULL
19          NULL                                    NULL                                    NULL                                    NULL
20          NULL                                    NULL                                    NULL                                    NULL
21          NULL                                    NULL                                    NULL                                    NULL
22          NULL                                    NULL                                    NULL                                    NULL
23          NULL                                    NULL                                    NULL                                    NULL
24          NULL                                    NULL                                    NULL                                    NULL
25          NULL                                    NULL                                    NULL                                    NULL
26          NULL                                    NULL                                    NULL                                    NULL
27          NULL                                    NULL                                    NULL                                    NULL
28          NULL                                    NULL                                    NULL                                    NULL
29          NULL                                    NULL                                    NULL                                    NULL
30          NULL                                    NULL                                    NULL                                    NULL(30 行受影响)
*/drop table [RunData],[STINF]