Three Column SQL Pivot

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 |

SQL - pivot three rows into three columns with static column names?

You have to have something to pivot on to identify which column should get what value, so you could create a row_number and then pivot that data. here is an example with test data.

DECLARE @UserLogins AS TABLE (LoginDateTime DATETIME, UserName VARCHAR(20))

    INSERT INTO @UserLogins (LoginDateTime, UserName)
    VALUES
    (GETDATE(),'John Doe')
    ,(GETDATE() -1,'John Doe')
    ,(GETDATE() -2,'John Doe')
    ,(GETDATE() -3,'John Doe')
    ,(GETDATE() -4,'John Doe')

    ;WITH cteRowNum  AS (
        SELECT TOP (3)
           UserName
           ,LoginDateTime
           ,RowNum = ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY LoginDateTime DESC)
        FROM
           @UserLogins
        WHERE
           UserName = 'John Doe'
        ORDER BY
           LoginDateTime DESC
    )

    SELECT
        UserName
        ,[1] as [1st]
        ,[2] as [2nd]
        ,[3] as [3rd]
    FROM
        cteRowNum
        PIVOT (
           MAX(LoginDateTime)
           FOR RowNum IN ([1],[2],[3])
        ) p

Note the SELECT TOP(3) and order by isn't necessary I just kept it there as a limiter. If there are less than 3 logins the column(s) for that count will be null, more than 3 just don't get displayed.....

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 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

sql server pivot string from one column to three columns

You can get the results you desire by taking max(school) for each pivot value. I'm guessing the pivot value you want is rank over school partitioned by student. This would be the query for that:

select * from
(select *, rank() over (partition by studid order by school) rank from student) r
pivot (max(school) for rank in ([1],[2],[3])) pv

note that max doesn't actually do anything. the query would return the same results if you replaced it with min. just the pivot syntax requires the use of an aggregate function here.

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

---

Related Topics