Tsql Pivot Multiple Columns

TSQL PIVOT MULTIPLE COLUMNS

Since you want to pivot multiple columns of data, I would first suggest unpivoting the result, score and grade columns so you don't have multiple columns but you will have multiple rows.

Depending on your version of SQL Server you can use the UNPIVOT function or CROSS APPLY. The syntax to unpivot the data will be similar to:

select ratio, col, value
from GRAND_TOTALS
cross apply
(
  select 'result', cast(result as varchar(10)) union all
  select 'score', cast(score as varchar(10)) union all
  select 'grade', grade
) c(col, value)

See SQL Fiddle with Demo. Once the data has been unpivoted, then you can apply the PIVOT function:

select ratio = col,
  [current ratio], [gearing ratio], [performance ratio], total
from
(
  select ratio, col, value
  from GRAND_TOTALS
  cross apply
  (
    select 'result', cast(result as varchar(10)) union all
    select 'score', cast(score as varchar(10)) union all
    select 'grade', grade
  ) c(col, value)
) d
pivot
(
  max(value)
  for ratio in ([current ratio], [gearing ratio], [performance ratio], total)
) piv;

See SQL Fiddle with Demo. This will give you the result:

|  RATIO | CURRENT RATIO | GEARING RATIO | PERFORMANCE RATIO |     TOTAL |
|--------|---------------|---------------|-------------------|-----------|
|  grade |          Good |          Good |      Satisfactory |      Good |
| result |       1.29400 |       0.33840 |           0.04270 |    (null) |
|  score |      60.00000 |      70.00000 |          50.00000 | 180.00000 |

How to PIVOT multiple columns using SQL Server

You just need to pivot twice and combine the results, e.g.:

-- Setup example data...
drop table if exists #Example;
create table #Example (
  VendorId int,
  Category varchar(10),
  FirstSaleDate date,
  StoreId int
);

insert #Example (VendorId, [Category], FirstSaleDate, StoreId)
values
  (1, 'Car', '2021-01-01', 12),
  (1, 'Clothes', '2021-01-02', 13),
  (1, 'Toys', '2021-01-03', 14),
  (1, 'Food', '2021-01-04', 15),
  (1, 'Others', '2021-01-05', 15);

-- Pivot data...
with FirstSales as (
  select VendorId, Category, FirstSaleDate from #Example
), Stores as (
  select VendorId, 'StoreId_' + Category as Category, StoreId from #Example
)
select
  FirstSales.VendorId,
  Car, StoreId_Car,
  Clothes, StoreId_Clothes,
  Toys, StoreId_Toys,
  Food, StoreId_Food,
  Others, StoreId_Others
from (
  select VendorId, Car, Clothes, Toys, Food, Others
  from FirstSales
  pivot (min(FirstSaleDate) for Category in ([Car], [Clothes], [Toys], [Food], [Others])) as pvt
) as FirstSales
join (
  select VendorId, StoreId_Car, StoreId_Clothes, StoreId_Toys, StoreId_Food, StoreId_Others
  from Stores
  pivot (min(StoreId) for Category in ([StoreId_Car], [StoreId_Clothes], [StoreId_Toys], [StoreId_Food], [StoreId_Others])) as pvt
) as Stores on Stores.VendorId=FirstSales.VendorId;

SQL Pivot multiple columns without forcing aggregate

The problem was that I needed a row_number per every group of column names. So the below worked

SELECT DISTINCT TrnYear,
                TrnMonth,
                EntryDate,
                TrnTime,
                StockCode,
                Warehouse
FROM
  (SELECT  (ROW_NUMBER() OVER (ORDER BY dw_view_change_event_nr) - 1) / 6 + 1 AS rn,
          COLUMN_NAME ,
         column_value
   FROM
     #TheTable AS tmp) AS src PIVOT (MAX(column_value)
                                                          FOR COLUMN_NAME in ([TrnYear], [TrnMonth], [EntryDate], [TrnTime], [StockCode], [Warehouse])) AS piv

Pivot/transpose rows into columns efficiently with multiple columns

SELECT
  [Num1], 
  [Type1], 
  [Code], 
  [Group], 
  [DA], 
  [123],
  [234]
