在 SQL 中使用'Pivot'将行转换为列

Question

我已经阅读了关于MS数据透视表的内容，但在纠正这些问题时仍然存在问题。

我对这些结果有一个看法

Invoice Date        Basecard    Item      Quantity  Subtotal    Taxes   Total
713938  09/11/2020  C90001      Desktop   14            2800       448      3248
713938  09/11/2020  C90001      Laptop    18.5      29091.25    4654.6  33745.85

我希望它能成为一个数据透视表，就像这样：

Invoice Date        Basecard    Laptop  Desktop   Subtotal  Taxes   Total
713938  09/11/2020  C90001      18.5    14        31891.25  5102.6  36993.85

这是我的查询

SELECT * FROM (
SELECT Invoice, Date, BaseCard, Item, sum(Quantity) Qty, sum(Subtotal) Subtotal
FROM MyView
Group by Invoice, Date, BaseCard, Item, Subtotal

) Resultados
PIVOT (
Sum(Qty)
FOR Item
IN (
[Laptop], [Desktop]         
)
) AS PivorTable
Group by Invoice, Date, BaseCard, Laptop, Desktop, Subtotal

结果

Invoice Date      baseCard  subtotal    Laptop  Desktop
713938  2020-11-09 C90001   2800.00     NULL    14.000000
713938  2020-11-09 C90001   7076.25     4.50    NULL
713938  2020-11-09 C90001   22015.000   14.00   NULL

Answer 1

假设您有这样一个表

CREATE TABLE [InvoiceInfo]( 
[Invoice]  INT, 
[Date]     DATE, 
[Basecard] VARCHAR(100), 
[Item]     VARCHAR(100),
[Quantity] FLOAT,
[Price]    FLOAT
)

从中创建视图。考虑到这一点，我可以建议您使用条件聚合来简化数据透视，并根据当前现有的[Item]值(如(转换为动态格式以使其灵活

DECLARE @cols  AS NVARCHAR(MAX),  @query AS NVARCHAR(MAX)
SET @cols = ( SELECT STRING_AGG(
CONCAT('SUM(CASE WHEN [Item]=''',[Item],
''' THEN [Quantity] END) AS [',[Item],']'),',') 
FROM (SELECT DISTINCT [Item]
FROM [InvoiceInfo] ) i );
SET @query = CONCAT(
N'SELECT [Invoice], [Date], [BaseCard],', @cols , 
N'      ,SUM([Price]*[Quantity]) AS [Subtotal],
SUM(ROUND([Price]*[Quantity]*.16, 2)) AS [Taxes],
SUM([Price]*[Quantity])+SUM(ROUND([Price]*[Quantity]*.16, 2)) AS [Total]
FROM [InvoiceInfo] i
GROUP BY [Invoice], [Date], [BaseCard]');
EXEC sp_executesql @query; 
Invoice Date        BaseCard    Desktop Laptop  Subtotal    Taxes   Total
713938  2020-11-09  C90001      14      18.5    31891.25    5102.6  36993.85

更新：根据您最近对旧DB版本的声明，您可以使用以下代码(包括FOR XML PATH和STUFF(作为STRING_AGG的替代方案

DECLARE @cols AS NVARCHAR(MAX),  @query AS NVARCHAR(MAX);

SELECT @cols = 
STUFF((SELECT DISTINCT ',' + 
CONCAT('SUM(CASE WHEN [Item]=''',[Item],
''' THEN [Quantity] END) AS [',[Item],']') 
AS formulas
FROM 
(
SELECT DISTINCT [Item]
FROM [InvoiceInfo] f
) ff
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)') 
,1,1,'');

SET @query = CONCAT(
N'SELECT [Invoice], [Date], [BaseCard],', @cols , 
N'      ,SUM([Price]*[Quantity]) AS [Subtotal],
SUM(ROUND([Price]*[Quantity]*.16, 2)) AS [Taxes],
SUM([Price]*[Quantity])+SUM(ROUND([Price]*[Quantity]*.16, 2)) AS [Total]
FROM [InvoiceInfo] i
GROUP BY [Invoice], [Date], [BaseCard]');
EXEC sp_executesql @query; 

演示

Answer 2

您只需要总结这些关键数字。您的行被拆分，因为您已将subtotal列添加到按.分组

db<gt；小提琴

with a as (
select 713938 as invoice, convert(date, '2020-11-09', 23) as dt, 'C90001' as Basecard, 'Desktop' as item, 14 as quantity, 2800 as subtotal, 448 as taxes, 3248 as total
union all
select 713938, convert(date, '2020-11-09', 23), 'C90001', 'Laptop', 18.5, 29091.25, 4654.6, 33745.85
)
select
invoice,
dt,
basecard,
sum([Desktop]) as desctop,
sum([Laptop]) as laptop,
sum(subtotal) as subtotal,
sum(taxes) as taxes,
sum(total) as total
from a
pivot (
sum(quantity) for item in ([Desktop], [Laptop])
) as q
group by
invoice,
dt,
basecard
invoice | dt         | basecard | desctop | laptop | subtotal |  taxes |    total
------: | :--------- | :------- | ------: | -----: | -------: | -----: | -------:
713938 | 2020-11-09 | C90001   |    14.0 |   18.5 | 31891.25 | 5102.6 | 36993.85