高手请进 关于多表动态行列转换

Question

-- 创建表
create table product
(
prod_id char (5) primary key, --产品编号
prod_name char(20) not null --产品名称
)
go
-- 插入数据
INSERT INTO PRODUCT VALUES('P0001','16M DRAM')
INSERT INTO PRODUCT VALUES('P0002','14寸显示器')
INSERT INTO PRODUCT VALUES('P0003','1.2GB硬盘')
INSERT INTO PRODUCT VALUES('P0004','3.5寸软驱')
INSERT INTO PRODUCT VALUES('P0005','键盘')
INSERT INTO PRODUCT VALUES('P0006','VGA 显示卡')
INSERT INTO PRODUCT VALUES('P0007','网卡')
INSERT INTO PRODUCT VALUES('P0008','PENTIUM 100 CPU')
INSERT INTO PRODUCT VALUES('P0009','激光打印机')
INSERT INTO PRODUCT VALUES('P0010','8倍光驱')
INSERT INTO PRODUCT VALUES('P0011','计算机字典')
INSERT INTO PRODUCT VALUES('P0012','9600 bit/s MODEM')
INSERT INTO PRODUCT VALUES('P0013','pentium 主板')
-- 数据显示
P0001 16M DRAM            
P0002 14寸显示器          
P0003 1.2GB硬盘           
P0004 3.5寸软驱           
P0005 键盘                
P0006 VGA 显示卡          
P0007 网卡                
P0008 PENTIUM 100 CPU     
P0009 激光打印机          
P0010 8倍光驱             
P0011 计算机字典          
P0012 9600 bit/s MODEM    
P0013 pentium 主板
        
-- 创建表 sale_item
create table sale_item
(
order_no int not null, --定单编号
prod_id char(5) not null,--产品编号
sup_id char(5) not null, --供应商编号
qty int not null, --销售数量
unit_price numeric (7,2) not null, --单价
order_date datetime null,--定单日期
constraint pk_sale_item primary key clustered(order_no,prod_id,sup_id) 
)
-- 插入数据
INSERT INTO SALE_ITEM VALUES(10001,'P0001','S0001',5,2500.00,'1996/10/22')
INSERT INTO SALE_ITEM VALUES(10001,'P0002','S0001',3,6500.00,'1996/10/22')
INSERT INTO SALE_ITEM VALUES(10001,'P0003','S0001',2,5300.00,'1996/10/22')
INSERT INTO SALE_ITEM VALUES(10001,'P0004','S0001',2,1600.00,'1996/10/22')
INSERT INTO SALE_ITEM VALUES(10002,'P0001','S0002',3,2600.00,'1996/11/10')
INSERT INTO SALE_ITEM VALUES(10002,'P0003','S0001',1,5300.00,'1996/11/10')
INSERT INTO SALE_ITEM VALUES(10002,'P0008','S0004',2,4800.00,'1996/11/10')
INSERT INTO SALE_ITEM VALUES(10003,'P0001','S0002',4,2700.00,'1996/10/15')
INSERT INTO SALE_ITEM VALUES(10003,'P0004','S0003',2,1580.00,'1996/10/15')-- 显示
10001    P0001    S0001    5    2500.00    1996-10-22 00:00:00.000
10001    P0002    S0001    3    6500.00    1996-10-22 00:00:00.000
10001    P0003    S0001    2    5300.00    1996-10-22 00:00:00.000
10001    P0004    S0001    2    1600.00    1996-10-22 00:00:00.000
10002    P0001    S0002    3    2600.00    1996-11-10 00:00:00.000
10002    P0003    S0001    1    5300.00    1996-11-10 00:00:00.000
10002    P0008    S0004    2    4800.00    1996-11-10 00:00:00.000
10003    P0001    S0002    4    2700.00    1996-10-15 00:00:00.000
10003    P0004    S0003    2    1580.00    1996-10-15 00:00:00.000
       。。    。        。  。。/**
列1 为时间列 
列2  为 prod_id列 对应的主表prod_name
每一列显示的结果 要求 时间 和 prod_id 对应 
**/
-- 想通过查询输出的结果 
     order_date              16M DRAM  14寸显示器  1.2GB硬盘   3.5寸软驱    PENTIUM 100 CPU     
