Sql Server Pivot on 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;

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.

SQL Server 2008, Pivot based on multiple columns

I don't think you can easily pivot on multiple columns using built-in functionality, but you can use the ROW_NUMBER() window function together with conditional aggregation to achieve the desired effect.

Something like:

WITH CTE_Data AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY Model, Size ORDER BY SKU) AS RowNumber
    FROM #Data
)
SELECT
    CD.Model, CD.Size
    , SKU       = MAX(CASE WHEN CD.RowNumber = 1 THEN CD.SKU   END)
    , Color     = MAX(CASE WHEN CD.RowNumber = 1 THEN CD.Color END)
    , AltSKU1   = MAX(CASE WHEN CD.RowNumber = 2 THEN CD.SKU   END)
    , AltColor1 = MAX(CASE WHEN CD.RowNumber = 2 THEN CD.Color END)
    , AltSKU2   = MAX(CASE WHEN CD.RowNumber = 3 THEN CD.SKU   END)
    , AltColor2 = MAX(CASE WHEN CD.RowNumber = 3 THEN CD.Color END)
FROM CTE_Data CD
GROUP BY Model, Size
ORDER BY Model, Size

Yielding the following result:

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

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

Select *
 From  (
        Select A.Super_Location
              ,B.*
         From  TestTable A
         Cross Apply ( values ( concat(location,'_Min_',year),[Min] ) 
                             ,( concat(location,'_Division_',year),[Value] ) 
                     ) B(Item,Value)
       ) src
 Pivot (max(Value) for Item in ( [Primary_Min_2020 ],[Primary_Min_2019],[Secondary_Min_2020], [Secondary_Min_2019],[Primary_Division_2020],[Secondary_Division_2020], [Secondary_Division_2019]  ) ) pvt

select id, [1], [2], [11], [12]
from
(
  select id, col, value
  from #t
  cross apply
  (
    select rscd, position union all
    select Accd, position + 10
  ) c (value, col)
) d
pivot
(
  max(value)
  for col in ([1], [2], [11], [12])
) piv;