SQL Server unpivot with multiple columns

Question

Trying to get an Unpivot to apply across a few columns and am struggling to get it to work correctly. Maybe its not the right solution so definitely open to suggestions. Here's an example of my dataset

Sample Data Set In Excel

Sample Wanted Data Set In Excel

Sorry for the links, can't quite stick pics in these posts just yet.

Here's the code that I'm close to, it seems like I'm close...but maybe not.

SELECT 
    RunDate,
    ShipMode,
    Amt
FROM
    (SELECT
         CAST(sh.RunDt as DATE) as RunDt,
         sh.method1, sh.method2, sh.method3,
         sh.method4, sh.method5
     FROM 
         [dbo].sampletable sh
     WHERE 
         RunDt = '2016-10-17') AS P
UNPIVOT (
    RunDate FOR ShipMode IN(method1, method2, method3,method4, method5)
) AS Unpvt

Kerry Jackson · Accepted Answer · 2018-10-29 20:31:55Z

3

You just have a couple of names wrong. You can unpivot using the SQL:

SELECT 

RunDt ,ShipMode ,Amt FROM (

 SELECT 
 CAST(sh.RunDt as DATE) as RunDt
,sh.method1 
,sh.method2 
,sh.method3  
,sh.method4 
,sh.method5
FROM [dbo].sampletable  sh
WHERE RunDt = '2018-10-17'

) AS P UNPIVOT ( Amt FOR ShipMode IN(method1, method2, method3,method4, method5) ) as Unpvt

answered Oct 29, 2018 at 20:31

Kerry Jackson

1,87114 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

John Cappelletti · Accepted Answer · 2018-10-29 20:21:09Z

1

Rather than UNPIVOT, another option is using a CROSS APPLY. It offers a bit more flexibility

Example

Select A.RunDt
      ,B.*
 From  YourTable A
 Cross Apply ( values ('Method1',Method1)
                     ,('Method2',Method2)
                     ,('Method3',Method3)
                     ,('Method4',Method4)
                     ,('Method5',Method5)
             ) B(Overall,Amt)
 Where RunDt = '2016-10-17'

answered Oct 29, 2018 at 20:21

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Collectives™ on Stack Overflow

SQL Server unpivot with multiple columns

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related