Convert Rows to columns using 'Pivot' in SQL

Question

I have read the stuff on MS pivot tables and I am still having problems getting this correct.

I have a view with these results

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

I would like it to come out as a pivot table, like this:

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

This is my query

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

The result

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

Suppose you have such a table

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

from which you created view. Considering this, I can recommend you use conditional aggregation to make your pivot easier, and convert to a dynamic format to make it flexible by depending the currently existing [Item] values such as

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

Update : Depending on your lately declaration for the DB version being old, you can use the following code including FOR XML PATH and STUFF as an alternative to 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; 

Demo

Answer 2

You just need to sum up that key figures also. Your rows are split because you've added subtotal column to group by.

db<>fiddle

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