行转列一个好玩的SQL（列数会视记录数变动，实现系统自定义字段功能）

最新推荐文章于 2024-02-01 17:18:00 发布

转载最新推荐文章于 2024-02-01 17:18:00 发布 · 802 阅读

oracle开发专栏收录该内容

11 篇文章

订阅专栏

本文介绍了一种使用SQL拼接技术动态生成多列数据的方法，特别适用于处理不确定数量的列需求，避免了频繁修改代码的问题。通过实例演示了如何在已有数据表基础上，动态构建SQL查询来实现复杂的数据转换需求。

链接来源于itpub

无意浏览itpub论坛的时候，看到的一个帖子，看到里面的一个写法非常ok，之前工作中也遇到类似的报表需求，但是当时没有找到这样的写法，所以这个需求是说服客户更换了另一种写法。今天遇到了，记一下，说不定以后会用到

create table Product  
(   
  ProductID   number,   
  age number,
  table_id  varchar2(20)
); 
create table ProductField   
(   
  ProductFieldID   number,   
  FieldName    VARCHAR2(50),
  FieldType VARCHAR2(20),   
  table_id  varchar2(20)  
) ;
create table Product_Field   
(   
  ProductID       number,   
  ProductFieldID    VARCHAR2(50),
  Val  VARCHAR2(20)
) ;  
insert into Product (ProductID, age,table_id) values (1,28, 'ps_layer');   
   
insert into ProductField (ProductFieldID, FieldName,FieldType,table_id) values (1,'Name','varchar2','ps_layer'); 
insert into ProductField (ProductFieldID, FieldName,FieldType,table_id) values (2,'productType','varchar2','ps_layer');
insert into ProductField (ProductFieldID, FieldName,FieldType,table_id) values (3,'notes','varchar2','ps_layer');

insert into Product_Field (ProductID , ProductFieldID,val) values (1,1, 'MP3');  
insert into Product_Field (ProductID , ProductFieldID,val) values (1,2, '消费数码');  
insert into Product_Field (ProductID , ProductFieldID,val) values (1,3, '备注');

SQL> select * from Product;
 
 PRODUCTID        AGE TABLE_ID
---------- ---------- --------------------
         1         28 ps_layer
 
SQL> select * from ProductField;
 
PRODUCTFIELDID FIELDNAME                                          FIELDTYPE            TABLE_ID
-------------- -------------------------------------------------- -------------------- --------------------
             1 Name                                               varchar2             ps_layer
             2 productType                                        varchar2             ps_layer
             3 notes                                              varchar2             ps_layer
 
SQL> select * from Product_Field;
 
 PRODUCTID PRODUCTFIELDID                                     VAL
---------- -------------------------------------------------- --------------------
         1 1                                                  MP3
         1 2                                                  消费数码
         1 3                                                  备注

需要实现的需求效果如下：

 PRODUCTID        AGE NAME                 PRODUCTTYPE          NOTES
---------- ---------- -------------------- -------------------- --------------------
         1         28 MP3                  消费数码             备注

关联关系是，如果ProductField表中加一条记录，那么这个最终结果中就要相应多一列

这种需求，就是在行转列的时候，列的数量是不定值，如果仅限于使用decode之类的函数的话，每次多一条记录就需要不停的修改代码，显然不能满足需求。

然后，在浏览帖子的时候，我发现有条回复，很ok，通过SQL拼接，如下

SELECT 'select A.ProductID,MAX(AGE) AS AGE,'||
WMSYS.WM_CONCAT('MAX(DECODE(B.FieldName,'''||FieldName||''',C.Val)) AS '||FieldName)
||' FROM Product A,ProductField B,Product_Field C
WHERE A.ProductID=C.ProductID
AND A.table_id =B.table_id
AND B.ProductFieldID=C.ProductFieldID
AND A.table_id='''||'ps_layer'||'''
GROUP BY A.ProductID'
FROM ProductField 
WHERE table_id='ps_layer';

这个sql执行之后得到一条SQL，再执行这个SQL，即可得到结果，如下

SQL> SELECT A.PRODUCTID,
  2         MAX(AGE) AS AGE,
  3         MAX(DECODE(B.FIELDNAME, 'Name', C.VAL)) AS NAME,
  4         MAX(DECODE(B.FIELDNAME, 'productType', C.VAL)) AS PRODUCTTYPE,
  5         MAX(DECODE(B.FIELDNAME, 'notes', C.VAL)) AS NOTES
  6    FROM PRODUCT A, PRODUCTFIELD B, PRODUCT_FIELD C
  7   WHERE A.PRODUCTID = C.PRODUCTID
  8     AND A.TABLE_ID = B.TABLE_ID
  9     AND B.PRODUCTFIELDID = C.PRODUCTFIELDID
 10     AND A.TABLE_ID = 'ps_layer'
 11   GROUP BY A.PRODUCTID;
 
 PRODUCTID        AGE NAME                 PRODUCTTYPE          NOTES
---------- ---------- -------------------- -------------------- --------------------
         1         28 MP3                  消费数码             备注

这样，下次遇到这种需求时，可以模拟这种拼接的SQL，然后返回结果集，OK 了