枢轴查询问题

Question

我在T-SQL表函数，在3项返回一个分裂值对作业编号

例如

JobNumber SplitValue 
J1    A 
J1    B 
J1    C 

任何人都可以使用，将改变一个支点声明帮助结果集 所以我得到

JobNumber SplitValue1, SPlitValue2, SPlitValue3 

？

在此先感谢

Answer 1

以下Oracle下应该工作，我希望它可以帮助你得到一个想法：

WITH t AS 
(SELECT 'J1' JOBNUMBER, 'A' SPLITVALUE FROM dual 
    UNION ALL 
    SELECT 'J1' JOBNUMBER, 'B' SPLITVALUE FROM dual 
    UNION ALL 
    SELECT 'J1' JOBNUMBER, 'C' SPLITVALUE FROM dual 
    UNION ALL 
    SELECT 'J2' JOBNUMBER, 'Z' SPLITVALUE FROM dual 
    UNION ALL 
    SELECT 'J2' JOBNUMBER, 'X' SPLITVALUE FROM dual 
    UNION ALL 
    SELECT 'J2' JOBNUMBER, 'Y' SPLITVALUE FROM dual) 
---- 
    select JOBNUMBER, max(SPLITVALUE1), max(SPLITVALUE2), max(SPLITVALUE3) 
    from (
    select 
    JOBNUMBER, 
    case when row_number() over (partition by jobnumber order by SPLITVALUE) = 1 then SPLITVALUE else null end SPLITVALUE1, 
    case when row_number() over (partition by jobnumber order by SPLITVALUE) = 2 then SPLITVALUE else null end SPLITVALUE2, 
    case when row_number() over (partition by jobnumber order by SPLITVALUE) = 3 then SPLITVALUE else null end SPLITVALUE3 
    from t) 
group by JOBNUMBER order by jobnumber 

输出：

JOBNUMBER SPLITVALUE1  SPLITVALUE2  SPLITVALUE3 
--------- ---------------- ---------------- ---------------- 
J1  A    B    C 
J2  X    Y    Z