Search code examples
sqlsql-serverunpivot

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
Solution

  • 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