Show database columns as headers

Question

I have a very strange situation and need expert opinion that is there any solution available or should i change my database structure.

Test Tables

ID    TestName
1       HP
2       PV
3       UX

Patient Test Records

ID    PID    TestName     TestValues        Date
1      2       HP            23          12-11-2017
2      2       PV            88          12-11-2017
3      5       UX            24          14-11-2017
4      5       HP            44          14-11-2017
5      2       UX            33          15-11-2017

Expected Output for each patient

TestName   PId    12-11-2017    15-12-2017
   HP       2        23            -
   PV       2        88            -
   UX       2        -            33

Now should I restructure my DB and is there any solution in MSSQL.

Looks like you want to investigate PIVOT/UNPIVOT ... turn column values into column headers and vice-versa (rows into columns, columns into rows). — pmbAustin, Sep 12 '17 at 19:41
I tried to use PIVOT but in my case patient visit dates are dynamic so not sure how to figure it out. — , Sep 12 '17 at 19:42
See [This](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — AVK, Sep 12 '17 at 19:52

score 0 · Answer 1 · answered Sep 12 '17 at 19:59

You can query as below:

Declare @cols1 varchar(max)
Declare @query nvarchar(max)

Select @cols1 = stuff((select distinct ','+QuoteName([date]) from #patientdata where pid = 2 for xml path('')),1,1,'')

Select @query = ' Select * from 
    ( Select PID, TestName, TestValues, [Date] from #patientdata where pid = 2) a
    pivot (max(TestValues) for [date] in (' + @cols1 + ') ) p '

Exec sp_executeSql @query

Output as below:

+-----+----------+------------+------------+
| PID | TestName | 2017-11-12 | 2017-11-15 |
+-----+----------+------------+------------+
|   2 | HP       | 23         | NULL       |
|   2 | PV       | 88         | NULL       |
|   2 | UX       | NULL       | 33         |
+-----+----------+------------+------------+

Thanks a lot for your kind reply. It works like a charm but having issue when i tried to execute this query in SQL CE as i am using compact edition. — , Sep 13 '17 at 10:43

Show database columns as headers

Test Tables

Patient Test Records

Expected Output for each patient

1 Answers1