| Need a Query to transpose rows to columns dynamically [message #641283] |
Wed, 12 August 2015 23:55  |
 |
yashaswini.gopalan
Messages: 7
Registered: August 2015
Location: bangalore
|
Junior Member |
|
|
Table 1
Factor
Complexity
Severity
Impact
Table2
Test_ID Stage_ID Factor Factor_Comment Factor_value
Test1 Stage 1 Complexity Medium 3
Test1 Stage1 Severity L1 2
Test1 Stage2 Impact More 5
Test1 Stage2 Complexity Low 2
I have two such tables. One having the factors another having the comments and values for each factors. Factors in table 1 can grow dynamically. I need to generate the below report
TestID StageID Complexity Complexity_Factor_Value Severity Severity_Factor_Value Impact Impact_Factor_Value
Test1 Stage1 Medium 3 L1 2 NULL NULL
Test1 Stage2 L1 2 NULL NULL More 5
There will be more TestID and Stage ID. I need to bring in the test and stage combination into a single row with all factor's value and comment like specified above. The factors should be fetched from table 1.Thanks in advance.
|
|
|
|
|
|
|
|
| Re: Need a Query to transpose rows to columns dynamically [message #641289 is a reply to message #641283] |
Thu, 13 August 2015 00:43  |
 |
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Note that PIVOT is a FAQ and solutions depend on your Oracle version you should post with every question.
[Updated on: Thu, 13 August 2015 00:45] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Need a Query to transpose rows to columns dynamically [message #641320 is a reply to message #641296] |
Thu, 13 August 2015 05:25 |
|
yashaswini.gopalan
Messages: 7
Registered: August 2015
Location: bangalore
|
Junior Member |
|
|
Hi,
Please find the queries to create the table
create table table1 (factor varchar2(30));
create table table2 (table_id varchar2(10),stage_id varchar2(10),factor varchar2(30),factor_comment varchar2(30),factor_value varchar2(30));
insert into table1 values ('Complexity');
insert into table1 values ('Severity');
insert into table1 values ('Impact');
insert into table2 values('Test1','Stage1','Complexity','Medium',3);
insert into table2 values('Test1', 'Stage1' ,'Severity', 'L1', 2);
insert into table2 values('Test1' ,'Stage2' ,'Impact', 'More', 5);
insert into table2 values('Test1', 'Stage2', 'Complexity' ,'Low',2 );
Thanks
|
|
|
|
| Re: Need a Query to transpose rows to columns dynamically [message #641338 is a reply to message #641320] |
Thu, 13 August 2015 07:57 |
|
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
From what I see "table1" of no use as all information are in "table2".
SQL> select table_id, stage_id,
2 max(decode(factor,'Complexity',factor_comment)) Complexity,
3 max(decode(factor,'Complexity',factor_value)) Complexity_value,
4 max(decode(factor,'Severity',factor_comment)) Severity,
5 max(decode(factor,'Severity',factor_value)) Severity_value,
6 max(decode(factor,'Impact',factor_comment)) Impact,
7 max(decode(factor,'Impact',factor_value)) Impact_value
8 from table2
9 group by table_id, stage_id
10 order by table_id, stage_id
11 /
TABLE_ID STAGE_ID COMPLEXITY COMPLEXITY SEVERITY SEVERITY_V IMPACT IMPACT_VAL
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
Test1 Stage1 Medium 3 L1 2
Test1 Stage2 Low 2 More 5
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Need a Query to transpose rows to columns dynamically [message #641441 is a reply to message #641439] |
Fri, 14 August 2015 20:24 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
Think I found it.
This is an article that builds upon another article (isn't I.T. great... we openly steal from each other and we like it). Of course we give credit for what we have stolen.
The article is about some guy named Anton who created a pipelined function that can do a dynamic pivot. After you read the article you will likely think two things:
1. what did I just read, cause I am not sure I understand it all
2. but that is not what I asked for, I am not trying to pivot any data
#1 I get. I don't understand it all either. #2 though means you have missed the point. The point is, the query (select * from <pivot crap>) is generating a dynamic number of output columns. That is a head bender, but as I interpret your request, it is what you wanted right?. So read up and figure out how to translate it into something else you can use. Then remember to post your working solution so we can steal it from you. Indeed, given the repeating nature of your test case, you may not need to do too much alteration. Understand it and then warp it to your own twisted ends?
select * from table( pivot('select deptno,job,avg(sal) sal_avg from emp group by deptno,job'));
DEPTNO ANALYST CLERK MANAGER PRESIDENT SALESMAN WIZARD
-------- ---------- ---------- ---------- ---------- ---------- ----------
10 1300 2450 5000 1991
20 3000 950 2975
30 950 2850 1400
40 1100
Neat.
In the above example, DEPTNO is the key of the output row, JOB becomes all the columns, and sal_avg is the data in the matrix. If there were 50 job titles, there would be 51 columns. Thus you have a query that can dynamically output any number of columns. Now you just have to wade through the code this provides, in order to figure out the one special moment where the magic happens, and then exploit it.
Good luck. Kevin
Wish I could have met this Anton dude.
[Updated on: Fri, 14 August 2015 20:45] Report message to a moderator |
|
|
|
| Re: Need a Query to transpose rows to columns dynamically [message #641448 is a reply to message #641439] |
Sat, 15 August 2015 02:00 |
|
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Kevin Meade wrote on Sat, 15 August 2015 02:29I once saw a really brilliant piece of code that did exactly what you want. It used the ANYDATA data type. Sadly I have not been able to find it. I'll keep looking.
I posted several times such "solutions".
As I said there is no solution in pure SQL.
The best way depends on the client tool.
[Updated on: Sat, 15 August 2015 02:03] Report message to a moderator |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
