时间戳

首页 > 解决方案 > 如何使用 DB2 sql 代码动态地将列转置为表的行，其中列可能会随着时间的推移而增加且无需更改代码？

sql - 如何使用 DB2 sql 代码动态地将列转置为表的行，其中列可能会随着时间的推移而增加且无需更改代码？

问题描述

我有一个表格，其中列最终会随着时间的推移而增加。我想编写一个查询来转置表，即使后来的列增加，也不需要添加额外的代码行来实现它。我需要转置值为'Y'的那些列

Eg: Source data on day 1
| Emp_ID | DOC 1 | DOC 2 |
| ------ |-------|-------|
| 001    | Y     |Y      |
| 002    | N     |Y      |

Day 1 output
| Emp_ID | Transposed | 
| ------ |-------|
| 001    | DOC 1 |
| 001    | DOC 2 |
| 002    | DOC 2 |

现在最终列可能会增加，并且希望在不更改代码的情况下使用相同的查询块来处理它，可以吗？

Source data on day 2
| Emp_ID | DOC 1 | DOC 2 | DOC 3|
| ------ |-------|-------|------|
| 001    | Y     |Y      |N     |
| 002    | N     |Y      |Y     |
| 003    | N     |N      |N     |

Day 2 output
| Emp_ID | Transposed | 
| ------ |-------|
| 001    | DOC 1 |
| 001    | DOC 2 |
| 002    | DOC 2 |
| 002    | DOC 3 |

**Note have considered only docs having Y as a value. Thanks in advance**

标签： sqldb2

解决方案

您需要为给定的基表动态构造以下语句
MYSCHEMA.MYTAB (EMP_ID INT, DOC1 CHAR, ..., DOCn CHAR)：

SELECT T.EMP_ID, V.COLNAME
FROM 
  MYSCHEMA.MYTAB T
, (
SELECT COLNAME
FROM SYSCAT.COLUMNS
WHERE TABSCHEMA = 'MYSCHEMA' AND TABNAME = 'MYTAB' AND COLNAME <> 'EMP_ID' AND TYPENAME LIKE '%CHAR%'
  ) V
WHERE
   V.COLNAME = 'DOC1' AND T.DOC1 = 'Y'
...
OR V.COLNAME = 'DOCn' AND T.DOCn = 'Y'

该语句只有动态WHERE部分。子选择SYSCAT.COLUMNS返回所有要转置的表列（除 之外的所有表列EMP_ID）。
表函数中的以下SELECT INTO语句生成任何数量的此类列所需的最终语句。

CREATE OR REPLACE FUNCTION MYFUNC ()
RETURNS TABLE (EMP_ID INT, TRANSPOSED VARCHAR (128))
BEGIN
  DECLARE V_SQL VARCHAR (4000);
  DECLARE V_EMP_ID INT;
  DECLARE V_TRANSPOSED VARCHAR (128);
  DECLARE SQLSTATE CHAR(5);
  DECLARE C1 CURSOR FOR S1;
  
    SELECT 
     'SELECT T.EMP_ID, V.COLNAME '
  || 'FROM ' 
  || '  MYSCHEMA.MYTAB T '
  || ', ( '
  || 'SELECT COLNAME '
  || 'FROM SYSCAT.COLUMNS '
  || 'WHERE TABSCHEMA = ''MYSCHEMA'' AND TABNAME = ''MYTAB'' AND COLNAME <> ''EMP_ID'' AND TYPENAME LIKE ''%CHAR%'''
  || '  ) V '
  || 'WHERE '
  || LISTAGG ('V.COLNAME = ''' || COLNAME || ''' AND T.' || COLNAME || ' = ''Y''', ' OR ')
  INTO V_SQL
  FROM SYSCAT.COLUMNS
  WHERE TABSCHEMA = 'MYSCHEMA' AND TABNAME = 'MYTAB' AND COLNAME <> 'EMP_ID' AND TYPENAME LIKE '%CHAR%';

  PREPARE S1 FROM V_SQL;
  OPEN C1;
  L1: LOOP 
    FETCH C1 INTO V_EMP_ID, V_TRANSPOSED;
    IF SQLSTATE = '02000' THEN LEAVE L1; END IF;
    PIPE (V_EMP_ID, V_TRANSPOSED);
  END LOOP L1;
  CLOSE C1;
  RETURN;
END@

推荐阅读