SQL Server Pivot：通过动态列分组

小编典典

SQL Server Pivot：通过动态列分组

sql

我在一张table上做了一个透视图，生成了动态列：

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

select @cols = STUFF((SELECT distinct ',' +
                        QUOTENAME(Replace(variable,char(CAST(0x0016 as int)),'')) val
                      FROM TABLEDATA
                      ORDER BY val asc
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)') 
                        , 1, 1, '');

SELECT @query =

'SELECT
  time,
  country,
  disease,
  ' + @cols + '
FROM
  TABLEDATA
PIVOT 
(
  MAX(value) 
  FOR variable IN( ' + @cols + ' )' +
' ) AS p; ';

 execute(@query);

它返回以下结果：

time | country | disease | indicateur1 | indicateur2 | ... | indicateur14 | ... | indicateurn
-----------------------------------------------------------------------------------------
2001 | FRANCE  | MAL     | 0.003       |             |     |              |     |
2002 | FRANCE  | AID     |             | 0.45        |     |              |     |
1997 | ITALY   | HIV     | 0.307       |             |     |              |     |
1999 | ROMANIA | MAL     | 1.044       |             |     |              |     |
2003 | ROMANIA | AID     |             |             |     | 0.77         |     |
2003 | ROMANIA | AID     | 0.872       |             |     |              |     |
2004 | ROMANIA | HIV     |             | 0.921       |     |              |     |
2002 | IRELAND | MAL     |             | 0.008       |     |              |     |

但是我需要按前三列进行分组以获取以下内容：

time | country | disease | indicateur1 | indicateur2 | ... | indicateur14 | ... | indicateurn
-----------------------------------------------------------------------------------------
2001 | FRANCE  | MAL     | 0.003       |             |     |              |     |
2002 | FRANCE  | AID     |             | 0.45        |     |              |     |
1997 | ITALY   | HIV     | 0.307       |             |     |              |     |
1999 | ROMANIA | MAL     | 1.044       |             |     |              |     |
2003 | ROMANIA | AID     | 0.872       |             |     | 0.77         |     |
2004 | ROMANIA | HIV     |             | 0.921       |     |              |     |
2002 | IRELAND | MAL     |             | 0.008       |     |              |     |

所以我需要group by time, country, disease，但是由于我的枢纽专栏是动态的，所以我无法设法找到解决方案…

这是一个SQL小提琴

阅读 287

2021-04-07

共1个答案

小编典典

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

select @cols = STUFF((SELECT distinct ',' +
                        QUOTENAME(Replace(variable,char(CAST(0x0016 as int)),'')) val
                      FROM TABLEDATA
                      ORDER BY val asc
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)') 
                        , 1, 1, '');

SELECT @query =

'SELECT
  time,
  country,
  disease,
  ' + @cols + '
FROM (
    Select
        time,
        country,
        disease,
        variable,
        convert(decimal(10, 2), value) as value
    From
        TABLEDATA
    ) t
PIVOT 
(
  Sum(value)
  FOR variable IN( ' + @cols + ' )' +
' ) AS p; ';


Execute(@query);

[Example Fiddle](http://www.sqlfiddle.com/#!3/81618f/59)

2021-04-07