具有多个列标题的 TSQL 数据透视表

2021-09-10 00:00:00 sql tsql sql-server

我有一个带有动态列标题的 sql server 数据透视查询，结果如下

I have a sql server pivot query with dynamic column headers that results in something like the below

|  DATE  | Key1          | Key2          | Key 3             |     Key4  |
|--------|---------------|---------------|-------------------|-----------|
|  1     |          1    |          2    |      3            |      4    |
|  2     |       1.29400 |       0.33840 |           0.04270 |    (null) |
|  3     |      60.00000 |      70.00000 |          50.00000 | 180.00000 |

我的key"列标题字段具有子键，因此 key1 和 key2 可以具有相同的子键，我想像下面一样返回我的数据透视，以便我有多个列标题:

My 'key' column header field has subkeys so that key1 and key2 can have the same subkey, I would like to return my pivot like the below so that I have multiple column headers:

|  DATE  | Key1          | Key2          | Key 3             |     Key4  |
|--------|---------------|---------------|-------------------|-----------|
|        | subkey1       | subkey2       | subkey3           |    subKey4|
|--------|---------------|---------------|-------------------|-----------|
|  1     |          1    |          2    |      3            |      4    |
|  2     |       1.29400 |       0.33840 |           0.04270 |    (null) |
|  3     |      60.00000 |      70.00000 |          50.00000 | 180.00000 |

我目前的代码简化如下:

My current code is simplified as below:

DECLARE @cols AS NVARCHAR(MAX)
DECLARE @query AS NVARCHAR(MAX)

SELECT key, subkey, value into #temp from table

SELECT @cols = STUFF((SELECT ',' + QUOTENAME([key])
                FROM #temp
                GROUP BY [Key]
                ORDER BY [Key]
            FOR XML PATH(''), TYPE
                        ).value('.', 'NVARCHAR(MAX)') 
                    ,1,1,'')

set @query = 'SELECT [date], ' + @cols + ' from 
            (
                select [key], [date], value
                from #temp                  
            ) x
            pivot
            (
                sum(value)
                for [key] in (' + @cols + ')
            ) p
            ORDER BY [Date] asc'

execute(@query)

是否可以在 sql 中像这样堆叠我的列标题?在 excel 中，这类似于将多个数据透视字段添加到列"区域

Is it possible to stack my column headers like this in sql? In excel this would be akin to adding multiple pivot fields into the 'Columns' area

推荐答案

您不能像在 excel 中那样有多个标题，但您可以改为预先连接标题.浏览器

You can't have multiple headers as you would in excel but you can pre-concatenate your headers instead. IE

SELECT key+'-'+subkey as key, value into #temp from table

相关文章