sql 行转列，高手进

Question

本人有如下表，XuQiuNumber是需求数，AlreadyNumber是供应数，Balance=AlreadyNumber-XuQiuNumber；在sql server 2005环境下的存储过程有了，我现在是要把它改为在sql server 2000环境下运行。
原始数据：XuQiuNumber  ProductName  SupplyName    Monday      AlreadyNumber    Balance
1000           A001         深圳公司      2010-07-26     800           -200
3000           A001         深圳公司      2010-08-2      3000           0
5000           A001         深圳公司      2010-08-9      4000          -1000
1000           A001         深圳公司      2010-08-16     500           -500
400            A001         深圳公司      2010-08-23     400            0
400            A001         深圳公司      2010-08-30     200           -200
800            A002         上海公司      2010-07-26     800            0
2000           A002         上海公司      2010-08-2      1500         -500
100            A002         上海公司      2010-08-9      100            0
1000           A002         上海公司      2010-08-16     500           -500
400            A002         上海公司      2010-08-23     400           0
400            A002         上海公司      2010-08-30     300           -100要求查询结果如下：
ProductName  SupplyName   Data   2010-07-26   2010-08-2      2010-08-30    Total
A001           深圳公      需求     1000         3000                 400        10800
                           供应     800          3000                  200       8900
                           平衡     -200         0                    -200       -1900
A002           上海公司    需求     800          2000                  400       4700
                           供应     800          1500                  300       3600
                           平衡     0            -500                 -100       -1100
sql server 2005下的存储过程，为Tony所写：set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goCreate PROCEDURE [dbo].[Search_TongJi]     @date DateTime, --开始时间
    @dateend DateTime --结束时间AS
BEGIN
    set nocount on    DECLARE @S VARCHAR(8000),@s1 VARCHAR(8000)
SELECT @S=isnull(@S+',','')+quotename(convert(VARCHAR,Monday,23)) 
FROM VIEW_XuQiuGongYingTongji 
where Monday>=@date and Monday<=@dateend  --这里还要加一个
GROUP BY [Monday]
SELECT @S1=isnull(@S1+',','')+'isnull('+quotename(convert(VARCHAR,Monday,23))+',0)'+quotename(convert(VARCHAR,Monday,23)) 
FROM VIEW_XuQiuGongYingTongji 
where Monday>@date and Monday<=@dateend
GROUP BY [Monday]
DECLARE @SQL VARCHAR(MAX)
SET @SQL='
    SELECT CASE TYPE WHEN ''XuQiuNumber'' THEN ProductName ELSE '''' END ProductName,
        CASE TYPE WHEN ''XuQiuNumber'' THEN SupplyName ELSE '''' END SupplyName,        
        Date = CASE TYPE WHEN ''XuQiuNumber'' THEN ''Order Demand'' WHEN ''AlreadyNumber'' THEN ''Schedule'' ELSE ''Balance'' END,
        '+@S1+',Total
    FROM (
        SELECT [ProductName],[SupplyName],
            CONVERT(VARCHAR,Monday,23) Monday,VALUE,TYPE,
            SUM(VALUE)OVER(PARTITION BY ProductName,SupplyName,Type) Total
        FROM (SELECT * FROM VIEW_XuQiuGongYingTongji WHERE Monday>='''+convert(CHAR,@date,23)+''') and Monday<='''+convert(CHAR,@dateend,23)+''') VIEW_XuQiuGongYingTongji
            UNPIVOT(VALUE FOR TYPE IN(XuQiuNumber,AlreadyNumber, Balance)) B
        ) A
        PIVOT(MAX(VALUE) FOR Monday IN('+@S+')) B 
    ORDER BY B.ProductName,CASE TYPE WHEN ''XuQiuNumber'' THEN 1 WHEN ''AlreadyNumber'' THEN 2 ELSE 3  END 
'
EXEC(@SQL)    set nocount off
END

Answer 1

我要具体代码，我市sql菜菜鸟，看不懂，只能用你们提供的直接调试。坐等Tony。

Answer 2

