求教高手一条高难度SQL语句
表1
PO ITEM_NO QTY DEST_NAME _120 _125 _130 ... _330 _XO
11 22 30 AAAA 0 0 15 15 0
11 22 45 BBBB 0 10 20 10 0
11 22 15 DDDD 0 0 0 0 15
字段中_120 _125 _XO等是鞋子的尺寸(有很多的尺寸,这里简写了)
转化成表2
PO ITEM_NO QTY SIZE AAAA BBBB CCCC DDDD
11 22 30 _125 15 0 0 0
11 22 30 _330 15 0 0 0
11 22 45 _125 0 10 0 0
11 22 45 _130 0 20 0 0
11 22 45 _330 0 10 0 0
11 22 15 _XO 0 10 0 15
没有数量的鞋子尺寸不会出现在表2里,同时会列出所有的订货商.
烦请高手帮忙看下,小弟在此感激万分!
[解决办法]
select PO, ITEM_NO, QTY,SIZE = '_120 ',
max(case when DEST_NAME = 'AAAA ' then _120 else 0 end) 'AAAA ',
max(case when DEST_NAME = 'bbbb ' then _120 else 0 end) 'bbbb ',
max(case when DEST_NAME = 'cccc ' then _120 else 0 end) 'cccc ',
max(case when DEST_NAME = 'dddd ' then _120 else 0 end) 'dddd '
from tb group by PO, ITEM_NO, QTY
union all
select PO, ITEM_NO, QTY,SIZE = '_125 ',
max(case when DEST_NAME = 'AAAA ' then _125 else 0 end) 'AAAA ',
max(case when DEST_NAME = 'bbbb ' then _125 else 0 end) 'bbbb ',
max(case when DEST_NAME = 'cccc ' then _125 else 0 end) 'cccc ',
max(case when DEST_NAME = 'dddd ' then _125 else 0 end) 'dddd '
from tb group by PO, ITEM_NO, QTY
..............
union all
select PO, ITEM_NO, QTY,SIZE = '_XO ',
max(case when DEST_NAME = 'AAAA ' then _XO else 0 end) 'AAAA ',
max(case when DEST_NAME = 'bbbb ' then _XO else 0 end) 'bbbb ',
max(case when DEST_NAME = 'cccc ' then _XO else 0 end) 'cccc ',
max(case when DEST_NAME = 'dddd ' then _XO else 0 end) 'dddd '
from tb group by PO, ITEM_NO, QTY
[解决办法]
--創建測試環境
Create Table 表1
(PO Int,
ITEM_NO Int,
QTY Int,
DEST_NAME Varchar(10),
_120 Int,
_125 Int,
_130 Int,
_330 Int,
_XO Int)
--插入數據
Insert 表1 Select 11, 22, 30, 'AAAA ', 0, 0, 15, 15, 0
Union All Select 11, 22, 45, 'BBBB ', 0, 10, 20, 10, 0
Union All Select 11, 22, 15, 'DDDD ', 0, 0, 0, 0, 15
GO
--測試
--列名應該會有很多，手寫比較長，使用動態語句來做
Declare @S Varchar(8000)
Select @S = ' '
Select @S = @S + ' Union Select PO, ITEM_NO, QTY, ' ' ' + Name + ' ' ' As [SIZE], Max(Case DEST_NAME When ' 'AAAA ' ' Then ' + Name + ' Else 0 End) As AAAA, Max(Case DEST_NAME When ' 'BBBB ' ' Then ' + Name + ' Else 0 End) As BBBB, Max(Case DEST_NAME When ' 'CCCC ' ' Then ' + Name + ' Else 0 End) As CCCC, Max(Case DEST_NAME When ' 'DDDD ' ' Then ' + Name + ' Else 0 End) As DDDD From 表1 Group By PO, ITEM_NO, QTY '
From SysColumns Where ID = OBJECT_ID( '表1 ') And Name Not In( 'PO ', 'ITEM_NO ', 'QTY ', 'DEST_NAME ') Order By ColID
Select @S = 'Select * From ( ' + Stuff(@S, 1, 6, ' ') + ' ) A Where AAAA + BBBB + CCCC + DDDD > 0 '
EXEC(@S)
GO
--刪除測試環境
Drop Table 表1
--結果
/*
POITEM_NOQTYSIZEAAAABBBBCCCCDDDD
112215_XO00015
112230_13015000
112230_33015000
112245_12501000
112245_13002000
112245_33001000
*/
[解决办法]
declare @size varchar(8000),@DEST_NAME varchar(8000),@sql varchar(8000)
select @size= ' ',@DEST_NAME= ' '
--size
select @size=@size+ 'select PO, ITEM_NO, QTY,DEST_NAME, ' ' '+name+ ' ' '[SIZE], '+name+ '[allsize] from tab1 union all '
from syscolumns where id=object_id( 'tab1 ')and left(name,1)= '_ '
--DEST_NAME(如果有DEST_NAME表换DEST_NAME表)
select @DEST_NAME=@DEST_NAME+ ',(case when DEST_NAME= ' ' '+DEST_NAME+ ' ' ' then allsize end) ' ' '+DEST_NAME+ ' ' ' ' from tab1
set @sql= 'select [PO],[ITEM_NO],[QTY],[SIZE] '+@DEST_NAME+ 'from ( '+left(@size,len(@size)-10)+ ')a where allsize <> 0 '
exec(@sql)
PO ITEM_NO QTY SIZE AAAA BBBB DDDD
----------- ----------- ----------- ---- ----------- ----------- -----------
11 22 45 _125 NULL 10 NULL
11 22 30 _130 15 NULL NULL
11 22 45 _130 NULL 20 NULL
11 22 30 _330 15 NULL NULL
11 22 45 _330 NULL 10 NULL
11 22 15 _XO NULL NULL 15
