sql - 关于 SQL 数据透视查询

Question

这是静态数据透视查询:

SELECT  
    *
FROM
    (SELECT
         PI.PRODID||' '||' ('||PI.PRODNAME||' - '||PI.PACKSIZE||')' PNAME,I.SIA_ID, IP.SALES_QTY
     FROM 
         DIS_INVOICE_INFO I, DIS_INVOICE_PRODUCT IP, MKT_PROD_INFO PI
     WHERE
         I.SLNO = IP.SLNO
         AND PI.PRODID = IP.PRODID) s
PIVOT  
    (SUM(SALES_QTY) 
        FOR SIA_ID IN (10080, 10084, 10082)
    ) pvt

但我想要一个动态查询。

喜欢:

......
PIVOT 
    (SUM(SALES_QTY) 
         FOR SIA_ID IN (SELECT DISTINCT SIA_ID FROM DIS_INVOICE_INFO) 
    ) pvt

Answer 1

你没有提到你的 DBMS ，我不知道其他DBMS系统，但对于 Oracle如果您正在制作 XML，您可以使其动态化结果如下例:

SELECT  
    *
FROM
    (SELECT PI.PRODID||' '||' ('||PI.PRODNAME||' - '||PI.PACKSIZE||')' PNAME,
            I.SIA_ID, IP.SALES_QTY
       FROM DIS_INVOICE_INFO I 
       INNER JOIN DIS_INVOICE_PRODUCT IP 
          ON (I.SLNO = IP.SLNO)
       INNER JOIN MKT_PROD_INFO PI
          ON (PI.PRODID = IP.PRODID)) s
PIVOT XML 
    (SUM(SALES_QTY) 
        FOR SIA_ID IN (SELECT DISTINCT SIA_ID FROM DIS_INVOICE_INFO)
    ) pvt;

通过使用以下 DDL声明；

create table DIS_INVOICE_INFO(SIA_ID int, SLNO int);
create table DIS_INVOICE_PRODUCT(PRODID int, SALES_QTY int, SLNO int);
create table MKT_PROD_INFO(PRODID int,PRODNAME varchar(90),PACKSIZE int);

insert all 
       into DIS_INVOICE_INFO values(10080,1);
       into DIS_INVOICE_PRODUCT values(1,100,1);
       into MKT_PROD_INFO values(1,'Milk',200);
select * from dual;

对于上述 SQL-Select，您会得到以下结果声明:

   PNAME       SIA_ID_XML
  ------------ ---------- 
  (Milk - 200)   <CLOB>

哪里<CLOB>是

<PivotSet><item><column name = "SIA_ID">10080</column>
<column name = "SUM(SALES_QTY)">100</column></item></PivotSet>

附言您不能使用 PIVOT动态没有 XML选项，我的意思是需要在 IN 中明确列出所有参数与您的情况一样的条款。