Pivot table in SQL Server 2016

Question

I am having problem with pivoting a table in SQL Server 2016. Consider the following data/query

WITH DATA1 AS
(
   SELECT 123 cid, 'test' cname, '2020-03-17' dt,   'BELLE' fc,     3782703 mn union all
  SELECT 123 cid, 'test' cname, '2020-03-12' dt,    'WOO' fc,       25679 mn union all

  SELECT 345 cid, 'test2' cname, '2019-03-17' dt,   'king' fc,      3782703 mn union all
  SELECT 345 cid, 'test2' cname, '2019-03-12' dt,   'east' fc,      25679 mn union all

  SELECT 111 cid, 'test3' cname, '2019-02-12' dt,   'east' fc,      2 mn
)
select *
 from DATA1
 PIVOT (
     MAX(mn)  FOR fc IN (
      [BELLE]   
      ,[WOO] 
      ,[KING]
      ,[EAST]  
       ) 
) a

I'm trying to pivot on dt and mn for the values in fc. So basically I am looking for this output.

cid       cname    fc1_dt        fc1_name   fc1_mn    fc2_name   fc2_dt        fc2_mn   
123       test      2020-03-17   BELLE      3782703     woo        2020-03-12    25679
345       test2    2019-03-17    king       37          east       2019-03-12    25
111       test3     2019-02-12   east        2

I was trying to add to max function like this but it doesn't work

 PIVOT (
     MAX(mn),max(dt)  FOR fc IN (
      [BELLE]   
      ,[WOO] 
      ,[KING]
      ,[EAST]  
       ) 
) a

Can someone please help modify my query to produce the output above?

Answer 1

One option uses row_number() and conditional aggregation. Assuming that you want the latest date in the first column:

select cid, cname,
    max(case when rn = 1 then dt end) fc1_dt,
    max(case when rn = 1 then fc end) fc1_name,
    max(case when rn = 1 then mn end) fc1_mn,
    max(case when rn = 2 then dt end) fc2_dt,
    max(case when rn = 2 then fc end) fc2_name,
    max(case when rn = 2 then mn end) fc2_mn
from (
    select d.*, row_number() over(partition by cid, cname order by dt desc) rn
    from data1 d
) d
group by cid, cname

If you want to handle more than 2 rows per group, then you need to expand the select clause with more conditional expressions.

Demo on DB Fiddle:

cid | cname | fc1_dt     | fc1_name |  fc1_mn | fc2_dt     | fc2_name | fc2_mn
--: | :---- | :--------- | :------- | ------: | :--------- | :------- | -----:
123 | test  | 2020-03-17 | BELLE    | 3782703 | 2020-03-12 | WOO      |  25679
345 | test2 | 2019-03-17 | king     | 3782703 | 2019-03-12 | east     |  25679
111 | test3 | 2019-02-12 | east     |       2 | null       | null     |   null