如何对动态透视表进行分组

Question

我有一个存储过程，可以按年、月份、每个和结果进行动态表分组，但是分组不是很好，而且我也找不到需要更改的地方。

我已经尝试在c#实体框架中复制代码，但是有相同的结果不起作用

DECLARE @fch1 NVARCHAR(10) = '2019-01-01';
DECLARE @fch2 NVARCHAR(10) = '2019-05-31';

DECLARE @query NVARCHAR(MAX);

Declare  @colc nvarchar(MAX) = STUFF((SELECT DISTINCT','+QUOTENAME(concat(year(e.FECHADOCTO),month(e.FECHADOCTO),'C'))
                         FROM EDocumentos e WHERE e.FECHADOCTO between @fch1 and @fch2
                         FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '');
Declare  @colv nvarchar(MAX) = STUFF((SELECT DISTINCT','+QUOTENAME(concat(year(e.FECHADOCTO),month(e.FECHADOCTO),'V'))
                         FROM EDocumentos e WHERE e.FECHADOCTO between @fch1 and @fch2
                         FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '');
Declare  @colf nvarchar(MAX) = STUFF((SELECT DISTINCT','+QUOTENAME(concat(year(e.FECHADOCTO),month(e.FECHADOCTO),'F'))
                         FROM EDocumentos e WHERE e.FECHADOCTO between @fch1 and @fch2
                         FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '');
    print @colc;

SET @query = 'SELECT MARCA, '+@colc+','+@colv+','+@colf+' from (
                    SELECT  p.MARCA,
                            concat(year(e.FECHADOCTO),month(e.FECHADOCTO),''C'') as [colc],
                            concat(year(e.FECHADOCTO),month(e.FECHADOCTO),''V'') as [colv],
                            concat(year(e.FECHADOCTO),month(e.FECHADOCTO),''F'') as [colf],
                            sum(d.CANTIDAD) as cant, 
                            sum(d.TOTAL-d.TDESCU) as venta,
                            (sum(d.TOTAL-d.TDESCU)/sum(d.COSTO*d.CANTIDAD)) as factor
                    from    EDocumentos e left join DDocumentos d on e.CVE_DOCTO=d.CVE_DOCTO
                            left join Productos p on d.CVE_PRODUCTO=p.CVE_PRODUCTO
                            left join Vendedores v on e.CVE_VEND=v.CVE_VEND
                    where   e.FECHADOCTO between '''+@fch1+''' and '''+@fch2+'''
                            and v.CLASIFICACION in (''CTRAL'',''DEPTO'',''COMPA'', ''OFICI'', ''BAZAR'', ''INSTA'')
                            and e.ESTADO <> ''C'' and e.TIPODOCTO = ''F'' 
                    group   by  p.MARCA, year(e.FECHADOCTO), month(e.FECHADOCTO) 
                    )x
                    pivot (sum(cant) for [colc] in ('+@colc+')) as pc
                    pivot (sum(venta) for [colv] in ('+@colv+')) as pv
                    pivot (avg(factor) for [colf] in ('+@colf+')) as pf';
print @query;
EXECUTE (@query);

这是实际结果实际

这是预期的结果期望的

Answer 1

如果没有动态部分，请检查这个示例，这样您就可以看到查询应该是什么样的：

SQL演示

• 在cte中，计算SUM和AVG
• 在base中，您将在执行枢轴的标记上创建标签。
  • 您也可以使用UNPIVOT而不是UNION ALL，但我认为这种情况没有必要。

​

• 然后，您可以做一个常规的枢轴(在这里，您可以包括动态部分，稍后)

查询：

with cte as (
  SELECT [type], [years], MAX([value]) as maxv, AVG([value]) as avgv
  FROM Table1
  GROUP BY [type], [years]
), 
base as (
  SELECT [type], cast([years] as nvarchar) + '_maxv' as calc, maxv as value
  FROM cte
  UNION ALL
  SELECT [type], cast([years] as nvarchar) + '_avgv' as calc, avgv as value
  FROM cte
)

SELECT [type], [2017_maxv], [2017_avgv], [2018_maxv], [2018_avgv] 
FROM ( SELECT [type], [calc], [value]
       FROM base ) p
PIVOT ( 
        MAX(value) FOR
        calc IN ([2017_maxv], [2017_avgv], [2018_maxv], [2018_avgv])
       ) as pvt

输出

| type | 2017_maxv | 2017_avgv | 2018_maxv | 2018_avgv |
|------|-----------|-----------|-----------|-----------|
|    A |        10 |        10 |        20 |        20 |
|    B |        30 |        30 |        40 |        40 |