亂馬客

https://rainmakerho.github.io/

2013-11-12

[SQL]PIVOT + UNION ALL 的應用

本文章說明使用PIVOT + UNION ALL來將彙總資料行一列一列的加上去

image

有朋友在問想要達到上圖的功能，要如何做呢?

一看到要將某欄位的資料以多欄來呈現，就會想到使用 PIVOT 。

 PIVOT 一次只能用一個彙總資料行，那如果要多行的話，可以參考「PIVOT使用多個彙總資料行(Multiple Aggregates)」。

而這次的需求是要那些彙總資料行一列一列的加上去，這時，筆者想到的就是使用 UNION ALL 來加入，如下，


--先建立測試的Table & 資料
USE tempdb
go
--drop table [dbo].[Sheet2]
CREATE TABLE [dbo].[Sheet2](
                [DistrictName] [nvarchar](255) NULL,
                [DistrictID] [nvarchar](255) NULL,
                [StyleID] [nvarchar](255) NULL,
                [區間系列數量] [float] NULL,
                [區間系列銷售金額] [float] NULL,
                [去年系列數量] [float] NULL,
                [去年系列銷售金額] [float] NULL,
                [前年系列數量] [float] NULL,
                [前年系列銷售金額] [float] NULL
) ON [PRIMARY]
 
GO
INSERT [dbo].[Sheet2] ([DistrictName], [DistrictID], [StyleID], [區間系列數量], [區間系列銷售金額], [去年系列數量], [去年系列銷售金額], [前年系列數量], [前年系列銷售金額]) VALUES (N'北區', N'北區', N'傳統', 0, 0, 1, 13178, 0, 0)
GO
INSERT [dbo].[Sheet2] ([DistrictName], [DistrictID], [StyleID], [區間系列數量], [區間系列銷售金額], [去年系列數量], [去年系列銷售金額], [前年系列數量], [前年系列銷售金額]) VALUES (N'北區', N'北區', N'休閒', 0, 0, 1, 13479, 0, 0)
GO
INSERT [dbo].[Sheet2] ([DistrictName], [DistrictID], [StyleID], [區間系列數量], [區間系列銷售金額], [去年系列數量], [去年系列銷售金額], [前年系列數量], [前年系列銷售金額]) VALUES (N'中區', N'中區', N'傳統', 12, 70168460.73, 12, 76756774.01, 12, 76466446.76)
GO
INSERT [dbo].[Sheet2] ([DistrictName], [DistrictID], [StyleID], [區間系列數量], [區間系列銷售金額], [去年系列數量], [去年系列銷售金額], [前年系列數量], [前年系列銷售金額]) VALUES (N'中區', N'中區', N'現代', 9, 10335226.26, 9, 33287173.21, 8, 13748774.32)
GO
INSERT [dbo].[Sheet2] ([DistrictName], [DistrictID], [StyleID], [區間系列數量], [區間系列銷售金額], [去年系列數量], [去年系列銷售金額], [前年系列數量], [前年系列銷售金額]) VALUES (N'中區', N'中區', N'休閒', 9, 22249265.97, 9, 44831288.9, 9, 31917989.87)
GO
INSERT [dbo].[Sheet2] ([DistrictName], [DistrictID], [StyleID], [區間系列數量], [區間系列銷售金額], [去年系列數量], [去年系列銷售金額], [前年系列數量], [前年系列銷售金額]) VALUES (N'南區', N'南區', N'傳統', 12, 34738905.12, 12, 31058522.49, 12, 26617728.18)
GO
INSERT [dbo].[Sheet2] ([DistrictName], [DistrictID], [StyleID], [區間系列數量], [區間系列銷售金額], [去年系列數量], [去年系列銷售金額], [前年系列數量], [前年系列銷售金額]) VALUES (N'南區', N'南區', N'現代', 9, 5728309.98, 9, 6696724.21, 8, 6097510.16)
GO
INSERT [dbo].[Sheet2] ([DistrictName], [DistrictID], [StyleID], [區間系列數量], [區間系列銷售金額], [去年系列數量], [去年系列銷售金額], [前年系列數量], [前年系列銷售金額]) VALUES (N'南區', N'南區', N'休閒', 9, 10829249.09, 9, 10446786.25, 9, 12767264.61)
GO
SELECT * FROM Sheet2;

