• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 531
  • Last Modified:

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

0
Radhs74
Asked:
Radhs74
  • 2
  • 2
1 Solution
 
tigin44Commented:
select no, name, MAX(CASE WHEN shift = 1 THEN Salary END) AS "1",
                         MAX(CASE WHEN shift = 2 THEN Salary END) AS "2",
from yourTable
GROUP BY no, name
0
 
Radhs74Author Commented:
its giving missing expression error

0
 
Radhs74Author Commented:
i got it , thanks a lot

Rads
0
 
tigin44Commented:
what was the exact error message?
0
 
Shaju KumbalathDeputy General Manager - ITCommented:

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;
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now