sql server - wide data to long data-CodePudding

I have a data set that I need to make long. Currently my data looks like this:

Hospital   Zip     Paid$_21    Paid$_22    Visits21    Visits22    LOS21    LOS22
A          12345   5.65        6.50        5           6           2        3 
A          12346   7.22        6.53        3           4           3        1 
A          15902   8.11        9.20        7           8           5        6

But I need it to look like this (with the year matching to the appropriate values)

Hospital   Zip      Year    Paid$    Visits   LOS    
A          12345    2021    5.65     5        2
A          12345    2022    6.50     6        3
A          12346    2021    7.22     3        3
A          12346    2022    6.53     4        1
A          15902    2021    8.11     7        5
A          15902    2022    9.20     8        6

I'm quite unsure how to do this.

Any help is greatly appreciated!

CodePudding user response：

You can unpivot this using CROSS APPLY (VALUES

SELECT
  t.Hospital,
  t.Zip,
  v.Year,
  v.Paid$,
  v.Visits,
  v.LOS
FROM [MyTable] T
CROSS APPLY (VALUES
    (2021, Paid$_21, Visits21, LOS21),
    (2022, Paid$_22, Visits22, LOS22)
) v(Year, Paid$, Visits, LOS)

Note that this only queries the base table once.

db<>fiddle

CodePudding user response：

SELECT Hospital, Zip, '2021' As Year, 
    Paid$_21 As Paid$, Visits21 as Visits, LOS21 as LOS
FROM [MyTable] 

UNION

SELECT Hospital, Zip, '2022' As Year, 
    Paid$_22 As Paid$, Visits22 as Visits, LOS22 as LOS
FROM [MyTable]

You can also try UNPIVOT