时间戳

首页 > 解决方案 > 查询将行转换为列 SQLite

sqlite - 查询将行转换为列 SQLite

问题描述

下午好，我想知道是否可以根据我表中的行数进行查询以生成列

example:

      ID       COD    DIAG
    111111111 | Z359 |  D   
    111111112 | Z359 |  D   
    111111112 | Z359 |  D   
    111111113 | Z359 |  R   
    111111113 | Z359 |  P   
    111111113 | Z359 |  R   
    111111114 | Z359 |  D   
    111111114 | Z359 |  D   
    111111114 | Z359 |  D   
    111111115 | Z359 |  D   


it would be ideal that columns be created according to the number of rows for each id, if not possible it would put a fixed number of columns.

    result query

        ID    | COD1 | DIAG1 | COD2 | DIAG2 | COD3 | DIAG3 
    111111111 | Z359 |   D   |      |       |      | 
    111111112 | Z359 |   D   | Z359 |   D   |      |
    111111113 | Z359 |   R   | Z359 |   P   | Z359 |  R
    111111114 | Z359 |   D   | Z359 |   D   | Z359 |  D
    111111115 | Z359 |   D   |      |       |      |

对不起我的英语

多谢 ！！

标签： sqlite

解决方案

第一个查询遵循重复问题的答案模式，此处包含用于比较。

WITH numbered AS (
     SELECT row_number() OVER 
                 (PARTITION BY ID ORDER BY COD, DIAG) 
                 AS seq,
            t.*
     FROM SO58566470 t)
SELECT ID,
       max(CASE WHEN seq = 1 THEN COD END) AS COD1,
       max(CASE WHEN seq = 1 THEN DIAG END) AS DIAG1,
       max(CASE WHEN seq = 2 THEN COD END) AS COD1,
       max(CASE WHEN seq = 2 THEN DIAG END) AS DIAG1,
       max(CASE WHEN seq = 3 THEN COD END) AS COD3,
       max(CASE WHEN seq = 3 THEN DIAG END) AS DIAG3
FROM numbered n
GROUP BY ID;

但这确实是对窗口函数的幼稚使用，因为它可以通过同时计算其他值来最大化窗口。第一个查询已经在收集和遍历分区行以获取行号，但它实际上通过使用聚合 max() 函数在下一个查询中收集值来重复该过程两次。

下面的查询看起来更长，可能更复杂，但它通过在同一过程中收集转换后的值来利用分区数据（即窗口数据）。但是因为窗口函数必须对每一行进行操作，所以有必要过滤掉“不完整”的行。我没有对查询进行任何类型的分析，但我怀疑第二个查询总体上效率更高。

WITH transform AS (
    SELECT id,
           lag(COD, 0)  OVER IDWin AS COD1,           
           lag(DIAG, 0) OVER IDWin AS DIAG1, 
           lag(COD, 1)  OVER IDWin AS COD2,
           lag(DIAG, 1) OVER IDWin AS DIAG2,
           lag(COD, 2)  OVER IDWin AS COD3,
           lag(DIAG, 2) OVER IDWin AS DIAG3,       
           row_number() OVER IDWin AS seq
    FROM SO58566470 t
    WINDOW IDWin AS (PARTITION BY ID ORDER BY COD, DIAG)
    ORDER BY ID, SEQ
  ),
  last AS (
    SELECT id, max(seq) as maxseq
    FROM transform
    GROUP BY id
  )
SELECT transform.*
FROM transform 
     JOIN last 
       ON transform.id = last.id AND transform.seq = last.maxseq
ORDER BY id;

推荐阅读