本人对SQL行变列难以理解。在此向各位高手请教。问题如下：
有一个表或视图如下：
说明：ProductName：产品编号；SupplyName：供应商；xuqiutime：需求时间；xuqiu：我司需求量；gongying：供应商可提供量；balance:差额。xuqiutime日期为未来6个星期，以每周的星期一为准的。每个产品只有1个供应商。
差额balance=gongying-xuqiu.ProductName SupplyName xuqiutime xuqiu(需求) gongying(供应) balance(差额)
No1 深圳公司 2010-05-24 200 200 0
No1 深圳公司 2010-05-31 300 200 -100
No1 深圳公司 2010-06-07 400 1000 600
No1 深圳公司 2010-06-14 800 1000 200
No1 深圳公司 2010-06-21 800 0 -800
No1 深圳公司 2010-06-28 500 1000 500No2 上海公司 2010-05-24 500 1000 500
No2 上海公司 2010-05-31 500 1500 1000
No2 上海公司 2010-06-07 800 1000 200
...
No2 上海公司 2010-06-28 1600 1000 -600我要使用SQL语句将其变为如下格式:ProductName SupplyName 2010-05-24 2010-05-31 2010-06-07 ...... 2010-06-28
No1 深圳公司 200 300 400 500 --该列为需求
200 200 1000 1000 --该列为供应
0 -100 600 500 --该列为差
No2 上海公司 500 500 800 600 --该列为需求
1000 1500 1000 1000 --该列为供应
500 1000 200 -600 --该列为差
......
要求：
１．只要ＳＱＬ语句，不要存储过程，我直接用Gridview显示的。
２．问一下，假如某一个公司比如深圳公司忘记添加2010-06-28号的需求和供应，查询出来时能不能显示为０。该贴问题类似于Tony回答的：
http://topic.csdn.net/u/20100515/20/dd7e42dd-1e73-4910-8646-3e4ff230e6c6.html
该题的显示效果类似于：http://www.bccyy.com/Flash/VM.bmp，不必按图片显示做，只要求按照上面的格式即可，该图片做参考的。请sql高手指教
有一个表或视图如下：
说明：ProductName：产品编号；SupplyName：供应商；xuqiutime：需求时间；xuqiu：我司需求量；gongying：供应商可提供量；balance:差额。xuqiutime日期为未来6个星期，以每周的星期一为准的。每个产品只有1个供应商。
差额balance=gongying-xuqiu.ProductName SupplyName xuqiutime xuqiu(需求) gongying(供应) balance(差额)
No1 深圳公司 2010-05-24 200 200 0
No1 深圳公司 2010-05-31 300 200 -100
No1 深圳公司 2010-06-07 400 1000 600
No1 深圳公司 2010-06-14 800 1000 200
No1 深圳公司 2010-06-21 800 0 -800
No1 深圳公司 2010-06-28 500 1000 500No2 上海公司 2010-05-24 500 1000 500
No2 上海公司 2010-05-31 500 1500 1000
No2 上海公司 2010-06-07 800 1000 200
...
No2 上海公司 2010-06-28 1600 1000 -600我要使用SQL语句将其变为如下格式:ProductName SupplyName 2010-05-24 2010-05-31 2010-06-07 ...... 2010-06-28
No1 深圳公司 200 300 400 500 --该列为需求
200 200 1000 1000 --该列为供应
0 -100 600 500 --该列为差
No2 上海公司 500 500 800 600 --该列为需求
1000 1500 1000 1000 --该列为供应
500 1000 200 -600 --该列为差
......
要求：
１．只要ＳＱＬ语句，不要存储过程，我直接用Gridview显示的。
２．问一下，假如某一个公司比如深圳公司忘记添加2010-06-28号的需求和供应，查询出来时能不能显示为０。该贴问题类似于Tony回答的：
http://topic.csdn.net/u/20100515/20/dd7e42dd-1e73-4910-8646-3e4ff230e6c6.html
该题的显示效果类似于：http://www.bccyy.com/Flash/VM.bmp，不必按图片显示做，只要求按照上面的格式即可，该图片做参考的。请sql高手指教
本人对SQL行变列难以理解。在此向各位高手请教。问题如下：
有一个表或视图如下：
说明：ProductName：产品编号；SupplyName：供应商；xuqiutime：需求时间；xuqiu：我司需求量；gongying：供应商可提供量；balance:差额。xuqiutime日期为未来6个星期，以每周的星期一为准的。每个产品只有1个供应商。
差额balance=gongying-xuqiu.ProductName SupplyName xuqiutime xuqiu(需求) gongying(供应) balance(差额)
No1 深圳公司 2010-05-24 200 200 0
No1 深圳公司 2010-05-31 300 200 -100
No1 深圳公司 2010-06-07 400 1000 600
No1 深圳公司 2010-06-14 800 1000 200
No1 深圳公司 2010-06-21 800 0 -800
No1 深圳公司 2010-06-28 500 1000 500No2 上海公司 2010-05-24 500 1000 500
No2 上海公司 2010-05-31 500 1500 1000
No2 上海公司 2010-06-07 800 1000 200
...
No2 上海公司 2010-06-28 1600 1000 -600我要使用SQL语句将其变为如下格式:ProductName SupplyName 2010-05-24 2010-05-31 2010-06-07 ...... 2010-06-28
No1 深圳公司 200 300 400 500 --该列为需求
200 200 1000 1000 --该列为供应
0 -100 600 500 --该列为差
No2 上海公司 500 500 800 600 --该列为需求
1000 1500 1000 1000 --该列为供应
500 1000 200 -600 --该列为差
......
/******************************************************************************************************************************************************
以学生成绩为例子，比较形象易懂整理人：中国风(Roy)日期:2008.06.06
******************************************************************************************************************************************************/--1、行互列
--> --> (Roy)生成測試數據
if not object_id('Class') is null
drop table Class
Go
Create table Class([Student] nvarchar(2),[Course] nvarchar(2),[Score] int)
Insert Class
select N'张三',N'语文',78 union all
select N'张三',N'数学',87 union all
select N'张三',N'英语',82 union all
select N'张三',N'物理',90 union all
select N'李四',N'语文',65 union all
select N'李四',N'数学',77 union all
select N'李四',N'英语',65 union all
select N'李四',N'物理',85
Go
--2000方法:
动态:declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'
from Class group by[Course]
exec('select [Student]'+@s+' from Class group by [Student]')
生成静态:select
[Student],
[数学]=max(case when [Course]='数学' then [Score] else 0 end),
[物理]=max(case when [Course]='物理' then [Score] else 0 end),
[英语]=max(case when [Course]='英语' then [Score] else 0 end),
[语文]=max(case when [Course]='语文' then [Score] else 0 end)
from
Class
group by [Student]GO
动态:declare @s nvarchar(4000)
Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]
exec('select * from Class pivot (max([Score]) for [Course] in('+@s+'))b')生成静态:
select *
from
Class
pivot
(max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b生成格式:
/*
Student 数学 物理 英语 语文
------- ----------- ----------- ----------- -----------
李四 77 85 65 65
张三 87 90 82 78(2 行受影响)
*/------------------------------------------------------------------------------------------
go
--加上总成绩(学科平均分)--2000方法:
动态:declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'
from Class group by[Course]
exec('select [Student]'+@s+',[总成绩]=sum([Score]) from Class group by [Student]')--加多一列(学科平均分用avg([Score]))生成动态:select
[Student],
[数学]=max(case when [Course]='数学' then [Score] else 0 end),
[物理]=max(case when [Course]='物理' then [Score] else 0 end),
[英语]=max(case when [Course]='英语' then [Score] else 0 end),
[语文]=max(case when [Course]='语文' then [Score] else 0 end),
[总成绩]=sum([Score]) --加多一列(学科平均分用avg([Score]))
from
Class
group by [Student]go--2005方法:动态:declare @s nvarchar(4000)
Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course] --isnull(@s+',','') 去掉字符串@s中第一个逗号
exec('select [Student],'+@s+',[总成绩] from (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a
pivot (max([Score]) for [Course] in('+@s+'))b ')生成静态:select
[Student],[数学],[物理],[英语],[语文],[总成绩]
from
(select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a --平均分时用avg([Score])
pivot
(max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b 生成格式:/*
Student 数学 物理 英语 语文 总成绩
------- ----------- ----------- ----------- ----------- -----------
李四 77 85 65 65 292
张三 87 90 82 78 337(2 行受影响)
*/go--2、列转行
--> --> (Roy)生成測試數據
if not object_id('Class') is null
drop table Class
Go
Create table Class([Student] nvarchar(2),[数学] int,[物理] int,[英语] int,[语文] int)
Insert Class
select N'李四',77,85,65,65 union all
select N'张三',87,90,82,78
Go--2000:动态:declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+'select [Student],[Course]='+quotename(Name,'''')--isnull(@s+' union all ','') 去掉字符串@s中第一个union all
+',[Score]='+quotename(Name)+' from Class'
from syscolumns where ID=object_id('Class') and Name not in('Student')--排除不转换的列
order by Colid
exec('select * from ('+@s+')t order by [Student],[Course]')--增加一个排序生成静态:
select *
from (select [Student],[Course]='数学',[Score]=[数学] from Class union all
select [Student],[Course]='物理',[Score]=[物理] from Class union all
select [Student],[Course]='英语',[Score]=[英语] from Class union all
select [Student],[Course]='语文',[Score]=[语文] from Class)t
order by [Student],[Course]go
--2005:动态:declare @s nvarchar(4000)
select @s=isnull(@s+',','')+quotename(Name)
from syscolumns where ID=object_id('Class') and Name not in('Student')
order by Colid
exec('select Student,[Course],[Score] from Class unpivot ([Score] for [Course] in('+@s+'))b')go
select
Student,[Course],[Score]
from
Class
unpivot
([Score] for [Course] in([数学],[物理],[英语],[语文]))b生成格式:
/*
Student Course Score
------- ------- -----------
李四 数学 77
李四 物理 85
李四 英语 65
李四 语文 65
张三 数学 87
张三 物理 90
张三 英语 82
张三 语文 78(8 行受影响)
*/
-- Author : htl258(Tony)
-- Date : 2010-05-18 15:23:55
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
------------------------------------------------------------------------------------> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([ProductName] [nvarchar](10),[SupplyName] [nvarchar](10),[xuqiutime] [datetime],[xuqiu] [int],[gongying] [int],[balance] [int])
INSERT INTO [tb]
SELECT 'No1','深圳公司','2010-05-24','200','200','0' UNION ALL
SELECT 'No1','深圳公司','2010-05-31','300','200','-100' UNION ALL
SELECT 'No1','深圳公司','2010-06-07','400','1000','600' UNION ALL
SELECT 'No1','深圳公司','2010-06-14','800','1000','200' UNION ALL
SELECT 'No1','深圳公司','2010-06-21','800','0','-800' UNION ALL
SELECT 'No1','深圳公司','2010-06-28','500','1000','500' UNION ALL
SELECT 'No2','上海公司','2010-05-24','500','1000','500' UNION ALL
SELECT 'No2','上海公司','2010-05-31','500','1500','1000' UNION ALL
SELECT 'No2','上海公司','2010-06-07','800','1000','200' UNION ALL
SELECT 'No2','上海公司','2010-06-28','1600','1000','-600'--SELECT * FROM [tb]-->SQL查询如下:
DECLARE @S VARCHAR(8000),@s1 VARCHAR(8000)
SELECT @S=isnull(@S+',','')+quotename(convert(VARCHAR,xuqiutime,23)) FROM tb GROUP BY [xuqiutime]
SELECT @S1=isnull(@S1+',','')+'isnull('+quotename(convert(VARCHAR,xuqiutime,23))+',0)'+quotename(convert(VARCHAR,xuqiutime,23))
FROM tb GROUP BY [xuqiutime]
EXEC('
SELECT CASE TYPE WHEN ''xuqiu'' THEN ProductName ELSE '''' END ProductName,
CASE TYPE WHEN ''xuqiu'' THEN SupplyName ELSE '''' END SupplyName,'+@S1+'
FROM (
SELECT [ProductName],[SupplyName],
convert(VARCHAR,xuqiutime,23) xuqiutime,VALUE,TYPE
FROM tb
UNPIVOT(VALUE FOR TYPE IN(xuqiu,gongying,balance)) B
) A
PIVOT(MAX(VALUE) FOR xuqiutime IN('+@S+')) B
ORDER BY B.ProductName,CASE TYPE WHEN ''xuqiu'' THEN 1 WHEN ''gongying'' THEN 2 ELSE 3 END
')
/*
ProductName SupplyName 2010-05-24 2010-05-31 2010-06-07 2010-06-14 2010-06-21 2010-06-28
----------- ---------- ----------- ----------- ----------- ----------- ----------- -----------
No1 深圳公司 200 300 400 800 800 500
200 200 1000 1000 0 1000
0 -100 600 200 -800 500
No2 上海公司 500 500 800 0 0 1600
1000 1500 1000 0 0 1000
500 1000 200 0 0 -600(6 行受影响)
*/
ProductName varchar(10), SupplyName varchar(20),xuqiutime datetime, xuqiu int, gongying int, balance int)
insert tb
select 'No1', '深圳公司', '2010-05-24', 200, 200, 0 union all
select 'No1', '深圳公司', '2010-05-31', 300, 200, -100 union all
select 'No1', '深圳公司', '2010-06-07', 400, 1000, 600 union all
select 'No1', '深圳公司', '2010-06-14', 800, 1000, 200 union all
select 'No1', '深圳公司', '2010-06-21', 800, 0, -800 union all
select 'No1', '深圳公司', '2010-06-28', 500, 1000, 500 union all
select 'No2', '上海公司', '2010-05-24', 500, 1000, 500 union all
select 'No2', '上海公司', '2010-05-31', 500, 1500, 1000 union all
select 'No2', '上海公司', '2010-06-07', 800, 1000, 200 union all
select 'No2', '上海公司', '2010-06-28', 1600, 1000, -600
SELECT ProductName ,SupplyName,[2010-05-24],[2010-05-31],[2010-06-07],[2010-06-14] FROM
(
SELECT ProductName,SupplyName,xuqiutime,data,dd
FROM
(SELECT ProductName,SupplyName,xuqiutime, xuqiu,gongying,balance
FROM tb) p
UNPIVOT
(data FOR dd IN
(xuqiu,gongying,balance)
)AS unpvt
) T
PIVOT
(MAX(data)
for xuqiutime in ([2010-05-24],[2010-05-31],[2010-06-07],[2010-06-14])
)as pt
/*
ProductName SupplyName 2010-05-24 2010-05-31 2010-06-07 2010-06-14
----------- -------------------- ----------- ----------- ----------- -----------
No1 深圳公司 0 -100 600 200
No1 深圳公司 200 200 1000 1000
No1 深圳公司 200 300 400 800
No2 上海公司 500 1000 200 NULL
No2 上海公司 1000 1500 1000 NULL
No2 上海公司 500 500 800 NULL（所影响的行数为 6 行）
*/
写不了动态，
写个静态 基础点的，
想再麻烦一下Tony，我想在原来的基础上加上2列Date，SUM，效果为：ProductName SupplyName Date 2010-05-24 2010-05-31 2010-06-07 ...... 2010-06-28 Sum
No1 深圳公司 需求 200 300 400 500 1400
供应 200 200 1000 1000 2240
0 -100 600 500 1000
No2 上海公司 需求 500 500 800 600
供应 1000 1500 1000 1000 500 1000 200 -600 Date为说明列，无数据。后面Sum为行的总和，可否实现，Tony？
-- Author : htl258(Tony)
-- Date : 2010-05-18 15:23:55
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
------------------------------------------------------------------------------------> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([ProductName] [nvarchar](10),[SupplyName] [nvarchar](10),[xuqiutime] [datetime],[xuqiu] [int],[gongying] [int],[balance] [int])
INSERT INTO [tb]
SELECT 'No1','深圳公司','2010-05-24','200','200','0' UNION ALL
SELECT 'No1','深圳公司','2010-05-31','300','200','-100' UNION ALL
SELECT 'No1','深圳公司','2010-06-07','400','1000','600' UNION ALL
SELECT 'No1','深圳公司','2010-06-14','800','1000','200' UNION ALL
SELECT 'No1','深圳公司','2010-06-21','800','0','-800' UNION ALL
SELECT 'No1','深圳公司','2010-06-28','500','1000','500' UNION ALL
SELECT 'No2','上海公司','2010-05-24','500','1000','500' UNION ALL
SELECT 'No2','上海公司','2010-05-31','500','1500','1000' UNION ALL
SELECT 'No2','上海公司','2010-06-07','800','1000','200' UNION ALL
SELECT 'No2','上海公司','2010-06-28','1600','1000','-600'--SELECT * FROM [tb]-->SQL查询如下:
DECLARE @S VARCHAR(8000),@s1 VARCHAR(8000)
SELECT @S=isnull(@S+',','')+quotename(convert(VARCHAR,xuqiutime,23)) FROM tb GROUP BY [xuqiutime]
SELECT @S1=isnull(@S1+',','')+'isnull('+quotename(convert(VARCHAR,xuqiutime,23))+',0)'+quotename(convert(VARCHAR,xuqiutime,23))
FROM tb GROUP BY [xuqiutime]
EXEC('
SELECT CASE TYPE WHEN ''xuqiu'' THEN ProductName ELSE '''' END ProductName,
CASE TYPE WHEN ''xuqiu'' THEN SupplyName ELSE '''' END SupplyName,
Data = CASE TYPE WHEN ''xuqiu'' THEN ''需求'' WHEN ''gongying'' THEN ''供应'' ELSE ''差额'' END,
'+@S1+',Total
FROM (
SELECT [ProductName],[SupplyName],
CONVERT(VARCHAR,xuqiutime,23) xuqiutime,VALUE,TYPE,
SUM(VALUE)OVER(PARTITION BY ProductName,SupplyName,Type) Total
FROM tb
UNPIVOT(VALUE FOR TYPE IN(xuqiu,gongying,balance)) B
) A
PIVOT(MAX(VALUE) FOR xuqiutime IN('+@S+')) B
ORDER BY B.ProductName,CASE TYPE WHEN ''xuqiu'' THEN 1 WHEN ''gongying'' THEN 2 ELSE 3 END
')
/*
ProductName SupplyName Data 2010-05-24 2010-05-31 2010-06-07 2010-06-14 2010-06-21 2010-06-28 Total
----------- ---------- ---- ----------- ----------- ----------- ----------- ----------- ----------- -----------
No1 深圳公司 需求 200 300 400 800 800 500 3000
供应 200 200 1000 1000 0 1000 3400
差额 0 -100 600 200 -800 500 400
No2 上海公司 需求 500 500 800 0 0 1600 3400
供应 1000 1500 1000 0 0 1000 4500
差额 500 1000 200 0 0 -600 1100(6 行受影响)
*/