i have the following data in SQL 2017
x
Number Description Value
37 Recorded 2019-11-06T10:51:25.482-05:00
37 ICD10Code Z32.00
37 ICD10Desc Encounter
37 Status Inactive
37 Recorded 2019-12-06T10:51:25.482-05:00
37 ICD10Code T11.00
37 ICD10Desc Test
37 Status Active
however i am new to pivot in sql and i tried different ways and not successful with the result i want it. below is the data i want, can someone please help to provide to achieve this.
Number Recorded ICD10Code ICD10Desc Status
37 2019-11-06T10:51:25.482-05:00 Z32.00 Encounter Inactive
37 2019-12-06T10:51:25.482-05:00 T11.00 Test Active
i tried with following query
select *
from
(
select patientnumber,description,value
from patient_Diagnosis
where (Description not like '%guid%' and description not like '%listorder%') and type != 0 and type =1 and description != 'statusdate'
and patientnumber = 946
) src
pivot
(
MIN(value)
FOR Description IN([Recorded],[ICD10COde],[ICD10Desc])
) piv;
Advertisement
Answer
Hi and thanks for updating.
I created a temporary table named #_tmp and loaded it with all the data that you had in those screenshots.
Input:
SELECT * FROM #_tmp
Output:
Number Description Value
946 Recorded 2019-07-11
946 ICD10Code F11.10
946 ICD10Desc Opioid abuse, uncomplicated
946 Status Resolved
946 Recorded 2019-05-02
946 ICD10Code F10.20
946 ICD10Desc Alcohol use disorder, Moderate
946 Status Resolved
Anyway, here’s the pivot. It uses a Common Table Expression to put a row_number() on each description and value.
;WITH tmpCTE AS (
SELECT X.*
, ROW_NUMBER() OVER(PARTITION BY X.[Description] ORDER BY X.[Number], X.[Description], X.[Value]) as [rn]
FROM #_tmp as X
)
SELECT pvt.[Number], pvt.[Recorded], pvt.[ICD10Code], pvt.[ICD10Desc], pvt.[Status]
FROM (
SELECT T.[Number], T.[Description], T.[Value], T.[rn]
FROM tmpCTE AS T
) AS p
PIVOT (
-- You will have to know the distinct names of the values for the columns
-- In this case, it is all the unique items from the Description column
-- Since that is what we're pivoting.
MAX([Value]) FOR [Description] IN ([Recorded], [ICD10Code], [ICD10Desc], [Status])
) as pvt
GROUP BY pvt.[rn], pvt.[Number], pvt.[Recorded], pvt.[ICD10Code], pvt.[ICD10Desc], pvt.[Status]
ORDER BY pvt.[Number], pvt.[Recorded] DESC
Output:
Number Recorded ICD10Code ICD10Desc Status
946 2019-07-11 F11.10 Opioid abuse, uncomplicated Resolved
946 2019-05-02 F10.20 Alcohol use disorder, Moderate Resolved
Note that if we just run the CTE:
SELECT X.*
, ROW_NUMBER() OVER(PARTITION BY X.[Description] ORDER BY X.[Number], X.[Description], X.[Value]) as [rn]
FROM #_tmp as X
We get the following. You can see the alternating rn
values to indicate groupings when pivoted. That helps keep things in order for our output.
Output:
Number Description Value rn
946 ICD10Code F10.20 1
946 ICD10Code F11.10 2
946 ICD10Desc Alcohol use disorder, Moderate 1
946 ICD10Desc Opioid abuse, uncomplicated 2
946 Recorded 2019-05-02 1
946 Recorded 2019-07-11 2
946 Status Resolved 1
946 Status Resolved 2