locked
Sql Pivot Transform RRS feed

  • Question

  • User1173249824 posted

    Hi I need a help on how to go about this

    I am using two tables DailyCalibration and Tank

    This my query here

     SELECT Distinct DailyCalibration.Site_code, Tank.Item, DailyCalibration.period, Max(DailyCalibration.date_dt) AS LastUpdated, Sum(DailyCalibration.quantity) AS QTY
    FROM         DailyCalibration INNER JOIN
    
                          Tank ON DailyCalibration.tank = Tank.Tank
    
    Group BY DailyCalibration.Site_code, Tank.Item, DailyCalibration.period

    This is My result

    +------ -+----------+---------+-------------+---------------+ 
    Site_code| Item     | Period  | LastUpdated |    QTY
    +--------+----------+-------+-------------+---------------+ 
      100   |  AGO     | EVENING |	2013-11-20  | 814355.793
    +--------+----------+-------+-------------+---------------+ 
      100   |  AGO     | MORNING |	2013-11-07  | 1104501.012
    +----------+--------+-------+-------------+---------------+  
      100   |  PMS     | EVENING |	2013-11-21  | 18000.000
    +----------+--------+-------+-------------+---------------+ 
      100   |  PMS     | MORNING |	2013-11-07  | 200000.055
    +----------+--------+-------+-------------+---------------+            
      101   |  AGO     | EVENING |	2013-11-21  | 1067478.693
    +----------+--------+-------+-------------+---------------+ 
      101   |  AGO     | MORNING |	2013-11-07  | 1437515.483
    +----------+--------+-------+-------------+---------------+ 
      101   |  PMS     | EVENING |	2013-11-21  | 28000.000
    +----------+--------+-------+-------------+---------------+ 
      101   |  PMS     | MORNING |	2013-11-07  | 18000.505

    But I want to transform it this way

    +------ -+-------------+---------+--------------+------------+
    Site_code| LastUpdated | Period  |    AGO       |    PMS    
    +------ -+-------------+---------+--------------+------------+
    100	 |  2013-11-20 | MORNING | 1104501.012  | 200000.055
    +------ -+-------------+---------+--------------+------------+
    100	 |  2013-11-07 | EVENING |   814355.793 |  18000.000
    +------ -+-------------+---------+--------------+------------+
    101	 |  2013-11-20 | MORNING |  1437515.483 |  18000.505
    +------ -+-------------+---------+--------------+------------+
    101	 |  2013-11-07 | EVENING |  1067478.693 |  28000.000
    +------ -+-------------+---------+--------------+------------+

    Pls Can Someone help Me out

    Thanks All.

    Tuesday, September 23, 2014 8:28 AM

Answers

  • User61956409 posted

    Hi Alamdreal,

    Thanks for your post.

    As for you problem, you could refer to the following sample.

    SELECT Distinct DailyCalibration.Site_code, Tank.Item, DailyCalibration.period, Max(DailyCalibration.date_dt) AS LastUpdated, Sum(DailyCalibration.quantity) AS QTY into #rstable FROM  DailyCalibration INNER JOIN Tank ON DailyCalibration.tank = Tank.Tank
    Group BY DailyCalibration.Site_code, Tank.Item, DailyCalibration.period
    
    SELECT Site_code, LastUpdated, Period, QTY as AGO INTO #T1 FROM #rstable WHERE Item='AGO' 
    
    SELECT Site_code, LastUpdated, Period, QTY as PMS INTO #T2 FROM #rstable WHERE Item='PMS' ORDER BY Site_code
    
    SELECT #T1.Site_code, #T1.LastUpdated, #T1.Period, AGO, PMS FROM #T1
    left join #T2
    on #T1.Site_code=#T2.Site_code and #T1.Period=#T2.Period and #T1.LastUpdated=#T2.LastUpdated
    

    In my sample, I used Temporary table (#rstable ,#T1, #T2). For more information about Temporary Table, you could check the following link.

    Hope it will be helpful to you.

    Best Regards,

    Fei Han

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 23, 2014 10:29 PM