• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 585
  • Last Modified:

Repeat Group Columns

My request is straight forward, I wan to turn multiple rows into one row with multiple columns in oracle sql.
0
Roberto Madro R.
Asked:
Roberto Madro R.
  • 4
  • 3
1 Solution
 
ajexpertCommented:
Can you show us the sample output?
0
 
Roberto Madro R.Programmer AnalystAuthor Commented:
Sorry, I have no ouput, and no script generated yet, I'd like to see how you'd approach such request, here's the basics;

That's what I get now.

Unit, Test Type, Test Results

AA, AGN1, 11
AA, XZf2, 13.5
AA, ADGv, 17

I don't care for the "Test Type, I just want the "Unit" and "Test Results" to come out as follows;

AA, 11, 13.5, 17, ..
0
 
mpaladuguCommented:
select column_name||column_name2||column_name3 column_alias from table name
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
mpaladuguCommented:
sorry that is wrong..ignore the above comment
0
 
mpaladuguCommented:
http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php

above link has a direct answer to your question
0
 
mpaladuguCommented:
If you are using 10g, then this should do the trick without using pl/sql function,

copied from the link posted above...
Base Data:

    DEPTNO ENAME
---------- ----------
        20 SMITH
        30 ALLEN
        30 WARD
        20 JONES
        30 MARTIN
        30 BLAKE
        10 CLARK
        20 SCOTT
        10 KING
        30 TURNER
        20 ADAMS
        30 JAMES
        20 FORD
        10 MILLER

SELECT deptno,
       LTRIM(MAX(SYS_CONNECT_BY_PATH(ename,','))
       KEEP (DENSE_RANK LAST ORDER BY curr),',') AS employees
FROM   (SELECT deptno,
               ename,
               ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS curr,
               ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) -1 AS prev
        FROM   emp)
GROUP BY deptno
CONNECT BY prev = PRIOR curr AND deptno = PRIOR deptno
START WITH curr = 1;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
0
 
Roberto Madro R.Programmer AnalystAuthor Commented:
I thought I closed this string not due to any specific solution but rather to the deep insights provided by couple of members on this forum, I'm appreciative and thankful for their input, programmers in every field don't necessarily copy other people's ideas but other people's ideas usually inspire them to code differently thus getting the result they want. That's what happened in this case.

Many Thanks
0
 
Roberto Madro R.Programmer AnalystAuthor Commented:
.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now