mysql - 如何执行多列透视

Question

我正在帮助解决这个问题Correlated Subquery in SQL

使用此 QUERY 我能够达到这个结果

• MRN1:是患者
• 加入:正在对该患者进行检查。
• DateA:考试日期
• DateB:上次考试的日期
• num_days:考试之间的日期差异

.

|   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 |

但是我无法获得 OP 想要的最终结果。

我知道执行 dynamic pivot 什么时候是基本格式

 ID   fieldName fieldValue 

但不知道如何处理这个表。

我的猜测是，在执行动态透视之前我必须先准备好表格。

这是期望的输出

|  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) |

Answer 1

您不能以现有查询为中心吗

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;

更新:

如果您使用某种类型的动态 SQL 生成，则可以使用以下方法在中间结果上创建字段值表:

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;