--> 生成测试数据表: [VIEW_XuQiuGongYingTongji]
--IF OBJECT_ID('[VIEW_XuQiuGongYingTongji]') IS NOT NULL
-- DROP TABLE [VIEW_XuQiuGongYingTongji]
--GO
CREATE TABLE [VIEW_XuQiuGongYingTongji] ([XuQiuNumber] [int],[ProductName] [nvarchar](10),[SupplyName] [nvarchar](10),[Monday] [datetime],[AlreadyNumber] [int],[Balance] [int])
INSERT INTO [VIEW_XuQiuGongYingTongji]
SELECT '1000','A001','深圳公司','2010-07-26','800','-200' UNION ALL
SELECT '3000','A001','深圳公司','2010-08-2','3000','0' UNION ALL
SELECT '5000','A001','深圳公司','2010-08-9','4000','-1000' UNION ALL
SELECT '1000','A001','深圳公司','2010-08-16','500','-500' UNION ALL
SELECT '400','A001','深圳公司','2010-08-23','400','0' UNION ALL
SELECT '400','A001','深圳公司','2010-08-30','200','-200' UNION ALL
SELECT '800','A002','上海公司','2010-07-26','800','0' UNION ALL
SELECT '2000','A002','上海公司','2010-08-2','1500','-500' UNION ALL
SELECT '100','A002','上海公司','2010-08-9','100','0' UNION ALL
SELECT '1000','A002','上海公司','2010-08-16','500','-500' UNION ALL
SELECT '400','A002','上海公司','2010-08-23','400','0' UNION ALL
SELECT '400','A002','上海公司','2010-08-30','300','-100'--SELECT * FROM [VIEW_XuQiuGongYingTongji]-->SQL查询如下:
IF OBJECT_ID('[dbo].[Search_TongJi]')>0
    DROP PROCEDURE [dbo].[Search_TongJi]
GO
CREATE PROCEDURE [dbo].[Search_TongJi] 
    @date DateTime, --开始时间
    @dateend DateTime --结束时间
