Home » SQL & PL/SQL » SQL & PL/SQL » sql - transpose
sql - transpose [message #640609] Fri, 31 July 2015 10:29 Go to next message
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 #640610 is a reply to message #640609] Fri, 31 July 2015 10:32 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

You need to explain the logic behind the transformation. And also, please provide the one CREATE TABLE statement and the twelve INSERT statments needed to set up the problem. And, of course, show what SQL you have tried so far.
Re: sql - transpose [message #640611 is a reply to message #640609] Fri, 31 July 2015 10:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Also the solution depends on your Oracle version, so post it.

Starting with 11g, search for PIVOT in Database SQL Reference book.
Before, search for "pivot query" and/or "MAX(DECODE".

Re: sql - transpose [message #640613 is a reply to message #640611] Fri, 31 July 2015 10:47 Go to previous messageGo to next message
rankrose
Messages: 4
Registered: July 2015
Junior Member
Its oracle 11g.

the output should be as below.

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
Re: sql - transpose [message #640614 is a reply to message #640613] Fri, 31 July 2015 10:49 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Thankyou for repeating what you said before. Anything else you'ld like to add?
Re: sql - transpose [message #640615 is a reply to message #640613] Fri, 31 July 2015 10:54 Go to previous messageGo to next message
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 #640616 is a reply to message #640615] Fri, 31 July 2015 11:15 Go to previous messageGo to next message
rankrose
Messages: 4
Registered: July 2015
Junior Member
Thanks for your Quick response. I tried with Pivot. It gives the aggregate of my results. But i need the data as it is when doing transpose. I dont need to get count or make any changes in the existing data.

Please let me know if you need further info.
Re: sql - transpose [message #640617 is a reply to message #640616] Fri, 31 July 2015 11:17 Go to previous messageGo to next message
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 #640618 is a reply to message #640616] Fri, 31 July 2015 11:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Post what you have tried and post a test case.
Last chance for you with me.

Re: sql - transpose [message #640620 is a reply to message #640618] Fri, 31 July 2015 12:02 Go to previous messageGo to next message
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 #640621 is a reply to message #640620] Fri, 31 July 2015 12:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

1/ You still didn't post your Oracle version
2/ This is still not formatted.

Retry.

Re: sql - transpose [message #640665 is a reply to message #640620] Mon, 03 August 2015 01:58 Go to previous messageGo to next message
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 Go to previous message
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

Previous Topic: Delete duplicate records without Rowid and drop (2 Merged)
Next Topic: Display Employee details with min & max hired date
Goto Forum:
  


Current Time: Fri Mar 29 06:47:58 CDT 2024