多列动态数据透视表 [英] Multi Column Dynamic Pivot Table
本文介绍了多列动态数据透视表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在尝试为多列获取一个干净的数据透视表.
I am trying to get a clean Pivot Table for multiple columns.
创建输入表
create table #temp (
ORDER_ID INT NOT NULL,
TEST_PLAN INT NOT NULL,
COLLECTION_TYPE INT NOT NULL,
TEST_GRP INT NOT NULL,
TEST INT NOT NULL
)
INSERT INTO #temp (ORDER_ID,TEST_PLAN,COLLECTION_TYPE,TEST_GRP,TEST) VALUES (1,1,2,1360942998,1360943100)
INSERT INTO #temp (ORDER_ID,TEST_PLAN,COLLECTION_TYPE,TEST_GRP,TEST) VALUES (2,1,2,1360943006,1360943079)
INSERT INTO #temp (ORDER_ID,TEST_PLAN,COLLECTION_TYPE,TEST_GRP,TEST) VALUES (1,2,2,1360942845,1360943173)
INSERT INTO #temp (ORDER_ID,TEST_PLAN,COLLECTION_TYPE,TEST_GRP,TEST) VALUES (2,2,2,1360942845,1360943134)
INSERT INTO #temp (ORDER_ID,TEST_PLAN,COLLECTION_TYPE,TEST_GRP,TEST) VALUES (3,2,2,1360942845,1360943189)
INSERT INTO #temp (ORDER_ID,TEST_PLAN,COLLECTION_TYPE,TEST_GRP,TEST) VALUES (1,3,2,1360942998,1360943100)
结果...
ORDER_ID PLAN COLLECTION_TYPE TEST_GRP TEST
-------- ---- --------------- ---------- ----------
1 1 2 1360942998 1360943100
2 1 2 1360943006 1360943079
1 2 1 1360942845 1360943173
2 2 1 1360942845 1360943134
3 2 1 1360942845 1360943189
1 3 2 1360942998 1360943100
我想执行以下操作，将ORDER_ID附加到COLLECTION_TYPE，TEST_GRP和TEST列中
I would like the following, where the ORDER_ID is appended to the COLLECTION_TYPE,TEST_GRP, and TEST columns
PLAN COLLECTION_TYPE_1 TEST_GRP_1 TEST_1 COLLECTION_TYPE_2 TEST_GRP_2 TEST_2 COLLECTION_TYPE_3 TEST_GRP_3 TEST_3
---- ----------------- ---------- ---------- ----------------- ---------- ---------- ----------------- ---------- ----------
1 2 1360942998 1360943100 2 1360943006 1360943079 NULL NULL NULL
2 1 1360942845 1360943173 1 1360942845 1360943134 1 1360942845 1360943189
3 2 1360942998 1360943100 NULL NULL NULL NULL NULL NULL
我有这个并且它可以工作，但是正在寻找更清洁的东西(例如，很少的空值).
I have this and it works, but was looking for something a little cleaner (e.g. Few Nulls).
DECLARE @SQL NVARCHAR(MAX),
@Cols NVARCHAR(MAX)
SELECT @cols = STUFF((select ',
MAX(CASE WHEN [TEST_PLAN]=' + CONVERT(VARCHAR,[TEST_PLAN]) + ' AND [ORDER_ID] = ' + CONVERT(VARCHAR,[ORDER_ID]) +
' THEN [TEST_GRP] ELSE NULL END) AS [TEST_GRP_' + CONVERT(VARCHAR,[ORDER_ID]) + '],
MAX(CASE WHEN [TEST_PLAN]=' + CONVERT(VARCHAR,[TEST_PLAN]) + ' AND [ORDER_ID] = ' + CONVERT(VARCHAR,[ORDER_ID]) +
' THEN [TEST_GRP] ELSE NULL END) AS [TEST_' + CONVERT(VARCHAR,[ORDER_ID]) + '],
MAX(CASE WHEN [TEST_PLAN]=' + CONVERT(VARCHAR,[TEST_PLAN]) + ' AND [ORDER_ID] = ' + CONVERT(VARCHAR,[ORDER_ID]) +
' THEN [COLLECTION_TYPE] ELSE NULL END) AS [COLLECTION_TYPE_' + CONVERT(VARCHAR,[ORDER_ID]) + ']'
FROM #temp
ORDER BY [TEST_PLAN],[ORDER_ID] FOR XML PATH(''),type).value('.','varchar(max)'),1,2,'')
SET @SQL = 'SELECT TEST_PLAN,' + @Cols + ' FROM #Temp GROUP BY TEST_PLAN'
EXECUTE( @SQL)
有输出...
TEST_PLAN TEST_GRP_1 TEST_1 COLLECTION_TYPE_1 TEST_GRP_2 TEST_2 COLLECTION_TYPE_2 TEST_GRP_1 TEST_1 COLLECTION_TYPE_1 TEST_GRP_2 TEST_2 COLLECTION_TYPE_2 TEST_GRP_3 TEST_3 COLLECTION_TYPE_3 TEST_GRP_4 TEST_4 COLLECTION_TYPE_4 TEST_GRP_5 TEST_5 COLLECTION_TYPE_5 TEST_GRP_6 TEST_6 COLLECTION_TYPE_6 TEST_GRP_7 TEST_7 COLLECTION_TYPE_7 TEST_GRP_8 TEST_8 COLLECTION_TYPE_8 TEST_GRP_9 TEST_9 COLLECTION_TYPE_9 TEST_GRP_10 TEST_10 COLLECTION_TYPE_10 TEST_GRP_1 TEST_1 COLLECTION_TYPE_1
----------- ----------- ----------- ----------------- ----------- ----------- ----------------- ----------- ----------- ----------------- ----------- ----------- ----------------- ----------- ----------- ----------------- ----------- ----------- ----------------- ----------- ----------- ----------------- ----------- ----------- ----------------- ----------- ----------- ----------------- ----------- ----------- ----------------- ----------- ----------- ----------------- ----------- ----------- ------------------ ----------- ----------- -----------------
1 1360942998 1360942998 2 1360943006 1360943006 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 NULL NULL NULL NULL NULL NULL 1360942845 1360942845 2 1360942845 1360942845 2 1360942845 1360942845 2 1360942845 1360942845 2 1360942845 1360942845 2 1360942845 1360942845 2 1360942845 1360942845 2 1360942845 1360942845 2 1360942845 1360942845 2 1360942845 1360942845 2 NULL NULL NULL
3 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1360942998 1360942998 2
我已经搜索了最接近上述sql的解决方案.
I have searched for a solution with the closest being the above sql.
谢谢 无限
推荐答案
为了获得所需的结果，我必须同时使用UNPIVOT和PIVOT函数. UNPIVOT将从列中获取值并将其转换为行，而PIVOT将从行中获取值并将其转换回列.
In order to get the result that you want, I would have to use both the UNPIVOT and the PIVOT functions. The UNPIVOT will take your values from columns and convert it to rows and the PIVOT takes the rows and converts it back to columns.
有时，先使用静态或硬编码版本的查询，然后再转换为动态SQL更为容易.静态版本为:
Sometimes it is easier to use a static or hard-coded version of the query first and then convert to dynamic SQL. The static version will be:
select [Plan],
Isnull(COLLECTION_TYPE_1, '') COLLECTION_TYPE_1,
Isnull(TEST_GRP_1, '') TEST_GRP_1,
Isnull(TEST_1, '') TEST_1,
Isnull(COLLECTION_TYPE_2, '') COLLECTION_TYPE_2,
Isnull(TEST_GRP_2, '') TEST_GRP_2,
Isnull(TEST_2, '') TEST_2,
Isnull(COLLECTION_TYPE_3, '') COLLECTION_TYPE_3,
Isnull(TEST_GRP_3, '') TEST_GRP_3,
Isnull(TEST_3, '') TEST_3
from
(
select [PLAN], col + '_'+ cast(ORDER_ID as varchar(50)) col, value
from
(
select ORDER_ID,[PLAN],COLLECTION_TYPE,TEST_GRP,TEST
from temp
) s
unpivot
(
value
for col in (COLLECTION_TYPE,TEST_GRP,TEST)
) unpiv
) src
pivot
(
max(value)
for col in (COLLECTION_TYPE_1, TEST_GRP_1, TEST_1,
COLLECTION_TYPE_2, TEST_GRP_2, TEST_2,
COLLECTION_TYPE_3, TEST_GRP_3, TEST_3)
) piv
请参见带演示的SQL小提琴.
一旦有了静态版本，就可以轻松地将其转换为动态SQL.生成动态SQL时，您可以创建一个列列表，用空字符串或其他值清除nulls替换null值.动态SQL代码为:
Once you have the static version, then you can easily convert this to dynamic SQL. When generating the dynamic SQL, you can create a list of the columns that replaces the null values with an empty string or another value which cleans up the nulls. The dynamic SQL code is:
DECLARE @cols AS NVARCHAR(MAX),
@colsNames AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(col + '_'+ cast(ORDER_ID as varchar(50)))
from temp t
cross apply
(
select 'COLLECTION_TYPE' col, 1 SortOrder
union all
select 'TEST_GRP' col, 2 SortOrder
union all
select 'TEST' col, 3 SortOrder
) c
group by col, ORDER_ID, sortorder
order by ORDER_ID, sortorder
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
select @colsNames = STUFF((SELECT ', IsNull(' + QUOTENAME(col + '_'+ cast(ORDER_ID as varchar(50)))+', '''') as '+QUOTENAME(col + '_'+ cast(ORDER_ID as varchar(50)))
from temp t
cross apply
(
select 'COLLECTION_TYPE' col, 1 SortOrder
union all
select 'TEST_GRP' col, 2 SortOrder
union all
select 'TEST' col, 3 SortOrder
) c
group by col, ORDER_ID, sortorder
order by ORDER_ID, sortorder
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT [PLAN],' + @colsNames + ' from
(
select [PLAN], col + ''_''+ cast(ORDER_ID as varchar(50)) col, value
from
(
select ORDER_ID,[PLAN],COLLECTION_TYPE,TEST_GRP,TEST
from temp
) s
unpivot
(
value
for col in (COLLECTION_TYPE,TEST_GRP,TEST)
) unpiv
) src
pivot
(
max(value)
for col in (' + @cols + ')
) p '
execute(@query)
请参见带有演示的SQL小提琴
两者都给出结果:
| PLAN | COLLECTION_TYPE_1 | TEST_GRP_1 | TEST_1 | COLLECTION_TYPE_2 | TEST_GRP_2 | TEST_2 | COLLECTION_TYPE_3 | TEST_GRP_3 | TEST_3 |
--------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | 2 | 1360942998 | 1360943100 | 2 | 1360943006 | 1360943079 | 0 | 0 | 0 |
| 2 | 1 | 1360942845 | 1360943173 | 1 | 1360942845 | 1360943134 | 1 | 1360942845 | 1360943189 |
| 3 | 2 | 1360942998 | 1360943100 | 0 | 0 | 0 | 0 | 0 | 0 |
这篇关于多列动态数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
