I have the table below which I am looking to pivot so that the descriptions in column 1 become column headers in the new pivot.
我有下面的表格，我正在寻找，以便第1列中的描述成为新枢轴中的列标题。
Nominal Group | GrpID | Description | Value | CustomerID
---------------+-------+-----------------+-------------+-----------
Balance Sheet | 7 | BS description | 56973.10 | 2
Cost of Sales | 4 | COS description | 55950.17 | 2
Sales | 1 | Sales | -178796.18 | 2
Labour Costs | 5 | Wages | 18596.43 | 2
Overheads | 6 | Rent | 47276.48 | 2
I'm using the code below to get the result set below that:
我正在使用下面的代码获得下面的结果集：
select * from trialbalancegrouping
PIVOT (Sum(value)
for nominalgroupname in ([Sales],[Cost of Sales],[Labour Costs],[Overheads])) AS PVTtable
-
-
GrpID | Description | CustomerID | Sales | Cost of Sales | Labour Costs | Overheads
------+---------------+------------+------------+---------------+--------------+-----------
1 | Sales | 2 | -178796.18 | NULL | NULL | NULL
2 |COS Description| 2 | NULL | 55950.17 | NULL | NULL
3 | Labour | 2 | NULL | NULL | 18596.43 | NULL
4 | Overheads | 2 | NULL | NULL | NULL | 47276.48
Ideally, I'd want the output to be one row per customer, like this:
理想情况下，我希望每个客户的输出为一行，如下所示：
CustomerID | Sales | Cost of Sales | Labour Costs | Overheads
-----------+------------+----------------+--------------+------------
2 | -178796.18 | 55950.17 | 18596.43 | 47276.48
1 个解决方案
#1
11
Any columns that are available are passed to the PIVOT function, so all apart from the column aggregated, and the column pivoted are implicitly grouped by, so since GrpID and Description are present, and not included it is grouped by, therefore you get one row per combination of these. You need to limit the columns passed to the pivot function by using a subquery:
任何可用的列都将传递给PIVOT函数，因此除了聚合的列之外，所有列都是隐式分组的，因此，由于存在GrpID和Description，并且不包括它，因此它会被分组，因此您获得一行每种组合。您需要使用子查询限制传递给pivot函数的列：
SELECT pvt.CustomerID,
pvt.Sales,
pvt.[Cost of Sales],
pvt.[Labour Costs],
pvt.[Overheads]
FROM ( SELECT CustomerID, nominalgroupname, Value
FROM trialbalancegrouping
) AS t
PIVOT
( SUM(Value)
FOR nominalgroupname IN
( [Sales],[Cost of Sales],
[Labour Costs],[Overheads]
)
) AS pvt;
#1
11
Any columns that are available are passed to the PIVOT function, so all apart from the column aggregated, and the column pivoted are implicitly grouped by, so since GrpID and Description are present, and not included it is grouped by, therefore you get one row per combination of these. You need to limit the columns passed to the pivot function by using a subquery:
任何可用的列都将传递给PIVOT函数，因此除了聚合的列之外，所有列都是隐式分组的，因此，由于存在GrpID和Description，并且不包括它，因此它会被分组，因此您获得一行每种组合。您需要使用子查询限制传递给pivot函数的列：
SELECT pvt.CustomerID,
pvt.Sales,
pvt.[Cost of Sales],
pvt.[Labour Costs],
pvt.[Overheads]
FROM ( SELECT CustomerID, nominalgroupname, Value
FROM trialbalancegrouping
) AS t
PIVOT
( SUM(Value)
FOR nominalgroupname IN
( [Sales],[Cost of Sales],
[Labour Costs],[Overheads]
)
) AS pvt;