行在Pivot列中重复(Rows are duplicating in Pivot column)

行在Pivot列中重复(Rows are duplicating in Pivot column) AccountTitle ReportMonth Amount -------------------------------------------- Visa January 3320.00 Medical January 1635.82 Commission January 2200.00 Staff Allowance January 1215.00 Commission January 2200.00 Medical February 1636.00 Commission February 2200.00 Staff Allowance March 1750.00

使用此查询

select AccountTitle, [January], [February], [March] [dbo].MyReport pivot ( sum(amount) for [ReportMonth] in (January, February, March) ) as P accounttile January February March ----------------------------------------------------------- Visa 3320 null null Medical 1635 null null Commission 4400 null null Staff Allowance 1215 null null Medical null 1636.00 null Commission null 2200.00 null Staff Allowance null null 1750.00

现在你可以看到医疗，佣金，员工津贴都是重复的。 二月来不合适

期望的结果是

accounttile January February March ----------------------------------------------------------- Visa 3320 null null Medical 1635 1636.00 null Commission 4400 2200.00 null Staff Allowance 1215 null 1750.00 AccountTitle ReportMonth Amount -------------------------------------------- Visa January 3320.00 Medical January 1635.82 Commission January 2200.00 Staff Allowance January 1215.00 Commission January 2200.00 Medical February 1636.00 Commission February 2200.00 Staff Allowance March 1750.00

using this query

select AccountTitle, [January], [February], [March] [dbo].MyReport pivot ( sum(amount) for [ReportMonth] in (January, February, March) ) as P accounttile January February March ----------------------------------------------------------- Visa 3320 null null Medical 1635 null null Commission 4400 null null Staff Allowance 1215 null null Medical null 1636.00 null Commission null 2200.00 null Staff Allowance null null 1750.00

Now you can see medical, commission, staff allowance are duplicating. for February is not coming properly

desired result is

accounttile January February March ----------------------------------------------------------- Visa 3320 null null Medical 1635 1636.00 null Commission 4400 2200.00 null Staff Allowance 1215 null 1750.00

最满意答案

您可能在示例中显示了更多列

LiveDemo1

使用子查询仅获取AccountTitle, ReportMonth, amount ：

SELECT AccountTitle, [January], [February], [March] FROM (SELECT AccountTitle, ReportMonth, amount FROM MyReport) AS s PIVOT ( SUM(amount) for [ReportMonth] in (January, February, March) ) AS pvt

LiveDemo2

输出：

╔═════════════════╦═════════╦══════════╦═══════╗ ║ AccountType ║ January ║ February ║ March ║ ╠═════════════════╬═════════╬══════════╬═══════╣ ║ Commission ║ 4400 ║ 2200 ║ ║ ║ Medical ║ 1635.82 ║ 1636 ║ ║ ║ Staff Allowance ║ 1215 ║ ║ 1750 ║ ║ Visa ║ 3320 ║ ║ ║ ╚═════════════════╩═════════╩══════════╩═══════╝

You probably have more columns that you've shown in your example

LiveDemo1

Use subquery to get only AccountTitle, ReportMonth, amount:

SELECT AccountTitle, [January], [February], [March] FROM (SELECT AccountTitle, ReportMonth, amount FROM MyReport) AS s PIVOT ( SUM(amount) for [ReportMonth] in (January, February, March) ) AS pvt

LiveDemo2

Output:

╔═════════════════╦═════════╦══════════╦═══════╗ ║ AccountType ║ January ║ February ║ March ║ ╠═════════════════╬═════════╬══════════╬═══════╣ ║ Commission ║ 4400 ║ 2200 ║ ║ ║ Medical ║ 1635.82 ║ 1636 ║ ║ ║ Staff Allowance ║ 1215 ║ ║ 1750 ║ ║ Visa ║ 3320 ║ ║ ║ ╚═════════════════╩═════════╩══════════╩═══════╝