Pivot Query Issue

I have a table function in t-sql that returns a split value in 3 entries against a job number

for instance

J开发者_JAVA百科obNumber SplitValue
J1             A
J1             B
J1             C

Can anyone help with a pivot statement that would transform the result set so I get

JobNumber SplitValue1, SPlitValue2, SPlitValue3

?

Thanks in advance

the following should work under Oracle, i hope it could help you to get an idea :

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

output :

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