1996-10-22 00:00:00.000      2500.00    6500.00     5300.00     1600.00      noll
1996-11-10 00:00:00.000      2600.00    null        5300.00      null        4800.00
1996-10-15 00:00:00.000      2700.00    null         null       1580.00      null
                             。。        。。               
注 。。 表示省略号 因为还有很多数据 提供给大家的就这么多

Answer 1

DECLARE @S VARCHAR(8000)
SET @s='select Order_date'
SELECT @s=@s+','+QUOTENAME(prod_name)+'=sum(case when prod_id='+RTRIM(prod_id)+' then qty*order_date end)' FROM productEXEC(@s+' from sale_item group by Order_date')

Answer 2

本帖最后由 roy_88 于 2011-09-23 12:46:01 编辑

  

Answer 3

declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + P.prod_name from sale_item A LEFT JOIN product P ON A.prod_id = P.prod_id  group by P.prod_name
set @sql = '[' + @sql + ']'
exec ('select * from (select A.order_date,A.unit_price,P.prod_name from sale_item A LEFT JOIN product P ON A.prod_id = P.prod_id) a pivot (max(unit_price) for prod_name in (' + @sql + ')) b')
/*
order_date              1.2GB硬盘                                 14寸显示器                                  16M DRAM                                3.5寸软驱                                  PENTIUM 100 CPU     
----------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
1996-10-15 00:00:00.000 NULL                                    NULL                                    2700.00                                 1580.00                                 NULL
1996-10-22 00:00:00.000 5300.00                                 6500.00                                 2500.00                                 1600.00                                 NULL
1996-11-10 00:00:00.000 5300.00                                 NULL                                    2600.00                                 NULL                                    4800.00(3 行受影响)
*/
??? 昨天那个问题的扩展？

Answer 4

