sql服务器枢轴：按动态列分组 [英] sql server pivot : group by with dynamic columns

问题描述

我在表上做了一个数据透视表，生成动态列：

I have done a pivot on a table, generating dynamic columns :

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);

它会返回以下结果：

It returns the following result :

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       |     |              |     | 

但是我需要按前3列进行分组以获得以下结果：

But I need to group by the first 3 columns to get the following :

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       |     |              |     |

所以我需要按时间，国家，疾病，但由于我的数据透视列是动态的，我无法找到解决方案......

So I need to group by time, country, disease, but as my columns from the pivot are dynamics, I can't manage to find a solution...

这是一个 SQL小提琴

推荐答案

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);

示例小提琴

这篇关于sql服务器枢轴：按动态列分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！