image

 


--先取出欄位名稱(使用動態的方式來串出欄位名稱)
DECLARE @qty_columns NVARCHAR(MAX)  
SELECT  @qty_columns = COALESCE(@qty_columns + ',['  + RTRIM(T.DS)
                                + ']', '['  + RTRIM(T.DS) + ']')  
FROM    ( SELECT DISTINCT DistrictName + '-' + StyleID AS DS
          FROM     dbo.Sheet2
        ) T
PRINT @qty_columns
--將欄位名稱串接進去
EXEC(N'SELECT T2.* 
FROM 
(
SELECT  *
FROM    ( SELECT DistrictName + ''-'' + StyleID AS GP
                                                , [區間系列數量] 
                                                , N''[區間系列數量]'' as [區域/風格]
                                                FROM dbo.Sheet2 
        ) AS SourceTable PIVOT
                ( SUM([區間系列數量]) FOR GP IN (' +  @qty_columns + ' ) ) 
                AS p1
UNION ALL
SELECT  *
FROM    ( SELECT DistrictName + ''-'' +  StyleID AS GP
                                                , [區間系列銷售金額] 
                                                , N''[區間系列銷售金額]'' as [區域/風格]
                                                FROM dbo.Sheet2 
        ) AS SourceTable PIVOT
                ( SUM([區間系列銷售金額]) FOR GP IN (' +  @qty_columns + ' ) ) 
                AS p2
UNION ALL
SELECT  *
FROM    ( SELECT DistrictName + ''-'' + StyleID AS GP
                                                , [去年系列數量] 
                                                , N''[去年系列數量]'' as [區域/風格]
                                                FROM dbo.Sheet2 
        ) AS SourceTable PIVOT
                ( SUM([去年系列數量]) FOR GP IN (' +  @qty_columns + ' ) ) 
                AS p3
UNION ALL
SELECT  *
FROM    ( SELECT DistrictName  + ''-'' + StyleID AS GP
                                                , [去年系列銷售金額] 
                                                , N''[去年系列銷售金額]'' as [區域/風格]
                                                FROM dbo.Sheet2 
        ) AS SourceTable PIVOT
                ( SUM([去年系列銷售金額]) FOR GP IN (' +  @qty_columns + ' ) ) 
                AS p4
UNION ALL
SELECT  *
FROM    ( SELECT DistrictName  + ''-'' +  StyleID AS GP
                                                , [前年系列數量] 
                                                , N''[前年系列數量]'' as [區域/風格]
                                                FROM dbo.Sheet2 
        ) AS SourceTable PIVOT
                ( SUM([前年系列數量]) FOR GP IN (' +  @qty_columns + ' ) ) 
                AS p5
UNION ALL
SELECT  *
FROM    ( SELECT DistrictName  + ''-'' +  StyleID AS GP
                                                , [前年系列銷售金額] 
                                                , N''[前年系列銷售金額]'' as [區域/風格]
                                                FROM dbo.Sheet2 
        ) AS SourceTable PIVOT
                ( SUM([前年系列銷售金額]) FOR GP IN (' +  @qty_columns + ' ) ) 
                AS p6 )   T2
');

image

 

這樣看起來似乎沒有依Sort來排序，所以可以建立相關的Sort Table，如下，


--建立排序用的Table
--區
CREATE TABLE District
(
[DistrictName] [nvarchar](255) NULL,
[Sort_Order] INT,
);
go

INSERT INTO District(DistrictName, Sort_Order) VALUES(N'北區', 10);
INSERT INTO District(DistrictName, Sort_Order) VALUES(N'中區', 20);
INSERT INTO District(DistrictName, Sort_Order) VALUES(N'南區', 30);
 
--風格
CREATE TABLE Style
(
[StyleID] [nvarchar](255) NULL,
[Sort_Order] INT,
);
GO
INSERT INTO Style([StyleID], Sort_Order) VALUES(N'現代', 10);
INSERT INTO Style([StyleID], Sort_Order) VALUES(N'傳統', 20);
INSERT INTO Style([StyleID], Sort_Order) VALUES(N'休閒', 30);
GO
 
--SELECT TOP 99 A.DistrictName  + '-' + B.StyleID
--FROM District A, Style B
--ORDER BY A.Sort_Order, B.Sort_Order;

--先取出欄位名稱(使用動態的方式來串出欄位名稱)
DECLARE @qty_columns NVARCHAR(MAX)  
SELECT  @qty_columns = COALESCE(@qty_columns + ',['  + RTRIM(T.DS)
                                + ']', '['  + RTRIM(T.DS) + ']')  
FROM    ( SELECT TOP(99) A.DistrictName  + '-' + B.StyleID AS DS
			FROM District A, Style B
			ORDER BY A.Sort_Order, B.Sort_Order
        ) T
PRINT @qty_columns
--將欄位名稱串接進去
EXEC(N'SELECT T2.* 
FROM 
(
SELECT  *
FROM    ( SELECT DistrictName + ''-'' + StyleID AS GP
                                                , [區間系列數量] 
                                                , N''[區間系列數量]'' as [區域/風格]
                                                FROM dbo.Sheet2 
        ) AS SourceTable PIVOT
                ( SUM([區間系列數量]) FOR GP IN (' +  @qty_columns + ' ) ) 
                AS p1
UNION ALL
SELECT  *
FROM    ( SELECT DistrictName + ''-'' +  StyleID AS GP
                                                , [區間系列銷售金額] 
                                                , N''[區間系列銷售金額]'' as [區域/風格]
                                                FROM dbo.Sheet2 
        ) AS SourceTable PIVOT
                ( SUM([區間系列銷售金額]) FOR GP IN (' +  @qty_columns + ' ) ) 
                AS p2
UNION ALL
SELECT  *
FROM    ( SELECT DistrictName + ''-'' + StyleID AS GP
                                                , [去年系列數量] 
                                                , N''[去年系列數量]'' as [區域/風格]
                                                FROM dbo.Sheet2 
        ) AS SourceTable PIVOT
                ( SUM([去年系列數量]) FOR GP IN (' +  @qty_columns + ' ) ) 
                AS p3
UNION ALL
SELECT  *
FROM    ( SELECT DistrictName  + ''-'' + StyleID AS GP
                                                , [去年系列銷售金額] 
                                                , N''[去年系列銷售金額]'' as [區域/風格]
                                                FROM dbo.Sheet2 
        ) AS SourceTable PIVOT
                ( SUM([去年系列銷售金額]) FOR GP IN (' +  @qty_columns + ' ) ) 
                AS p4
UNION ALL
SELECT  *
FROM    ( SELECT DistrictName  + ''-'' +  StyleID AS GP
                                                , [前年系列數量] 
                                                , N''[前年系列數量]'' as [區域/風格]
                                                FROM dbo.Sheet2 
        ) AS SourceTable PIVOT
                ( SUM([前年系列數量]) FOR GP IN (' +  @qty_columns + ' ) ) 
                AS p5
UNION ALL
SELECT  *
FROM    ( SELECT DistrictName  + ''-'' +  StyleID AS GP
                                                , [前年系列銷售金額] 
                                                , N''[前年系列銷售金額]'' as [區域/風格]
                                                FROM dbo.Sheet2 
        ) AS SourceTable PIVOT
                ( SUM([前年系列銷售金額]) FOR GP IN (' +  @qty_columns + ' ) ) 
                AS p6 )   T2
');

所以，欄位就會依排序的方式來呈現哦!

image

 

參考資料

PIVOT使用多個彙總資料行(Multiple Aggregates)

UNION ALL

使用 PIVOT 和 UNPIVOT

使用PIVOT

Hi, 

亂馬客Blog已移到了 「亂馬客​ : Re:從零開始的軟體開發生活」

請大家繼續支持 ^_^

學習網站

pluralsight

wintellectnow

標籤雲

系列文章

最新留言