Search code examples
sqlsql-servert-sqlsql-server-2014unpivot

SQL Unpivot groups of columns

Honestly, I'm not even sure if there is a more appropriate function to use to tackle my problem as I'm not familiar with many functions in SQL 2014 besides basic Select, Update, Delete, Insert...

I have this table: 

RegionID    Price1    Price1New    Efx1Date    Price2    Price2New    Efx2Date
   1         3.5        4.0        10/23/17     3.75       4.5        10/20/17
   2         3.25       4.5        10/21/17     4.25       4.0        10/21/17

How can I get the result to be?

RegionID    PriceList    Current    NewPrice    EfxDate
   1        Price1        3.5        4.0        10/23/17
   1        Price2        3.75       4.5        10/20/17
   2        Price1        3.25       4.5        10/21/17
   2        Price2        4.25       4.0        10/21/17
Solution

  • You could use a UNION ALL

    SELECT RegionId, 'Price1' AS PriceList, Price1 AS [Current], Price1New AS NewPrice, Efx1Date AS EfxDate
UNION ALL
SELECT RegionId, 'Price2' AS PriceList, Price2 AS [Current], Price2New AS NewPrice, Efx2Date AS EfxDate