SQLTeam.com | Weblogs | Forums

Trying to unpivot/pivot columns in temp table


#1

Hi,

Having searched online i have been unable to follow the examples regarding pivot/unpivot to do the following:

I have created a temp table with the following format:

Id Weekday Date TrustA TrustB TrustC Type

63 Mon 2016-06-20 30 82 112 Admissions

64 Tue 2016-06-21 36 61 97 Admissions

65 Wed 2016-06-22 34 107 141 Admissions

73 Mon 2016-06-20 34 96 130 Admissions

74 Tue 2016-06-21 40 104 144 Admissions

75 Wed 2016-06-22 44 101 145 Admissions

83 Mon 2016-06-20 23 42 65 Admissions

And i want to unpivot (?) so that it is as follows:

Id Weekday Date Site Admissions

63 Mon 20/06/2016 TrustA 30

63 Mon 20/06/2016 TrustB 82

63 Mon 20/06/2016 TrustC 112

etc.

The data is coming from #TempBHRAdm

Any help gratefully received.

Q


#2
SELECT ca.*
FROM #TempBHRAdm
CROSS APPLY (
    VALUES(Id, Weekday, Date, 'TrustA', TrustA),
          (Id, Weekday, Date, 'TrustB', TrustB),
          (Id, Weekday, Date, 'TrustC', TrustC)
) AS ca(Id, Weekday, Date, Site, Admissions)

#3

thank you Scott