AS
SET NOCOUNT ON    
DECLARE @s VARCHAR(8000),@c VARCHAR(8000),@date1 VARCHAR(10),@date2 VARCHAR(10)
SELECT @c=ISNULL(@c+',','')+QUOTENAME(Monday),
       @s=ISNULL(@s,'')+',SUM(CASE CONVERT(VARCHAR,Monday,23) WHEN '''+Monday+''' THEN VALUE ELSE 0 END)['+Monday+']'
FROM (SELECT DISTINCT CONVERT(VARCHAR,Monday,23) Monday FROM VIEW_XuQiuGongYingTongji WHERE Monday BETWEEN @date AND @dateend) t
SELECT @date1=CONVERT(VARCHAR,@date,23),@date2=CONVERT(VARCHAR,@dateend,23)
EXEC('
    SELECT CASE DATA WHEN ''需求'' THEN a ELSE '''' END ProductName,
        CASE DATA WHEN ''需求'' THEN SupplyName ELSE '''' END SupplyName,Data,
        '+@c+',Total
    FROM (
        SELECT [ProductName] a,[SupplyName],
            DATA = CASE TYPE WHEN 1 THEN ''需求'' WHEN 2 THEN ''供应'' ELSE ''差额'' END
            '+@s+',SUM(VALUE) Total
        FROM (
                 SELECT ProductName, SupplyName, Monday,TYPE=1, [XuQiuNumber] VALUE 
                 FROM VIEW_XuQiuGongYingTongji 
                 WHERE Monday>='''+@date1+''' and Monday<='''+@date2+'''
                 UNION ALL
                 SELECT ProductName, SupplyName, Monday,TYPE=2, [AlreadyNumber] 
                 FROM VIEW_XuQiuGongYingTongji 
                 WHERE Monday>='''+@date1+''' and Monday<='''+@date2+'''
                 UNION ALL
                 SELECT ProductName, SupplyName, Monday,TYPE=3, [Balance] 
                 FROM VIEW_XuQiuGongYingTongji 
                 WHERE Monday>='''+@date1+''' and Monday<='''+@date2+'''
             ) a
        GROUP BY [ProductName],[SupplyName],CASE TYPE WHEN 1 THEN ''需求'' WHEN 2 THEN ''供应'' ELSE ''差额'' END
    ) t
    ORDER BY t.a,CASE DATA WHEN ''需求'' THEN 1 WHEN ''供应'' THEN 2 ELSE 3 END
')
SET NOCOUNT OFF 
GOEXEC [dbo].[Search_TongJi] '20100101','20101231'
/*
ProductName SupplyName Data 2010-07-26  2010-08-02  2010-08-09  2010-08-16  2010-08-23  2010-08-30  Total
----------- ---------- ---- ----------- ----------- ----------- ----------- ----------- ----------- -----------
A001        深圳公司       需求   1000        3000        5000        1000        400         400         10800
                       供应   800         3000        4000        500         400         200         8900
                       差额   -200        0           -1000       -500        0           -200        -1900
A002        上海公司       需求   800         2000        100         1000        400         400         4700
                       供应   800         1500        100         500         400         300         3600
                       差额   0           -500        0           -500        0           -100        -1100*/

Answer 3

--> 测试数据: [VIEW_XuQiuGongYingTongji]
if object_id('[VIEW_XuQiuGongYingTongji]') is not null drop table [VIEW_XuQiuGongYingTongji]
create table [VIEW_XuQiuGongYingTongji] (XuQiuNumber int,ProductName varchar(4),SupplyName varchar(8),Monday datetime,AlreadyNumber int,Balance int)
insert into [VIEW_XuQiuGongYingTongji]
select 1000,'A001','深圳公司','2010-07-26',800,-200 union all
select 3000,'A001','深圳公司','2010-08-2',3000,0 union all
select 5000,'A001','深圳公司','2010-08-9',4000,-1000 union all
select 1000,'A001','深圳公司','2010-08-16',500,-500 union all
select 400,'A001','深圳公司','2010-08-23',400,0 union all
select 400,'A001','深圳公司','2010-08-30',200,-200 union all
select 800,'A002','上海公司','2010-07-26',800,0 union all
select 2000,'A002','上海公司','2010-08-2',1500,-500 union all
select 100,'A002','上海公司','2010-08-9',100,0 union all
select 1000,'A002','上海公司','2010-08-16',500,-500 union all
select 400,'A002','上海公司','2010-08-23',400,0 union all
select 400,'A002','上海公司','2010-08-30',300,-100
godeclare @sql1 varchar(8000),@sql2 varchar(8000),@sql3 varchar(8000)
set @sql1='select x=productname,supplyname,px=1,date=''需求'''
set @sql2='select x=productname,'''',2,date=''供应'''
set @sql3='select x=productname,'''',3,date=''平衡'''
select @sql1=@sql1+',['+Monday+']=sum(case convert(varchar(10),Monday,120) when '''+Monday+''' then XuQiuNumber else 0 end)',
@sql2=@sql2+',['+Monday+']=sum(case convert(varchar(10),Monday,120) when '''+Monday+''' then AlreadyNumber else 0 end)',
@sql3=@sql3+',['+Monday+']=sum(case convert(varchar(10),Monday,120) when '''+Monday+''' then balance else 0 end)'
from (select distinct Monday=convert(varchar(10),Monday,120) from VIEW_XuQiuGongYingTongji)a
set @sql1=@sql1+',Total=sum(XuQiuNumber) from VIEW_XuQiuGongYingTongji group by productname,supplyname'
set @sql2=@sql2+',sum(AlreadyNumber) from VIEW_XuQiuGongYingTongji group by productname,supplyname'
set @sql3=@sql3+',sum(balance) from VIEW_XuQiuGongYingTongji group by productname,supplyname'
exec('select productname=case px when 1 then x else '''' end,* from ('+@sql1+' union all '+@sql2+' union all '+@sql3+')a order by x,px')
--结果：productname x    supplyname px          date 2010-07-26  2010-08-02  2010-08-09  2010-08-16  2010-08-23  2010-08-30  Total
----------- ---- ---------- ----------- ---- ----------- ----------- ----------- ----------- ----------- ----------- -----------
A001        A001 深圳公司       1           需求   1000        3000        5000        1000        400         400         10800
            A001            2           供应   800         3000        4000        500         400         200         8900
            A001            3           平衡   -200        0           -1000       -500        0           -200        -1900
