Convert specific columns to rows in SQL

Question

I am selecting PatientID, Location, LN, FN, ServiceCode1, ServiceDate1, ServiceCode2, ServiceDate2 from a table. I am trying to list each service and corresponding date as its own row. The attached image shows how I want this to look highlighted in green. I've tried using the PIVOT function but with no luck.

You actually want `UNPIVOT`. http://stackoverflow.com/questions/24828346/sql-server-unpivot-multiple-columns — SS_DBA, Dec 28 '16 at 20:59

John Cappelletti · Answer 1 · 2016-12-28T21:47:35.780

1

With the help of a Cross Join

Select  A.PatientID
       ,A.Location
       ,A.Last_Name
       ,A.First_Name
       ,B.*
 From  YourTable A
 Cross Join (
              Values ('Service_Code1',A.Service_Date1)
                    ,('Service_Code2',A.Service_Date2)
            ) B  (Service_Code,Service_Date)

edited Dec 28 '16 at 21:47

answered Dec 28 '16 at 21:31

John Cappelletti

79,615
7
44
66

Convert specific columns to rows in SQL

1 Answers1