这是我的查询:
select d.disposition, count(a.serialnumber) AS SUBTOTAL
FROM FADB a, FARepair b, Disposition d
WHERE a.ID = b.ID AND b.DispositionID = d.DispositionID
GROUP BY d.Disposition
这是结果:
Disposition SUBTOTAL
a 45
b 100
c 39
我需要在 sql server 中使用 PIVOT 函数将这些行转换为列,这就是我希望它的样子:
a b c
45 100 39
有人请帮我更改查询语句,非常感谢您的帮助。
使用 PIVOT 函数:
WITH Data AS (
-- this is your actual query
--select d.disposition, count(a.serialnumber) AS SUBTOTAL
--FROM FADB a, FARepair b, Disposition d
--WHERE a.ID = b.ID AND b.DispositionID = d.DispositionID
--GROUP BY d.Disposition
-- this is a sample just to make up the example
SELECT *
FROM (
VALUES ('a', 45), ('b', 100), ('c', 39)
) AS d (Disposition, SUBTOTAL)
)
SELECT *
FROM DATA
PIVOT ( AVG(SUBTOTAL) FOR Disposition IN ([a],[b],[c])) AS pvt
我使用了 AVG 聚合函数。由于每个处置只有一行,因此使用哪个聚合函数并不重要。