行在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.00using 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.00Now 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 pvtLiveDemo2
输出:
╔═════════════════╦═════════╦══════════╦═══════╗ ║ 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 pvtLiveDemo2
Output:
╔═════════════════╦═════════╦══════════╦═══════╗ ║ AccountType ║ January ║ February ║ March ║ ╠═════════════════╬═════════╬══════════╬═══════╣ ║ Commission ║ 4400 ║ 2200 ║ ║ ║ Medical ║ 1635.82 ║ 1636 ║ ║ ║ Staff Allowance ║ 1215 ║ ║ 1750 ║ ║ Visa ║ 3320 ║ ║ ║ ╚═════════════════╩═════════╩══════════╩═══════╝更多推荐
发布评论