CODEWITHSUNDEEP
sql-servert-sql
matthew owens
matthew owens

Reputation: 1

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

JobNumber 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

Upvotes: 0

Views: 73

Answers (1)

mcha
mcha

Reputation: 2998

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

Upvotes: 1

Related Questions