Home » SQL & PL/SQL » SQL & PL/SQL » Help required in converting query output.
icon5.gif  Help required in converting query output. [message #219835] Fri, 16 February 2007 04:52 Go to next message
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?./fa/2152/0/

[Mod edit: converted 700+kB bmp to 14 kb png attachment]
  • Attachment: sample.PNG
    (Size: 13.92KB, Downloaded 227 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 Go to previous messageGo to next message
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 #219847 is a reply to message #219844] Fri, 16 February 2007 06:13 Go to previous messageGo to next message
yd_261076
Messages: 19
Registered: February 2007
Junior Member
Hi,
Thank you for the reply, could you please explain what you want me to do exactly, because the query you gave is not running....
Re: Help required in converting query output. [message #219848 is a reply to message #219847] Fri, 16 February 2007 06:17 Go to previous messageGo to next message
pmaupoil
Messages: 40
Registered: February 2007
Location: France
Member
As I said, I have not tested it so you will have to save your output in a table and run my sql against it (replace "tb" with the name of your table obvioulsy).
Re: Help required in converting query output. [message #219858 is a reply to message #219848] Fri, 16 February 2007 06:48 Go to previous messageGo to next message
yd_261076
Messages: 19
Registered: February 2007
Junior Member
it's not working... could you please suggest some diff method?
Re: Help required in converting query output. [message #219859 is a reply to message #219858] Fri, 16 February 2007 06:59 Go to previous messageGo to next message
pmaupoil
Messages: 40
Registered: February 2007
Location: France
Member
Could you be a bit more specific on what is not working.
When do you get an error and send the error messages you are getting?
Re: Help required in converting query output. [message #219862 is a reply to message #219859] Fri, 16 February 2007 07:32 Go to previous messageGo to next message
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 110 times)
Re: Help required in converting query output. [message #219876 is a reply to message #219862] Fri, 16 February 2007 08:45 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #219883 is a reply to message #219882] Fri, 16 February 2007 09:30 Go to previous messageGo to next message
pmaupoil
Messages: 40
Registered: February 2007
Location: France
Member
Would have been nice to know it earlier...
I would also like to hear for another method...
Re: Help required in converting query output. [message #219885 is a reply to message #219883] Fri, 16 February 2007 09:45 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
So, you want the result to be a matrix of 20 million rows by 20 million columns? Shocked
Or do I misunderstand your requirement?
Re: Help required in converting query output. [message #219897 is a reply to message #219835] Fri, 16 February 2007 10:24 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Looks like a 'generic data model'. Read about this concept and watch some query examples. Maybe you will find more on asktom.
And good luck.
Re: Help required in converting query output. [message #219906 is a reply to message #219835] Fri, 16 February 2007 11:29 Go to previous messageGo to next message
yd_261076
Messages: 19
Registered: February 2007
Junior Member
I have 85 distinct values in the metatag column like CVrule,CVops,CVStress,CVcourage etc......
Re: Help required in converting query output. [message #219912 is a reply to message #219906] Fri, 16 February 2007 12:04 Go to previous message
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;
Previous Topic: need help in Query
Next Topic: Normalize / Denormalize
Goto Forum:
  


Current Time: Sun Dec 11 00:46:50 CST 2016

Total time taken to generate the page: 0.15644 seconds