Pivot and unpivot a table with multiple columns and Rows in SQL

Question

I need to output this below table as following. Below is existing database table output.

UNIQUE_ID   PARTICULARS                   18-Jan    18-Feb    18-Mar
-----       -----                         -----     -----     -----
1           Direct Cost                   3,393     3,776     3,776
1           Quarter                       Q3 FY18   Q3 FY18   Q3 FY18
1           Revenue net Volume Discount   4,409     5,787     5,512
2           Direct Cost                   25,022    39,178    34,143
2           Quarter                       Q2 FY18   Q2 FY18   Q2 FY18
2           Revenue net Volume Discount   28,730    45,507    38,247

I need to convert above table to below output.

UNIQUE_ID   FinancialMonth  Quarter     DirectCost  Revenue net Volume Discount
1           18-Jan          Q3 FY18     3,393       4,409
1           18-Feb          Q3 FY18     3,776       5,787
1           18-Mar          Q3 FY18     3,776       5,512
2           18-Jan          Q2 FY18     25,022      28,730
2           18-Feb          Q2 FY18     39,178      45,507
2           18-Mar          Q2 FY18     34,143      38,247

Could you help me on this to convert it. I have converted FinancialMonth using unpivot, but I could not convert Quarter as Column.

SELECT UNIQUE_ID
       ,PARTICULARS
       ,[FinancialYearMonth] AS 'FinancialMonth'
       ,CASE WHEN PARTICULARS='Direct Cost'   
             THEN [FinancialValues] END AS [DirectCost]
       ,CASE WHEN PARTICULARS='Revenue net Volume Discount'   
             THEN [FinancialValues] END AS [RevenueNetVolumeDiscount]

FROM DBO.Raw_Monthly
UNPIVOT   
  ( 
        FinancialValues
    FOR [FinancialYearMonth] IN(
       Jan18
      ,[Feb18]
      ,[Mar18]


       ) 
   ) AS unpv

In above query Quarter values are missing.

Both FinancialMonth and Quarter might be unpivot and pivot on same time as my understanding. Could you please help on this.

Posting DDL & Sample data in text format is more helpful than image. As image can not be used or copied. Also put what you have tried so far with the issue. — Suraj Kumar, Jan 02 '20 at 17:37
Your base table design looks odd. Do you really have column names which are actual dates (months) or is this data imported from an external resource that you need to normalise. What will happen with your base table going forward - will the column names change to other month/dates? — Peter Smith, Jan 02 '20 at 17:55
@peter This is extracted from Macro excel sheet to SQL table as it is. There are more months are reside in the table and not duplicating , I have put as sample months in here.Months are relate to Quarter as well for more information. I need to get ouput as above from the data set. — lochana, Jan 02 '20 at 18:06

Barbaros Özhan · Accepted Answer · 2020-01-05T13:36:05.253

The tried query is missing pivoting ( Conditional Aggregation, e.g. case..when clauses containing Aggregation through Grouping ) after unpivoting. Therefore, consider :

 SELECT [Unique_ID], [FinancialMonth], 
        MAX(CASE WHEN [Particulars]='Quarter' THEN [FinancialValues] END) AS [Quarter],
        MAX(CASE WHEN [Particulars]='Direct Cost' THEN [FinancialValues] END) AS [DirectCost],
        MAX(CASE WHEN [Particulars]='Revenue net Volume Discount' THEN [FinancialValues] 
        END) AS [Revenue net Volume Discount]
   FROM Raw_Monthly
UNPIVOT  
   (
    [FinancialValues] FOR [FinancialMonth] IN ( [18-Jan] ,[18-Feb] ,[18-Mar] ) 
   ) AS unpvt
  GROUP BY [Unique_ID], [FinancialMonth]
  ORDER BY [Unique_ID], 
           CONVERT(date, REVERSE(SUBSTRING(REVERSE([FinancialMonth]),1,3))+
                  ' 20'+  SUBSTRING(REPLACE([FinancialMonth],'-',''),1,2) , 13)

Demo

Pivot and unpivot a table with multiple columns and Rows in SQL

1 Answers1