Home » SQL & PL/SQL » SQL & PL/SQL » sql - transpose
sql - transpose [message #640609] |
Fri, 31 July 2015 10:29 |
|
rankrose
Messages: 4 Registered: July 2015
|
Junior Member |
|
|
Hi,
I have data as below in a table.
FORM_ID DEPT_ID NAME QSTN_NAME ASWR
1234 23 test user_id A123
1234 23 test quarter Q1
1234 23 test user_name Grace
1234 23 test user_data tt
4567 23 test1 user_id A123
4567 23 test1 quarter Q2
4567 23 test1 user_name Grace
4567 23 test1 user_data zz
8458 24 test3 user_id B123
8458 24 test3 quarter Q1
8458 24 test3 user_name Tony
8458 24 test3 user_data ff
And need to select data from the above table and the output should be as below.
DEPT_ID NAME USER_ID QUARTER USER_NAME USER_DATA
23 test A123 Q1 Grace tt
24 test1 A123 Q2 Grace zz
25 test3 B123 Q1 Tony ff
Please suggest the efficient way to do it.
[Edit MC: add tags and format post]
[Updated on: Fri, 31 July 2015 10:38] by Moderator Report message to a moderator
|
|
|
|
|
|
|
Re: sql - transpose [message #640615 is a reply to message #640613] |
Fri, 31 July 2015 10:54 |
|
Michel Cadot
Messages: 68625 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
You MUST read the links we have provided you and provide what we asked in the way we asked.
See what have I done to your first post and compare it to your last one. Don't you see some differences?
[Updated on: Fri, 31 July 2015 10:55] Report message to a moderator
|
|
|
|
Re: sql - transpose [message #640617 is a reply to message #640616] |
Fri, 31 July 2015 11:17 |
John Watson
Messages: 8922 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
You have ignored everything I said. I am sorry to have wasted your time. Perhaps you will be prepared to accept assistance from someone else. Goodbye.
|
|
|
|
Re: sql - transpose [message #640620 is a reply to message #640618] |
Fri, 31 July 2015 12:02 |
|
rankrose
Messages: 4 Registered: July 2015
|
Junior Member |
|
|
Sorry for not providing the right details. Hope the below info helps you!
-- create table:
create table T1
(
form_id varchar2(32),
dept_id varchar2(32),
name varchar2(32),
qstn_name varchar2(32),
aswr varchar2(32)
);
-- Insert Statements
INSERT INTO T1(FORM_ID,DEPT_ID,NAME,QSTN_NAME,ASWR)VALUES('1234','23','test','user_id','A123');
INSERT INTO T1(FORM_ID,DEPT_ID,NAME,QSTN_NAME,ASWR)VALUES('1234','23','test','quarter','Q1');
INSERT INTO T1(FORM_ID,DEPT_ID,NAME,QSTN_NAME,ASWR)VALUES('1234','23','test','user_name','Grace');
INSERT INTO T1(FORM_ID,DEPT_ID,NAME,QSTN_NAME,ASWR)VALUES('1234','23','test','user_data','tt');
INSERT INTO T1(FORM_ID,DEPT_ID,NAME,QSTN_NAME,ASWR)VALUES('4567','23','test1','user_id','A123');
INSERT INTO T1(FORM_ID,DEPT_ID,NAME,QSTN_NAME,ASWR)VALUES('4567','23','test1','quarter','Q2');
INSERT INTO T1(FORM_ID,DEPT_ID,NAME,QSTN_NAME,ASWR)VALUES('4567','23','test1','user_name','Grace');
INSERT INTO T1(FORM_ID,DEPT_ID,NAME,QSTN_NAME,ASWR)VALUES('4567','23','test1','user_data','zz');
INSERT INTO T1(FORM_ID,DEPT_ID,NAME,QSTN_NAME,ASWR)VALUES('8458','24','test3','user_id','B123');
INSERT INTO T1(FORM_ID,DEPT_ID,NAME,QSTN_NAME,ASWR)VALUES('8458','24','test3','quarter','Q1');
INSERT INTO T1(FORM_ID,DEPT_ID,NAME,QSTN_NAME,ASWR)VALUES('8458','24','test3','user_name','Tony');
INSERT INTO T1(FORM_ID,DEPT_ID,NAME,QSTN_NAME,ASWR)VALUES('8458','24','test3','user_data','ff');
commit;
-- Sql Query - Pivot:( The pivot query which I used. I think that I should not use count. However I am not sure what should I use).
select * from (select dept_id, qstn_name from t1)
pivot
(
count(qstn_name)
for qstn_name in ('name' NAME,'user_id' USER_ID,'quarter' QUARTER,'user_name' USER_NAME, 'user_data' USER_DATA)
);
Result for Pivot: (Above sql query Result)
DEPT_ID NAME USER_ID QUARTER USER_NAME USER_DATA
24 0 1 0 1 1
23 0 2 2 2 2
Expected Result:
DEPT_ID NAME USER_ID QUARTER USER_NAME USER_DATA
23 test A123 Q1 Grace tt
23 test1 A123 Q2 Grace zz
24 test3 B123 Q1 Tony ff
*BlackSwan added {code} tags. Please do so yourself in the future.
How to use {code} tags and make your code easier to read
[Updated on: Sat, 01 August 2015 09:13] by Moderator Report message to a moderator
|
|
|
|
Re: sql - transpose [message #640665 is a reply to message #640620] |
Mon, 03 August 2015 01:58 |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
Quote:The pivot query which I used. I think that I should not use count. However I am not sure what should I use
You should use what you want to display - the value of ASWR column I suppose. Also, as you use the values of NAME and ASWR, you should not get rid of them in the inner subquery - it is useless anyway.
select dept_id, name, user_id_aswr, quarter_aswr, user_name_aswr, user_data_aswr
from t1 pivot ( max(aswr) aswr
for qstn_name in ('user_id' USER_ID,'quarter' QUARTER,'user_name' USER_NAME, 'user_data' USER_DATA) )
order by dept_id, name;
(suppose that the Oracle version supports PIVOT clause as its attempt including result set was posted)
|
|
|
Re: sql - transpose [message #645200 is a reply to message #640609] |
Sun, 29 November 2015 08:04 |
|
Michel Cadot
Messages: 68625 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Solution for all Oracle versions:
SQL> select dept_id, name,
2 max(decode(qstn_name, 'user_id', aswr)) user_id,
3 max(decode(qstn_name, 'quarter', aswr)) quarter,
4 max(decode(qstn_name, 'user_name', aswr)) user_name,
5 max(decode(qstn_name, 'user_data', aswr)) user_data
6 from t1
7 group by dept_id, name
8 order by dept_id, name
9 /
DEPT_ID NAME USER_ID QUARTER USER_NAME USER_DATA
------- ----- ------- ------- --------- ---------
23 test A123 Q1 Grace tt
23 test1 A123 Q2 Grace zz
24 test3 B123 Q1 Tony ff
Solution for version starting with 11g:
SQL> select dept_id, name, user_id, quarter, user_name, user_data
2 from t1
3 pivot (max(aswr)
4 for qstn_name in ('user_id' as user_id,
5 'quarter' as quarter,
6 'user_name' as user_name,
7 'user_data' as user_data))
8 order by dept_id, name
9 /
DEPT_ID NAME USER_ID QUARTER USER_NAME USER_DATA
------- ----- ------- ------- --------- ---------
23 test A123 Q1 Grace tt
23 test1 A123 Q2 Grace zz
24 test3 B123 Q1 Tony ff
|
|
|
Goto Forum:
Current Time: Fri Mar 29 06:47:58 CDT 2024
|