Select the rows data , column wise

Posted on 2010-01-11
Medium Priority
Last Modified: 2013-12-18
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


Question by:Radhs74
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 26

Accepted Solution

tigin44 earned 1000 total points
ID: 26288840
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

Author Comment

ID: 26289236
its giving missing expression error


Author Comment

ID: 26289241
i got it , thanks a lot

LVL 26

Expert Comment

ID: 26289307
what was the exact error message?
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 26289942

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

SELECT name,
       KEEP (DENSE_RANK LAST ORDER BY curr),',') AS list
FROM   (SELECT no,name,
               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;

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question