SQL Pivot of string

i have the following data in SQL 2017

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;

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