A002        A002 上海公司       1           需求   800         2000        100         1000        400         400         4700
            A002            2           供应   800         1500        100         500         400         300         3600
            A002            3           平衡   0           -500        0           -500        0           -100        -1100

Answer 4

IF OBJECT_ID('[tb]') IS NOT NULL
    DROP TABLE [tb]
GO
CREATE TABLE [tb] (XuQiuNumber varchar(10),ProductName varchar(10),SupplyName varchar(10),Monday datetime,AlreadyNumber [int],Balance [int])
INSERT INTO [tb]
select '1000','A001','深圳公司','2010-07-26','800','-200' union all
select '3000','A001','深圳公司','2010-08-2','3000','0' union all
select '5000','A001','深圳公司','2010-08-9','4000',' -1000' union all
select '1000','A001','深圳公司','2010-08-16','500','-500' union all
select '400','A001','深圳公司','2010-08-23','400',' 0' union all
select '400','A001','深圳公司','2010-08-30','200','-200' union all
select '800','A002','上海公司','2010-07-26','800',' 0' union all
select '2000','A002','上海公司','2010-08-2','1500','-500' union all
select '100','A002','上海公司','2010-08-9','100',' 0' union all
select '1000','A002','上海公司','2010-08-16','500','-500' union all
select '400','A002','上海公司','2010-08-23','400','0' union all
select '400','A002','上海公司','2010-08-30','300','-100'
go
--- 查询
declare @s0 varchar(8000),@s1 varchar(8000), @s2 varchar(8000), @s3 varchar(8000)
select @s0='',
@s1='select ProductName ,SupplyName,Data=''需求'',sort=1',
@s2='select ProductName ,SupplyName,Data=''供应'',sort=2',
@s3='select ProductName ,SupplyName,Data=''平衡'',sort=3'
select 
@s0=@s0+',['+convert(varchar(10),Monday,120)+']',
@s1=@s1+',max(case when convert(varchar(10),Monday,120) = '''+convert(varchar(10),Monday,120)+''' then XuQiuNumber else null end) as ['+convert(varchar(10),Monday,120)+']',
@s2=@s2+',max(case when convert(varchar(10),Monday,120) = '''+convert(varchar(10),Monday,120)+''' then AlreadyNumber else null end) as ['+convert(varchar(10),Monday,120)+']',
@s3=@s3+',max(case when convert(varchar(10),Monday,120) = '''+convert(varchar(10),Monday,120)+''' then Balance else null end) as ['+convert(varchar(10),Monday,120)+']'
from (select distinct Monday from tb where Monday between '2010-07-26' and '2010-08-30') a ---条件在这行设置
select 
@s0='select case when sort=1 then ProductName else '''' end as [ProductName],
case when sort=1 then SupplyName else '''' end as [SupplyName] ,Data'+@s0+',Total',
@s1=@s1+' ,sum(cast(XuQiuNumber as int)) as Total from tb group by ProductName ,SupplyName ',
@s2=@s2+' ,sum(AlreadyNumber) as Total from tb group by ProductName ,SupplyName ',
@s3=@s3+' ,sum(Balance) as Total from tb group by ProductName ,SupplyName 'exec (@s0+' from ('+@s1+' union all '+@s2+' union all '+@s3+')t order by t.ProductName, t.sort')
/*(12 行受影响)
ProductName SupplyName Data 2010-07-26  2010-08-02  2010-08-09  2010-08-16  2010-08-23  2010-08-30  Total
----------- ---------- ---- ----------- ----------- ----------- ----------- ----------- ----------- -----------
A001        深圳公司       需求   1000        3000        5000        1000        400         400         10800
                       供应   800         3000        4000        500         400         200         8900
                       平衡   -200        0           -1000       -500        0           -200        -1900
A002        上海公司       需求   800         2000        100         1000        400         400         4700
                       供应   800         1500        100         500         400         300         3600
                       平衡   0           -500        0           -500        0           -100        -1100
警告: 聚合或其他 SET 操作消除了空值。(6 行受影响)*/