TSQL pivot with multiple column headers

问题:

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 |

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)

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

回答1:

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