FROM
  yourTable
PIVOT
(
  MAX([value])  
  FOR [account] IN ([123], [234])
)
  AS PivotTable

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=7fbe16b9254aa5ee60a23e43eec9597f

Pivot on multiple columns (T-SQL)

First you need to unpivot the data then you can pivot the result

Sample data

  create table piv(TimeRange varchar(50),Type varchar(50), Month int,ActionDuration int, GrossValue bigint, NetValue bigint)

  insert piv values
  ('09:00-10:00','Bonus'        ,1 ,30 ,0       ,0       ), 
  ('09:00-10:00','Bonus'        ,1 ,30 ,0       ,0       ),
  ('09:00-10:00','Billed'       ,1 ,30 ,77982   ,701838  ),
  ('09:00-10:00','Not Billed'   ,1 ,30 ,506124  ,4555116 ),
  ('10:00-11:00','Bonus'        ,1 ,30 ,0       ,0       ),
  ('10:00-11:00','Billed'       ,1 ,30 ,109739  ,987651  ),
  ('10:00-11:00','Billed'       ,1 ,30 ,109739  ,987651  ),
  ('10:00-11:00','Not Billed'   ,1 ,30 ,98021   ,882189  ),
  ('09:00-10:00','Bonus'        ,2 ,30 ,0       ,0       ),
  ('09:00-10:00','Billed'       ,2 ,30 ,288947  ,2600523 ),
  ('09:00-10:00','Billed'       ,2 ,30 ,288947  ,2600523 ),
  ('09:00-10:00','Not Billed'   ,2 ,30 ,64669   ,582021  ),
  ('10:00-11:00','Bonus'        ,2 ,30 ,0       ,0       ),
  ('10:00-11:00','Billed'       ,2 ,30 ,48738   ,438642  ),
  ('10:00-11:00','Not Billed'   ,2 ,30 ,269969  ,2429721 )

Query

SELECT *
FROM   (SELECT TimeRange,
               TYPE,
               DATA,
               left(DateName( month , DateAdd( month , month , 0 ) - 1 ),3) + ' '
               + COLUMN_NAME AS PIV_COL
        FROM   Yourtable
               CROSS APPLY (VALUES ('ActionDuration',ActionDuration),
                                   ('GrossValue',GrossValue),
                                   ('NetValue',NetValue)) CS(COLUMN_NAME, DATA)) a
       PIVOT (sum(DATA)
             FOR PIV_COL IN([Jan ActionDuration],
                            [Jan GrossValue],
                            [Jan NetValue],
                            [Feb ActionDuration],
                            [Feb GrossValue],
                            [Feb NetValue])) PV 

Result

TimeRange   TYPE        Jan ActionDuration  Jan GrossValue  Jan NetValue    Feb ActionDuration  Feb GrossValue  Feb NetValue
----------- ----------- ------------------  --------------  ------------    ------------------  --------------  -------------
09:00-10:00 Billed      30                  77982           701838          60                  577894              5201046
10:00-11:00 Billed      60                  219478          1975302         30                  48738               438642
09:00-10:00 Bonus       60                  0               0               30                  0                   0
10:00-11:00 Bonus       30                  0               0               30                  0                   0
09:00-10:00 Not Billed  30                  506124          4555116         30                  64669               582021
10:00-11:00 Not Billed  30                  98021           882189          30                  269969              2429721

Pivoting on multiple columns with multiple sums

We can try handling this by taking a union approach:

WITH cte AS (
    SELECT [Type], Total1 AS Total, Account1 AS Account FROM #temp
    UNION ALL
    SELECT [Type], Total2, Account2 FROM #Temp
)

SELECT
    [Type],
    SUM(CASE WHEN Account = '1220' THEN Total ELSE 0 END) AS [1220],
    SUM(CASE WHEN Account = '4110' THEN Total ELSE 0 END) AS [4110]
FROM cte
GROUP BY
    Type;

Demo

The basic strategy here is to bring all accounts and totals into two separate columns, and then aggregate/pivot just over those single columns. This gets around the problem of having the data you want to aggregate across multiple columns. The best long term fix might be to change your data structure to just have a single column for the account and total.

---

Related Topics