Home » SQL & PL/SQL » SQL & PL/SQL » Help required in converting query output.
 Help required in converting query output. [message #219835] |
Fri, 16 February 2007 04:52  |
yd_261076
Messages: 19
Registered: February 2007
|
Junior Member |
|
|
Hi,
I have a query which gives me following output:
VAL REPID QUESTID ID METATAG
2 73201619 684379 825 TDespect
2 73201856 684295 825 TDule
1 73201856 684296 825 TDps
2 73201856 684297 825 TDtress
2 73201857 684379 825 TDourage
1 73201857 684295 825 TDnviro
2 73201857 684296 825 TDrust
2 73201857 684297 825 YDccount
1 73201858 684379 825 YDulfilling
1 73201858 684295 825 TDost
I Need to convert rows in metatag to columns and represent the correponding values in attached format.
The val value for each metatag for a particular id,repid,qid should come under it.How can I achive this?
[Mod edit: converted 700+kB bmp to 14 kb png attachment]
-
Attachment: sample.PNG
(Size: 13.92KB, Downloaded 770 times)
[Updated on: Fri, 16 February 2007 08:35] by Moderator Report message to a moderator |
|
|
|
| Re: Help required in converting query output. [message #219844 is a reply to message #219835] |
Fri, 16 February 2007 05:56  |
pmaupoil
Messages: 40
Registered: February 2007
Location: France
|
Member |
|
|
I have not tested it but that should somehow works I think if you run that statement and then run the output... (you will have to remove the comma on the last column of the output.
SELECT 'SELECT REPID, QUESTID, ID, ' FROM dual
UNION ALL
SELECT 'decode(METATAG, ' || '''' || metatag || ''', val, NULL) ' || metatag || ','
FROM tb
UNION ALL
SELECT 'FROM tb;' FROM dual;
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Help required in converting query output. [message #219862 is a reply to message #219859] |
Fri, 16 February 2007 07:32 |
yd_261076
Messages: 19
Registered: February 2007
|
Junior Member |
|
|
when i run this query:
SELECT 'SELECT REPID, QUESTID, ID, ' FROM dual
UNION ALL
SELECT 'decode(METATAG, ' || '''' || metatag || ''', val, NULL) ' || metatag || ','
FROM Lore_t_Question_Master
UNION ALL
SELECT 'FROM Lore_t_Question_Master;' FROM dual
it gives me following out put:
'SELECTREPID,QUESTID,ID,'
SELECT REPID, QUESTID, ID,
decode(METATAG, '', val, NULL) ,
decode(METATAG, '', val, NULL) ,
decode(METATAG, '', val, NULL) ,
decode(METATAG, '', val, NULL) ,
decode(METATAG, '', val, NULL) ,
decode(METATAG, '', val, NULL) ,
decode(METATAG, '', val, NULL) ,
Let me once again explian what I actually want to do:
please check the image in the zip file sample2.zip.
Could you tell me what to do?
-
Attachment: sample2.zip
(Size: 20.66KB, Downloaded 869 times)
|
|
|
|
| Re: Help required in converting query output. [message #219876 is a reply to message #219862] |
Fri, 16 February 2007 08:45 |
pmaupoil
Messages: 40
Registered: February 2007
Location: France
|
Member |
|
|
I did it and it ran straight away...
create table pm_test(respondentid NUMBER,
questid NUMBER,
formid NUMBER,
answerval NUMBER,
metatag varchar2(20));
insert into pm_test values(72058168,672683,813,null,'CVRule');
insert into pm_test values(72058168,672684,813,null,'CVOps');
insert into pm_test values(72058168,672685,813,null,'CVStress');
insert into pm_test values(72058168,672686,813,2,'CVCourage');
insert into pm_test values(72058168,672687,813,null,'CVEnviro');
insert into pm_test values(72058168,672688,813,2,'CVTrust');
insert into pm_test values(72058168,672689,813,1,'CVAccount');
insert into pm_test values(72058168,672690,813,null,'CVFulfilling');
insert into pm_test values(72058168,672691,813,null,'CVCost');
insert into pm_test values(72058168,672692,813,null,'CVMeaning');
insert into pm_test values(72058168,672693,813,null,'CVWellBeing');
COMMIT;
-- copy and run the output of this query
SELECT 'SELECT respondentid, questid, formid, ' FROM dual
UNION ALL
SELECT 'decode(METATAG, ' || '''' || metatag || ''', answerval, NULL) ' || metatag || ','
FROM pm_test
UNION ALL
SELECT 'FROM pm_test;' FROM dual;
-- query output from above (note:remove comma at the end of line on last column) - run it
SELECT respondentid, questid, formid,
decode(METATAG, 'CVRule', answerval, NULL) CVRule,
decode(METATAG, 'CVOps', answerval, NULL) CVOps,
decode(METATAG, 'CVStress', answerval, NULL) CVStress,
decode(METATAG, 'CVCourage', answerval, NULL) CVCourage,
decode(METATAG, 'CVEnviro', answerval, NULL) CVEnviro,
decode(METATAG, 'CVTrust', answerval, NULL) CVTrust,
decode(METATAG, 'CVAccount', answerval, NULL) CVAccount,
decode(METATAG, 'CVFulfilling', answerval, NULL) CVFulfilling,
decode(METATAG, 'CVCost', answerval, NULL) CVCost,
decode(METATAG, 'CVMeaning', answerval, NULL) CVMeaning,
decode(METATAG, 'CVWellBeing', answerval, NULL) CVWellBeing
FROM pm_test;
|
|
|
|
| Re: Help required in converting query output. [message #219882 is a reply to message #219876] |
Fri, 16 February 2007 09:22 |
yd_261076
Messages: 19
Registered: February 2007
|
Junior Member |
|
|
I have 20 million records in this table... The o/p of the query
SELECT 'SELECT respondentid, questid, formid, ' FROM dual
UNION ALL
SELECT 'decode(METATAG, ' || '''' || metatag || ''', answerval, NULL) ' || metatag || ','
FROM pm_test
UNION ALL
SELECT 'FROM pm_test;' FROM dual;
Will have lot of rows and it will be impossible for me to run that query....
Can you suggest an alternate method?
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Help required in converting query output. [message #219912 is a reply to message #219906] |
Fri, 16 February 2007 12:04 |
pmaupoil
Messages: 40
Registered: February 2007
Location: France
|
Member |
|
|
just add a distinct in the 2nd select clause...
SELECT 'SELECT respondentid, questid, formid, ' FROM dual
UNION ALL
SELECT DISTINCT 'decode(METATAG, ' || '''' || metatag || ''', answerval, NULL) ' || metatag || ','
FROM pm_test
UNION ALL
SELECT 'FROM pm_test;' FROM dual;
|
|
|
|
Goto Forum:
Current Time: Mon Aug 25 23:04:42 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
