SQL Unpivot groups of columns

Question

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

Well you have what is known as repeating groups which violates 1NF. This is why you are struggling here. You can use UNPIVOT to do this. Or you could use a UNION ALL for this too. — Sean Lange, Oct 11 '17 at 18:09

score 1 · Answer 1 · answered Oct 11 '17 at 18:34

As requested, using UNPIVOT notation, the query will be like below

--create table T(RegionID int,   Price1  money,  Price1New   money, Efx1Date  date,  Price2  money,  Price2New money,  Efx2Date date)
--insert into T values 
--(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')

select 
RegionId,
priceList,
[Current],
NewPrice= Case 
            when priceList='Price1'
            then Price1New
            when priceList='Price2'
            then Price2New
          end,
EfxDate= Case 
            when priceList='Price1'
            then Efx1Date    
            when priceList='Price2'
            then Efx2Date    
          end
from
(select * from T)s
unpivot
(
    [Current] for [priceList] in ([Price1],[Price2])
    )up
 order by 1,2

See working demo

You should not order by ordinal position. If the columns change in your query and you don't also change the order by your query will suddenly have a different order. https://sqlblog.org/2009/10/06/bad-habits-to-kick-order-by-ordinal — Sean Lange, Oct 11 '17 at 19:14

score 0 · Accepted Answer · answered Oct 11 '17 at 18:11

0

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

answered Oct 11 '17 at 18:11

dybzon

1,236
2
15
21

SQL Unpivot groups of columns

2 Answers2