SQL 2012 - 透视和反透视 [英] SQL 2012 - Pivot and Unpivot

问题描述

我在一个类似于此的表格中汇总了数据:

I have summarised data in a table which is similar to this:

Customer Year Month No_trans spend  points
1        2015 1     30       400    10
1        2015 2     20       150    5
1        2015 3     10       500    15
2        2015 1     5        100    7

我想尝试使用 Pivot/Unpivot 改成这个，可以吗?

I would like to try and use Pivot/Unpivot to change to this, is it possible?

??????   Customer 2015_1 2015_2 2015_3  
No_trans 1        30     20     10               
Spend    1        400    150    500
Points   1        10     5      15
No_trans 2        5      0      0               
Spend    2        100    0      0
Points   2        7      0      0

谢谢

推荐答案

您可以使用动态 SQL 来转置表:

You could use dynamic SQL to transpose table:

DECLARE @cols NVARCHAR(MAX) = 
                STUFF((SELECT DISTINCT ',' + QUOTENAME(CONCAT([Year], '_', [Month]))
                      FROM #tab
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)') 
                     , 1, 1, '');

DECLARE @query NVARCHAR(MAX) = 
FORMATMESSAGE(
N'SELECT col_name, customer, %s
FROM (SELECT [year_month] = CONCAT([Year], ''_'', [Month]),
             Customer, No_Trans, spend, points
      FROM #tab) AS sub
UNPIVOT
(
    val FOR col_name  IN (No_trans, spend, points)
) AS unpvt
PIVOT
(
    MAX(val) FOR [year_month] IN (%s)
) AS pvt
ORDER BY customer, col_name;', @cols, @cols); 

EXEC [dbo].[sp_executesql] @query;

LiveDemo

输出:

╔══════════╦══════════╦════════╦════════╦════════╗
║   col    ║ customer ║ 2015_1 ║ 2015_2 ║ 2015_3 ║
╠══════════╬══════════╬════════╬════════╬════════╣
║ No_Trans ║        1 ║     30 ║     20 ║     10 ║
║ points   ║        1 ║     10 ║      5 ║     15 ║
║ spend    ║        1 ║    400 ║    150 ║    500 ║
║ No_Trans ║        2 ║      5 ║        ║        ║
║ points   ║        2 ║      7 ║        ║        ║
║ spend    ║        2 ║    100 ║        ║        ║
╚══════════╩══════════╩════════╩════════╩════════╝

如果您希望 zeros 在缺失的位置，您可以使用 ISNULL/COALESCE.请注意，并非每个数据类型都可以替换为 0 (int)

If you want zeros at missing position you could use ISNULL/COALESCE. Be aware that not every datatatype could be replaced by 0 (int)

LiveDemo2

FORMATMESSAGE 是一种奇特的方式用字符串替换 %s.它可以通过简单的 REPLACE 轻松更改:

FORMATMESSAGE is fancy way to replace %s with string. It could be easily changed by simple REPLACE:

 DECLARE @query NVARCHAR(MAX) = 
 N'SELECT col1, col2, <placeholder>
   FROM ...
   ...
   PIVOT( MAX(col) IN col2 IN (<placeholder>)
   ...';

 SET @query = REPLACE(@query, '<placeholder', @cols);

 -- for debug
 PRINT @query;

工作原理:

1. 在子查询中生成[year_month]列
2. UNPIVOT 数据(列到行)
3. PIVOT 结果(行到列)
4. 用动态 SQL 包裹它，允许在不知道 [year_month] 的情况下生成列.

1. Generate [year_month] column in subquery
2. UNPIVOT data (columns to row)
3. PIVOT result (rows to columns)
4. Wrap it with dynamic-SQL to allow generate columns without knowing [year_month] in advance.

这篇关于SQL 2012 - 透视和反透视的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！