?
Solved

oracle: transpose rows to columns

Posted on 2012-08-22
1
Medium Priority
?
1,157 Views
Last Modified: 2012-08-22
HI,

I have a table that looks like this
EMPLID     COL_NAME     COL_VAL
123              col1                ABC
123              col2                9999
123              col3                Stop!
456              col1                 QQQ
456              col2                 87493
456              col3                 All Done

I need to transpose this to look like
EMPLID       col1           col2        col3
123             ABC           9999       Stop!
456             QQQ          87493     All Done

In the most efficient way possible. All fields are of type varchar
Thanx
0
Comment
Question by:jorbagw
1 Comment
 
LVL 25

Accepted Solution

by:
lwadwell earned 2000 total points
ID: 38323610
SELECT emplid
     , MAX(CASE WHEN col_name = 'col1' THEN col_val ELSE NULL END) as col1
     , MAX(CASE WHEN col_name = 'col2' THEN col_val ELSE NULL END) as col2
     , MAX(CASE WHEN col_name = 'col3' THEN col_val ELSE NULL END) as col3
FROM your_table
GROUP BY emplid
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

809 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