sql data change

Question

Currently my data looks like this:

A          15902   8.11        9.20        7           8           5        6 

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!

Answer 1

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

Answer 2

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