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 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
SQL Server Pivot on multiple fields
You need first to UNPIVOT your table. You can do it using this query:
SELECT Portfolio, [Date], Val, ColType
FROM (SELECT Portfolio,
[Date],
TotalLoans,
ActiveLoans,
TotalBalance
FROM mytable
WHERE Portfolio = 'P1') AS srcUnpivot
UNPIVOT (
Val FOR ColType IN (TotalLoans, ActiveLoans, TotalBalance)) AS unpvt
Output:
Portfolio Date Val ColType
===============================================
P1 2015-12-31 1000 TotalLoans
P1 2015-12-31 900 ActiveLoans
P1 2015-12-31 100000 TotalBalance
P1 2015-11-30 1100 TotalLoans
P1 2015-11-30 800 ActiveLoans
P1 2015-11-30 100100 TotalBalance
P1 2015-10-31 1200 TotalLoans
P1 2015-10-31 700 ActiveLoans
P1 2015-10-31 100200 TotalBalance
Note: All unpivoted fields must be of the same type. The query above assumes a type of int for all fields. If this is not the case then you have to use CAST.
Using the above query you can apply PIVOT:
SELECT Portfolio, ColType, [2015-12-31], [2015-11-30], [2015-10-31]
FROM (
... above query here ...
PIVOT (
MAX(Val) FOR [Date] IN ([2015-12-31], [2015-11-30], [2015-10-31])) AS pvt
Pivot multiple columns over one column in SQL Server
Just perform UNPIVOT, then PIVOT. Here is full working example:
DROP TABLE IF EXISTS #rdata;
CREATE TABLE #rdata
(
[Months] VARCHAR(12)
,[KPI_1] INT
,[KPI_2] INT
,[KPI_3] INT
);
INSERT INTO #rdata ([Months], [KPI_1], [KPI_2], [KPI_3])
VALUES ('Jan-18 ', 1, 2, 3)
,('Feb-18 ', 4, 5, 6)
,('Mar-18 ', 7, 8, 9)
,('Apr-18 ', 10, 11, 12)
,('Aug-18 ', 13, 14, 15)
DECLARE @DynammicTSQLStatement NVARCHAR(MAX)
,@DynamicPIVOTColumns NVARCHAR(MAX);
SET @DynamicPIVOTColumns = STUFF
(
(
SELECT ',[' + CAST([Months] AS VARCHAR(12)) + ']'
FROM #rdata
GROUP BY [Months]
ORDER BY [Months]
FOR XML PATH('') ,TYPE
).value('.', 'NVARCHAR(MAX)')
,1
,1
,''
);
SET @DynammicTSQLStatement = N'
SELECT *
FROM #rdata
UNPIVOT
(
[value] FOR [KPIs] IN ([KPI_1], [KPI_2], [KPI_3])
) UNPVT
PIVOT
(
MAX([value]) FOR [Months] IN (' + @DynamicPIVOTColumns + ')
) PVT'
EXEC sp_executesql @DynammicTSQLStatement;
Note, that when I am creating the dynamic columns based on the month column, I am ordering by the month value itself. If you want to have/sort the months chronologically, you need to store the data in different format (like 2018-01, 2017-10) for sort by other column (like primary key or something like that).
SQL server Pivot on Multiple Columns
I would unpivot the columns into pairs first, then pivot them. Basically the unpivot process will convert the pairs of columns (rscd, position and accd, aposition) into rows, then you can apply the pivot. The code will be:
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;
See SQL Fiddle with Demo
Related Topics
How to Delete in Ms Access When Using Join'S
Joining Multiple Tables in SQL
Store Multiple Bit Values in a Single Table Column
Get Start and End Date from Week Number SQL Server
Postgresql Does Not Accept Column Alias in Where Clause
Optimize Groupwise Maximum Query
Transposing an SQL Result So That One Column Goes Onto Multiple Columns
Postgres Function Returning Table Not Returning Data in Columns
SQL Group By, Top N Items for Each Group
Get the Name of a Row's Source Table When Querying the Parent It Inherits From
Count the Null Columns in a Row in SQL
Sorting String Column Containing Numbers in SQL
How to Map Input and Output Columns Dynamically in Ssis
Using Pivot in SQL Server 2008
In SQL, Is Update Always Faster Than Delete+Insert
SQL Server: Cannot Insert an Explicit Value into a Timestamp Column