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

Barbaros Özhan · Answer 1 · 2020-12-07T23:24:38.470

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

Thanks for your help Barbaros, but i have SQL Server 2015 and have this errors when run the query. -- STRING_AGG is not a recognized built-in function name -- CONCAT is not a recognized built-in function name — Andros, Dec 07 '20 at 22:24
Hi @Andros , you're welcome. I've updated due to your DB's version. — Barbaros Özhan, Dec 07 '20 at 23:06
Im sorry Babaros, my mistake, my SQL version is 2005. I try to made it works but i can't. You are of great help — Andros, Dec 07 '20 at 23:22

astentx · Answer 2 · 2020-12-01T23:59:05.820

0

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

edited Dec 01 '20 at 23:59

answered Dec 01 '20 at 23:49

astentx

6,393
2
16
25

Thanks for your help, this works almost perfectly, how can convert to dynnamic format to make it flexible? because i have more items – Andros Dec 07 '20 at 22:29
@Andros There's no way to make it dynamic in static SQL (I think because parser should know the exact column names before execution). So the only way is to use dynamic SQL, as there is in another answer. And lots of examples: [one](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query), [two](https://stackoverflow.com/questions/19797937/sql-server-dynamic-pivot-in-udf-that-returns-table) and, bad but working, got from the second, [three](https://dba.stackexchange.com/questions/12585/invalid-use-of-a-side-effecting-operator-insert-within-a-function-multiple-i/12586#12586). – astentx Dec 08 '20 at 06:35

Convert Rows to columns using 'Pivot' in SQL

2 Answers2