无意浏览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 了