mysql - 动态枢轴求和和分组依据

Question

我试图对两列进行动态透视以重新排列一些数据。她是我原来的 table :

Initial table

我使用以下代码根据 CountryID 和 GenericArticle 来旋转BRAND，但我得到以下结果(如您所见，表似乎没有按分组)

Result after pivot

这是我的枢轴代码:

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
 

SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME(Brand)
FROM (SELECT DISTINCT [Brand]
	  FROM #tempProgCov) AS Country


SET @DynamicPivotQuery = 
  N'SELECT [CountryID], [GenericArticle], ' + @ColumnName + '
INTO ##ProgCovPivot
FROM #tempProgCov as Src
PIVOT
 (
	SUM([Coverage])
	FOR [Brand] IN (' + @ColumnName + ')
 ) as Pvt'

EXEC sp_executesql @DynamicPivotQuery

非常感谢任何帮助，谢谢!

PS，期待这样的事情:

Desired result

Answer 1

没关系，我最终解决了它，就在我眼皮底下。问题出在源表选择中:

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
 

SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME(Brand)
FROM (SELECT DISTINCT [Brand]
	  FROM #tempProgCov) AS Country



SET @DynamicPivotQuery = 
  N'SELECT [CountryID], [GenericArticle], ' + @ColumnName + '
INTO ##ProgCovPivot
FROM (SELECT CountryID,
			 Brand,
			 GenericArticle,
			 Coverage
	  FROM #tempProgCov) as Src
PIVOT
 (
	SUM([Coverage])
	FOR [Brand] IN (' + @ColumnName + ')
 ) as Pvt'


EXEC sp_executesql @DynamicPivotQuery