CODEWITHSUNDEEP
sqlsql-serverunpivot
Jordan Haymond
Jordan Haymond

Reputation: 87

SQL Server unpivot with multiple columns

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

Upvotes: 2

Views: 138

Answers (2)

Kerry Jackson
Kerry Jackson

Reputation: 1871

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

Upvotes: 3

John Cappelletti
John Cappelletti

Reputation: 82010

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'

Upvotes: 1

Related Questions