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

本文介绍了一种使用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 了

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值