日期:2014-05-17 浏览次数:20515 次
CREATE TABLE #tmp_QA_INLINE_Month(
[BUYER_PO_DEL_DATE] VARCHAR(7) NULL, --日期
[PRODUCT_LINE_NO] NVARCHAR(25) NULL, --组别
[DEFECT_GARMENT_QTY] INT NULL , --疵点点数
[AUDITED_QTY] INT NULL, --检查件数
);
INSERT INTO #tmp_QA_INLINE_Month
SELECT '09-2011', 'SW12AB', 5, 42 UNION ALL
SELECT '09-2011', 'SW15AB', 40 ,228 UNION ALL
SELECT '10-2011', 'SW05', 1, 3 UNION ALL
SELECT '10-2011', 'SW12AB', 166 ,870 UNION ALL
SELECT '10-2011', 'SW04', 1, 9 UNION ALL
SELECT '10-2011', 'SW14AB', 129,718
declare @s nvarchar(MAX)
SELECT @s=isnull(@s+',','')+quotename(PRODUCT_LINE_NO)
from #tmp_QA_INLINE_Month
group BY PRODUCT_LINE_NO
exec('
select * from #tmp_QA_INLINE_Month PIVOT (sum(DEFECT_GARMENT_QTY) for PRODUCT_LINE_NO in('+@s+'))Z
')
-- 我想要的如果还需要将 [AUDITED_QTY] 也进行行列转换。类似于这种写法 PIVOT (sum(DEFECT_GARMENT_QTY) for PRODUCT_LINE_NO in('+@s+'))Z
-- 可是PIVOT 不支持两个,请问我该如何解决?
declare @s nvarchar(max)
SELECT @s=isnull(@s+',','')+quotename(PRODUCT_LINE_NO)
from #tmp_QA_INLINE_Month
group BY PRODUCT_LINE_NO
exec(';with cte1 as
(select BUYER_PO_DEL_DATE,'+@s+' from #tmp_QA_INLINE_Month PIVOT (sum(DEFECT_GARMENT_QTY) for PRODUCT_LINE_NO in('+@s+'))Z)
,cte2 as
(select BUYER_PO_DEL_DATE,'+@s+' from #tmp_QA_INLINE_Month PIVOT (sum(AUDITED_QTY) for PRODUCT_LINE_NO in('+@s+'))Z)
select a.*,b.* from cte1 a inner join cte2 b on a.BUYER_PO_DEL_DATE=b.BUYER_PO_DEL_DATE
')
exec(';with cte1 as
(select * from (select [BUYER_PO_DEL_DATE],[PRODUCT_LINE_NO],[DEFECT_GARMENT_QTY] from #tmp_QA_INLINE_Month)a
PIVOT (sum(DEFECT_GARMENT_QTY) for PRODUCT_LINE_NO in('+@s+'))Z )
select * from cte1
')