DECLARE @S NVARCHAR(4000)
SET @s='select Order_date'
SELECT @s=@s+','+QUOTENAME(prod_name)+'=sum(case when prod_id='''+RTRIM(prod_id)+''' then unit_price end)' FROM productEXEC(@s+' from sale_item group by Order_date order by 2')
/*
Order_date 16M DRAM             14寸显示器               1.2GB硬盘              3.5寸软驱               键盘                   VGA 显示卡              网卡                   PENTIUM 100 CPU      激光打印机                8倍光驱                 计算机字典                9600 bit/s MODEM     pentium 主板          
1996-10-22 00:00:00.000 2500.00 6500.00 5300.00 1600.00 NULL NULL NULL NULL NULL NULL NULL NULL NULL
1996-11-10 00:00:00.000 2600.00 NULL 5300.00 NULL NULL NULL NULL 4800.00 NULL NULL NULL NULL NULL
1996-10-15 00:00:00.000 2700.00 NULL NULL 1580.00 NULL NULL NULL NULL NULL NULL NULL NULL NULL
*/

Answer 5

樓主是看不明動態吧?--------
用Print @s 顯示寫成的語句

Answer 6

declare @sql varchar(8000)
set @sql = 'select convert(varchar(10),order_date,120)  as order_date '
select @sql = @sql + ' , sum(case when prod_id = ''' + prod_id + ''' then unit_price  else null end) [' + prod_id + ']'
from (select distinct prod_id from sale_item) as a
set @sql = @sql + ' from sale_item group by convert(varchar(10),order_date,120)'
exec(@sql) 

Answer 7

DECLARE @S NVARCHAR(4000)--声明一个变量
SET @s='select Order_date'  --赋变量初值
SELECT @s=@s+','+QUOTENAME(prod_name)+'=sum(case when prod_id='''+RTRIM(prod_id)+''' then unit_price end)' FROM product --'+'是连接字符串的，QUOTENAME自己去查一下意思EXEC(@s+' from sale_item group by Order_date order by 2')
-- 这段仍然是将前面的拼接进来

Answer 8

DECLARE @S NVARCHAR(4000)
SET @s='select Order_date'
SELECT @s=@s+','+QUOTENAME(prod_name)+'=sum(case when prod_id='''+RTRIM(prod_id)+''' then unit_price end)' FROM productPRINT @S--顯示生成的語句
/*select Order_date,
[16M DRAM]=sum(case when prod_id='P0001' then unit_price end),
[14寸显示器]=sum(case when prod_id='P0002' then unit_price end),
[1.2GB硬盘]=sum(case when prod_id='P0003' then unit_price end),
[3.5寸软驱]=sum(case when prod_id='P0004' then unit_price end),
[键盘]=sum(case when prod_id='P0005' then unit_price end),
[VGA 显示卡]=sum(case when prod_id='P0006' then unit_price end),
[网卡]=sum(case when prod_id='P0007' then unit_price end),
[PENTIUM 100 CPU]=sum(case when prod_id='P0008' then unit_price end),
[激光打印机]=sum(case when prod_id='P0009' then unit_price end),
[8倍光驱]=sum(case when prod_id='P0010' then unit_price end),
[计算机字典]=sum(case when prod_id='P0011' then unit_price end),
[9600 bit/s MODEM]=sum(case when prod_id='P0012' then unit_price end),
[pentium 主板]=sum(case when prod_id='P0013' then unit_price end)
*/PRINT @s+' from sale_item group by Order_date'
/*
select Order_date,
[16M DRAM]=sum(case when prod_id='P0001' then unit_price end),
[14寸显示器]=sum(case when prod_id='P0002' then unit_price end),
[1.2GB硬盘]=sum(case when prod_id='P0003' then unit_price end),
[3.5寸软驱]=sum(case when prod_id='P0004' then unit_price end),
[键盘]=sum(case when prod_id='P0005' then unit_price end),
[VGA 显示卡]=sum(case when prod_id='P0006' then unit_price end),
[网卡]=sum(case when prod_id='P0007' then unit_price end),
[PENTIUM 100 CPU]=sum(case when prod_id='P0008' then unit_price end),
[激光打印机]=sum(case when prod_id='P0009' then unit_price end),
[8倍光驱]=sum(case when prod_id='P0010' then unit_price end),
[计算机字典]=sum(case when prod_id='P0011' then unit_price end),
[9600 bit/s MODEM]=sum(case when prod_id='P0012' then unit_price end),
[pentium 主板]=sum(case when prod_id='P0013' then unit_price end)
 from sale_item 
 group by Order_date
*/
把表1的char改為varchar--這樣可去掉空格

Answer 9

不明白 为什么  
prod_name  prod_id  是两个表的数据 为什么from product一个表就可以查处两个表的数据，不报错
代码：QUOTENAME(prod_name)+'=sum(case when prod_id='''+prod_id +''' then unit_price else 
0  end)' 
FROM product换句话说就是不明白他的执行过程 我把代码连到了一起 请看
select CONVERT(VARCHAR(10),Order_date,120)[' + '时间'+'],
QUOTENAME(prod_name) = (sum(case when prod_id=prod_id then unit_price else 0  end))
FROM product
from sale_item group by CONVERT(VARCHAR(10),Order_date,120) order by 2到底是怎么个执行过程 谢谢大家

Answer 10

前面都是生成語句,print可分部份看exec(..)--這一段才是執行語句]--樓主這樣執行看結果，用個變量是把結果生成一個字符串
SELECT 
','+QUOTENAME(prod_name)+'=sum(case when prod_id='''+RTRIM(prod_id)+''' then unit_price end)' 
FROM product