asked on

Select the rows data , column wise

I have some data like this and I want the output as below

No      Name      Shift        Salary

1      Jack 1        10
1      Jack        2        12
2      Mary        1        11
2      Mary        2        13
3      Patty        1        14
3      Patty        2        12

output should be as below

No      Name       1       2

1      Jack 10      12
2      Mary        11      13
3      Patty      14      12

anyones help is greatly appreciated

thanks

ASKER CERTIFIED SOLUTION

tigin44

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rads R

ASKER

its giving missing expression error

Rads R

ASKER

i got it , thanks a lot

Rads

tigin44

what was the exact error message?

Shaju Kumbalath

SELECT no,name,
LTRIM (EXTRACT (XMLAGG (XMLELEMENT ("x", ',' || shift)), '/x/text()'),
','
)
FROM yourtable
GROUP BY no,name

or

SELECT name,
LTRIM(MAX(SYS_CONNECT_BY_PATH(shift,','))
KEEP (DENSE_RANK LAST ORDER BY curr),',') AS list
FROM (SELECT no,name,
shift,
ROW_NUMBER() OVER (PARTITION BY no,name ORDER BY shift) AS curr,
ROW_NUMBER() OVER (PARTITION BY no,name ORDER BY shift) -1 AS prev
FROM yourtable)
GROUP BY no,name
CONNECT BY prev = PRIOR curr AND name = PRIOR name
START WITH curr = 1;