How perform pivot over multiple column

Question

I was helping with this question Correlated Subquery in SQL

Using this QUERY I was able to achive this result

MRN1: Is the patient
Accession: Are exam on that patient.
DateA: The date of exam
DateB: The date of previous exam
num_days: Difference of day between exams

.

|   MRN1 | rn | Accession1 |         DateA    |         DateB    | num_days |
|--------|----|------------|------------------|------------------|----------|
| 001734 |  1 |      33104 | 12/21/2013 06:52 |           (null) |   (null) |
| 001734 |  2 |      33374 | 01/21/2014 08:19 | 12/21/2013 06:52 |       31 |
| 001734 |  3 |      33399 |  2/19/2014 11:48 | 01/21/2014 08:19 |       29 |
| 001734 |  4 |      34453 | 03/14/2014 09:14 |  2/19/2014 11:48 |       23 |
|  35681 |  1 |      28153 | 09/14/2012 05:00 |           (null) |   (null) |
|  35681 |  2 |      29007 | 11/16/2012 08:23 | 09/14/2012 05:00 |       63 |
|  80592 |  1 |      27122 | 06/26/2013 10:20 |           (null) |   (null) |
|  80592 |  2 |      27248 | 08/01/2013 06:23 | 06/26/2013 10:20 |       36 |

But I'm having problem to achive the final result OP want.

I know perform the dynamic pivot when is a basic format

 ID   fieldName fieldValue

But dont know how handle this table.

My guess is I have to prepare the table first before perform the dynamic pivot.

This is the desire output

|  MRN1 | Accession1 |                   ReadDate1 | Accession2 |                  ReadDate2 | num_days2 | Accession3 |       ReadDate3 | num_days3 | Accession4 |        ReadDate4 | num_days4 |
|-------|------------|-----------------------------|------------|----------------------------|-----------|------------|-----------------|-----------|------------|------------------|-----------|
|  1734 |      33104 |  December, 21 2013 06:52:00 |      33374 |  January, 21 2014 08:19:00 |        31 |      33399 | 2/19/2014 11:48 |        29 |      34453 | 03/14/2014 09:14 |        23 |
| 35681 |      28153 | September, 14 2012 05:00:00 |      29007 | November, 16 2012 08:23:00 |        63 |     (null) |          (null) |    (null) |     (null) |           (null) |    (null) |
| 80592 |      27122 |      June, 26 2013 10:20:00 |      27248 |   August, 01 2013 06:23:00 |        36 |     (null) |          (null) |    (null) |     (null) |           (null) |    (null) |

mysql doesn't have dynamic pivots. you can simulate one by specifying columns using an aggregate like MAX... one thing you could do is join on a numbers table between 1 and like a million. which could pivot on your mrn1 correctly — John Ruddell, Oct 21 '15 at 21:40
@JohnRuddell I know how emulate dynamic pivot. My problem is dont know how convert my table to a basic table with `ID, fieldName, fieldValue` Where I will be able to perform the pivot — Juan Carlos Oropeza, Oct 21 '15 at 21:42

JRD · Answer 1 · 2015-10-22T00:13:18.857

1

Can't you pivot on your existing query with

select q.mrn1,
       max(case when rn = 1 then q.accession1 end) accession1,
       max(case when rn = 1 then q.datea end) as read_date1,
       max(case when rn = 2 then q.accession1 end) accession2,
       max(case when rn = 2 then q.datea end) as read_date2,
       max(case when rn = 2 then q.num_days end) num_days2
       // And so on ...
from(
...
) q
group by q.mrn1;

Update:

If you're using some type of dynamic sql generation, you can create a field-value table on your intermediate result with:

create table fields(name varchar(10));

insert into fields values('accession'), ('date'), ('days');

select mrn1,
       max(case when f.name = 'accession' then accession1
                when f.name = 'date' then datea
                when f.name = 'days' then num_days
           end) v,
       f.name
from (
...
) q
// fields table can be any table with at least as many rows as number of fields to create, as long as the rows can be uniquely identified.
cross join fields f
group by mrn1, accession1, f.name;

edited Oct 22 '15 at 00:13

answered Oct 21 '15 at 21:47

JRD

1,957
1
12
17

This may work, but number of `accession` is dynamic. That is why I want make a dynamic pivot – Juan Carlos Oropeza Oct 21 '15 at 21:52
You'd need to use 3GL language to dynamically construct the query, or apply the format after getting the results. – JRD Oct 21 '15 at 21:58
I was checking this sample http://stackoverflow.com/questions/12598120/mysql-pivot-table-query-with-dynamic-columns and works ok. The problem is my table isnt on that format – Juan Carlos Oropeza Oct 21 '15 at 22:15
like I was saying in the previous comments if you join this on a numbers table you can simulate a dynamic pivot but its hacky and not recommended – John Ruddell Oct 21 '15 at 22:23
@JohnRuddell Thanks for your comment but my problem is how i take my current table to a format where I can use the `simulate pivot` – Juan Carlos Oropeza Oct 21 '15 at 22:52

How perform pivot over multiple column

1 Answers1