各位高手，我有一個復雜點的交叉表問題，如下表內容：
table為計算出某種源料在產品系列之每月的計劃數，已訂數和已訂數平均單價
------
proj periods planQty orderQty avgPrice
------------------------------------------------------
CT231 0908 22.078 125.00 9.200000
DP070 0908 NULL 40000.00 1.440000
DP082 0908 NULL 3400.00 9.900000
DP084 0908 NULL 125.00 18.000000
DP084 0909 NULL 24625.00 16.500000
DP085 0908 129.612 179275.00 3.583333
DP085 0909 214.519 24550.00 9.571111
EM115 0909 40.829 575.00 8.900000現要把它變為交叉表顯示(零可不用顯示)：
proj 0908 0909
------------------------------ ------------------------------
planQty orderQty avgPrice planQty orderQty avgPrice
--------------------------------------------------------------------------
CT231 22.078 125.00 9.20
DP070 0.00 40000.00 1.44
DP082 0.00 3400.00 9.90
DP084 0.00 125.00 18.00 0.00 24625.00 16.50
DP085 129.612 179275.00 3.59 214.519 24550.00 9.57
EM115 40.829 575.00 8.90這怎麼能實現呢？請各位幫助，謝謝！
table為計算出某種源料在產品系列之每月的計劃數，已訂數和已訂數平均單價
------
proj periods planQty orderQty avgPrice
------------------------------------------------------
CT231 0908 22.078 125.00 9.200000
DP070 0908 NULL 40000.00 1.440000
DP082 0908 NULL 3400.00 9.900000
DP084 0908 NULL 125.00 18.000000
DP084 0909 NULL 24625.00 16.500000
DP085 0908 129.612 179275.00 3.583333
DP085 0909 214.519 24550.00 9.571111
EM115 0909 40.829 575.00 8.900000現要把它變為交叉表顯示(零可不用顯示)：
proj 0908 0909
------------------------------ ------------------------------
planQty orderQty avgPrice planQty orderQty avgPrice
--------------------------------------------------------------------------
CT231 22.078 125.00 9.20
DP070 0.00 40000.00 1.44
DP082 0.00 3400.00 9.90
DP084 0.00 125.00 18.00 0.00 24625.00 16.50
DP085 129.612 179275.00 3.59 214.519 24550.00 9.57
EM115 40.829 575.00 8.90這怎麼能實現呢？請各位幫助，謝謝！
你好！ 你說的方法能不能詳細點呢？ 謝謝！
DROP TABLE [tb]
GO
CREATE TABLE [tb]([proj] nvarchar(5),[periods] nvarchar(5),[planQty] decimal(18,3),[orderQty] decimal(18,2),[avgPrice] decimal(18,6))
INSERT [tb]
SELECT N'CT231','0908',22.078,125.00,9.200000 UNION ALL
SELECT N'DP070','0908',null,40000.00,1.440000 UNION ALL
SELECT N'DP082','0908',null,3400.00,9.900000 UNION ALL
SELECT N'DP084','0908',null,125.00,18.000000 UNION ALL
SELECT N'DP084','0909',null,24625.00,16.500000 UNION ALL
SELECT N'DP085','0908',129.612,179275.00,3.583333 UNION ALL
SELECT N'DP085','0909',214.519,24550.00,9.571111 UNION ALL
SELECT N'EM115','0909',40.829,575.00,8.900000
GO
--SELECT * FROM [tb] where 1 <2-->SQL查询如下:
DECLARE @s VARCHAR(8000)
SELECT @s=ISNULL(@s,'SELECT proj')
+',MAX(CASE periods WHEN '''+periods+''' THEN [planQty] END)['+periods+'planQty]'
+',MAX(CASE periods WHEN '''+periods+''' THEN [orderQty] END)['+periods+'orderQty]'
+',MAX(CASE periods WHEN '''+periods+''' THEN [avgPrice] END)['+periods+'avgPrice]'
FROM tb GROUP BY periods ORDER BY periods
EXEC(@s+' FROM tb GROUP BY proj')
/*
proj 0908planQty 0908orderQty 0908avgPrice 0909planQty 0909orderQty 0909avgPrice
----- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
CT231 22.078 125.00 9.200000 NULL NULL NULL
DP070 NULL 40000.00 1.440000 NULL NULL NULL
DP082 NULL 3400.00 9.900000 NULL NULL NULL
DP084 NULL 125.00 18.000000 NULL 24625.00 16.500000
DP085 129.612 179275.00 3.583333 214.519 24550.00 9.571111
EM115 NULL NULL NULL 40.829 575.00 8.900000(6 行受影响)
*/你這個情況好像更簡單
-- Author :fredrickhu(小F，向高手学习)
-- Date :2009-09-12 16:53:49
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([proj] varchar(5),[periods] varchar(4),[planQty] numeric(6,3),[orderQty] numeric(8,2),[avgPrice] numeric(8,6))
insert [tb]
select 'CT231','0908',22.078,125.00,9.200000 union all
select 'DP070','0908',null,40000.00,1.440000 union all
select 'DP082','0908',null,3400.00,9.900000 union all
select 'DP084','0908',null,125.00,18.000000 union all
select 'DP084','0909',null,24625.00,16.500000 union all
select 'DP085','0908',129.612,179275.00,3.583333 union all
select 'DP085','0909',214.519,24550.00,9.571111 union all
select 'EM115','0909',40.829,575.00,8.900000
--------------开始查询--------------------------
select
proj,
max(case periods when '0908' then [planQty] end)[0908planQty],
max(case periods when '0908' then [orderQty] end)[0908orderQty],
max(case periods when '0908' then [avgPrice] end)[0908avgPrice],
max(case periods when '0909' then [planQty] end)[0909planQty],
max(CASE periods when '0909' then [orderQty] end)[0909orderQty],
max(case periods when '0909' then [avgPrice] end)[0909avgPrice]
from
tb
group by
proj----------------结果----------------------------
/*proj 0908planQty 0908orderQty 0908avgPrice 0909planQty 0909orderQty 0909avgPrice
----- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
CT231 22.078 125.00 9.200000 NULL NULL NULL
DP070 NULL 40000.00 1.440000 NULL NULL NULL
DP082 NULL 3400.00 9.900000 NULL NULL NULL
DP084 NULL 125.00 18.000000 NULL 24625.00 16.500000
DP085 129.612 179275.00 3.583333 214.519 24550.00 9.571111
EM115 NULL NULL NULL 40.829 575.00 8.900000
警告: 聚合或其他 SET 操作消除了空值。(6 行受影响)
*/
/*
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c)
1988-2008 Microsoft Corporation Enterprise Evaluation Edition on Windows NT 5.1 <X86>
(Build 2600: Service Pack 3)
愿和大家共同进步
如有雷同、实属巧合
●●●●●2009-09-12 20:32:02.700●●●●
★★★★★soft_wsx★★★★★
*/
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([proj] varchar(5),[periods] varchar(4),[planQty] numeric(6,3),[orderQty] numeric(8,2),[avgPrice] numeric(8,6))
insert [tb]
select 'CT231','0908',22.078,125.00,9.200000 union all
select 'DP070','0908',null,40000.00,1.440000 union all
select 'DP082','0908',null,3400.00,9.900000 union all
select 'DP084','0908',null,125.00,18.000000 union all
select 'DP084','0909',null,24625.00,16.500000 union all
select 'DP085','0908',129.612,179275.00,3.583333 union all
select 'DP085','0909',214.519,24550.00,9.571111 union all
select 'EM115','0909',40.829,575.00,8.900000
declare @sql nvarchar(4000)
SET @sql=N'select [proj]' --初始化变量必须
select @sql=@sql+N','+
QUOTENAME([periods]+N'_planQty')+
N'=max(
case when [periods]='+quotename([periods],N'''')
+N' then [planQty] end)'
+N','+
QUOTENAME([periods]+N'_orderQty')+
N'=max(
case when [periods]='+quotename([periods],N'''')
+N' then [orderQty] end)'+
N','+
QUOTENAME([periods]+N'_avgPrice')+
N'=max(
case when [periods]='+quotename([periods],N'''')
+N' then [avgPrice] end)'
from tb group by [periods]
set @sql=@sql+N' from tb group by proj'
print @sql
exec(@sql)--动态SQL生成的语句
select [proj],[0908_planQty]=max(
case when [periods]='0908' then [planQty] end),[0908_orderQty]=max(
case when [periods]='0908' then [orderQty] end),[0908_avgPrice]=max(
case when [periods]='0908' then [avgPrice] end),[0909_planQty]=max(
case when [periods]='0909' then [planQty] end),[0909_orderQty]=max(
case when [periods]='0909' then [orderQty] end),[0909_avgPrice]=max(
case when [periods]='0909' then [avgPrice] end) from tb group by proj
/*
proj 0908_planQty 0908_orderQty 0908_avgPrice 0909_planQty 0909_orderQty 0909_avgPrice
CT231 22.078 125.00 9.200000 NULL NULL NULL
DP070 NULL 40000.00 1.440000 NULL NULL NULL
DP082 NULL 3400.00 9.900000 NULL NULL NULL
DP084 NULL 125.00 18.000000 NULL 24625.00 16.500000
DP085 129.612 179275.00 3.583333 214.519 24550.00 9.571111
EM115 NULL NULL NULL 40.829 575.00 8.900000
*/动态报表！
planqty decimal(10,3),orderqty decimal(10,2),avgprice decimal(10,6))
insert into @tb select 'ct231',0908,22.078,125.00,9.200000
union all select 'dp070',0908,null,40000.00,1.440000
union all select 'dp082',0908,null,3400.00,9.900000
union all select 'dp084',0908,null,125.00,18.000000
union all select 'dp084',0909,null,40000.00,1.440000
union all select 'dp085',0908,129.612,179275.00,3.583333
union all select 'dp085',0909,214.519,24550.00,9.571111
union all select 'em115',0909,40.829,575.00,8.900000
select isnull(a.[908_proj],b.[909_proj]) proj,
a.[908_periods],isnull(a.[908_planqty],0) [908_planqty],
a.[908_orderqty], a.[908_avgprice],b.[909_periods],
b.[909_planqty],b.[909_orderqty],b.[909_avgprice] from
(select proj as [908_proj],periods as [908_periods],planqty as [908_planqty],
orderqty as [908_orderqty],avgprice as [908_avgprice] from @tb where periods=908) a
full join
(select proj as [909_proj],periods as [909_periods],planqty as [909_planqty],
orderqty as [909_orderqty],avgprice as [909_avgprice]
from @tb where periods=909) b
on a.[908_proj]=b.[909_proj]
order by proj
proj 908_periods 908_planqty 908_orderqty 908_avgprice 909_periods 909_planqty 909_orderqty 909_avgprice
---------- ----------- --------------------------------------- --------------------------------------- --------------------------------------- ----------- --------------------------------------- --------------------------------------- ---------------------------------------
ct231 908 22.078 125.00 9.200000 NULL NULL NULL NULL
dp070 908 0.000 40000.00 1.440000 NULL NULL NULL NULL
dp082 908 0.000 3400.00 9.900000 NULL NULL NULL NULL
dp084 908 0.000 125.00 18.000000 909 NULL 40000.00 1.440000
dp085 908 129.612 179275.00 3.583333 909 214.519 24550.00 9.571111
em115 NULL 0.000 NULL NULL 909 40.829 575.00 8.900000(6 行受影响)