query to transpose rows to columns SQLite

Question

Good afternoon, I would like to know if it is possible to make a query to generate columns according to the number of rows that I have in my table

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

sorry my english

Thanks a Lot !!

Answer 1

This first query follows the pattern of the answer to the duplicate question, included here for comparison.

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;

But that really is a naive use of window functions, since it could have maximized the window by calculating other values at the same time. The first query is already collecting and traversing partitioned rows to get the row number, yet it essentially repeats that process twice by collecting values in the next query using the aggregate max() functions.

The following query looks longer and perhaps more complicated, but it takes advantage of the partitioned data (i.e. window data) by collecting the transformed values in the same process. But because window functions necessarily operate on each row, it becomes necessary to filter out "incomplete" rows. I did not do any kind of profiling on the queries, but I suspect this second query is much more efficient overall.

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;