Unpivot and pivot with in SQL Server table-CodePudding

I'm looking for some guidance (new to SQL Server in my college work) with unpivot and pivoting data in the following format

Source table:

JobNumber   SourceRowInsertDate Column3 Column5 Column6 Column7 Column8 Column9 Column10    Column11    Column12    Column13    Column14
1832801 9/21/2021   MonthYear   21-Mar  21-Apr  21-May  21-Jun  21-Jul  21-Aug  21-Sep  21-Oct  21-Nov  21-Dec
1832801 9/21/2021   CONDUIT 10  25  40  55  70  85  100 115 130 145
1832801 9/21/2021   WIRE    20  35  50  65  80  95  110 125 140 155
1832801 9/21/2021   SW GEAR 35,000  68  -34,864 -69,796 -104,728    -139,660    -174,592    -209,524    -244,456    -279,388
1832801 9/21/2021   GENERATOR   30  254 478 702 926 1150    1374    1598    1822    2046

Output:

JobNumber   SourceRowInsertDate MonthYear   CONDUIT WIRE    SW GEAR GENERATOR
1832801 9/21/2021   21-Mar  10  20  35,000  30
1832801 9/21/2021   21-Apr  25  35  68  254
1832801 9/21/2021   21-May  40  50  -34,864 478
1832801 9/21/2021   21-Jun  55  65  -69,796 702
1832801 9/21/2021   21-Jul  70  80  -104,728    926
1832801 9/21/2021   21-Aug  85  95  -139,660    1150
1832801 9/21/2021   21-Sep  100 110 -174,592    1374
1832801 9/21/2021   21-Oct  115 125 -209,524    1598
1832801 9/21/2021   21-Nov  130 140 -244,456    1822
1832801 9/21/2021   21-Dec  145 155 -279,388    2046

I tried the following SQL , but it is not giving the desired output,

;WITH CTE_Material AS (
 SELECT 
  JobNumber
     ,RowInsertDate AS SourceRowInsertDate
     ,Column3   ,Column5    ,Column6    ,Column7    ,Column8    ,Column9    ,Column10   ,Column11   ,Column12   ,Column13   ,Column14   
FROM [dbo].RawInput

)
SELECT * FROM CTE_MATERIAL
 UNPIVOT
 (
    MonthYear
    FOR values_ IN (  Column3   ,Column5    ,Column6    ,Column7    ,Column8    ,Column9    ,Column10   ,Column11   ,Column12   ,Column13   ,Column14   )
 ) AS DataUnpivot

Appreciate the help!

DDL:

create table  dbo.RawInput
(
JobNumber varchar(255) null,    
SourceRowInsertDate varchar(255) null,  
Column3 varchar(255) null,  
Column5   varchar(255) null,    
Column6   varchar(255) null,    
Column7   varchar(255) null,    
Column8   varchar(255) null,    
Column9   varchar(255) null,    
Column10  varchar(255) null,    
Column11  varchar(255) null,    
Column12  varchar(255) null,    
Column13  varchar(255) null,    
Column14  varchar(255) null 

)


insert into dbo.RawInput (JobNumber,    SourceRowInsertDate ,Column3    ,Column5    ,Column6    ,Column7    ,Column8    ,Column9    ,Column10   ,Column11   ,Column12   ,Column13   ,Column14)
values
('1832801','9/1/2021','MonthYear','21-Mar','21-Apr','21-May','21-Jun','21-Jul','21-Aug','21-Sep','21-Oct','21-Nov','21-Dec')
,('1832801','9/1/2021','CONDUIT','10','35','40','55','70','44429','100','115','130','145')
,('1832801','9/1/2021','WIRE','20','68','50','65','80','85','110','125','140','155')
,('1832801','9/1/2021','SW GEAR','35000','254','-34864','-69796','-104728','95','-174592','-209524','-244456','-279388')
,('1832801','9/1/2021','GENERATOR','30','','478','702','926','-139660','1374','1598','1822','2046')

CodePudding user response：

As I mentioned in the comments, what you need here is both an unpivot and a pivot. Rather than using the restrictive UNPIVOT and PIVOT operators I use a VALUES table construct and conditional aggregation:

SELECT RI.JobNumber,
       RI.SourceRowInsertDate,
       MAX(CASE RI.Column3 WHEN 'MonthYear' THEN V.Val END) AS MonthYear,
       MAX(CASE RI.Column3 WHEN 'CONDUIT' THEN V.Val END) AS Conduit,
       MAX(CASE RI.Column3 WHEN 'WIRE' THEN V.Val END) AS Wire,
       MAX(CASE RI.Column3 WHEN 'SW GEAR' THEN V.Val END) AS SW_Gear,
       MAX(CASE RI.Column3 WHEN 'GENERATOR' THEN V.Val END) AS Generator
FROM dbo.RawInput RI
     CROSS APPLY (VALUES(5,Column5),(6,Column6),(6,Column7),(8,Column8),(9,Column9),(10,Column10),(11,Column11),(12,Column12),(13,Column13),(14,Column14))V(Col,Val)
GROUP BY RI.JobNumber,
         RI.SourceRowInsertDate,
         V.Col
ORDER BY RI.JobNumber,
         RI.SourceRowInsertDate,
         V.Col;