ANSI sql to dynamically convert rows into column data

Question

I have a dynamic input data as below

   Id  val
   --- -----
    10  A
    10  B
    11  A
    11  B
    11  C
    .   .
    .   .

I need to print it following order.

    Id  Val
   ---- ----
    10  A,B
    11  A,B,C

I need to write single query to convert above data without using any inbuilt functions.

My try: I can try this out by populating data in to some temporary data and update records as below.

Step 1: try loading into temp data with unique id column alone and val as null as below.

create table temp as (select id, null as val from table group by id) with data;

Step 2: Update as follows.

update temp t2 
set val=(case when t2.val is null then t1.val else t1.val || ',' t2.val end) from 
(select val from table t1 where t1.val= t2.val) t1

Now, temp table will have above output... But all i need is , is there anyway to bring this output without using Temp table (single query)..

Your answer is here http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string. Next time, try google — Jaques, Nov 21 '14 at 11:55
The use of `||` for string concatenation strongly suggests Oracle. I am removing the other database tags. Of course, the OP can add the correct flags, if this is not reasonable. — Gordon Linoff, Nov 21 '14 at 11:59
@Jaques : Thanks. but i need ANSI standard format. they are using some inbuilt function to achieve this.,. — logan, Nov 21 '14 at 12:00
@Mihai : Yes. I need generic query which can run on any RDBMS — logan, Nov 21 '14 at 12:05
@Mihai . . . The question is more like: "Why do you want ANSI standard? No database supports *that*." Okay, increasingly the standard and databases are moving closer in many areas, so that is a tad too cynical. — Gordon Linoff, Nov 21 '14 at 12:05

score 0 · Answer 1 · answered Nov 21 '14 at 11:54

0

can try this:

select ID, group_concat(distinct value) as value
from table
group by ID

answered Nov 21 '14 at 11:54

Krish

what is that group_concat ? – logan Nov 21 '14 at 11:57
okay i see you've tagged wrong tags. above code works for mysql. here similar to group_concat in oracle: http://stackoverflow.com/questions/16771086/is-there-any-function-in-oracle-similar-like-group-concat-of-mysql – Krish Nov 21 '14 at 12:01

score 0 · Answer 2 · answered Nov 21 '14 at 12:01

0

In Oracle, you would use listagg():

select id, listagg(val, ',') within group (order by val)
from table t
group by id
order by id;

There is not ANSI standard mechanism for doing this operation, to the best of my knowledge.

answered Nov 21 '14 at 12:01

Gordon Linoff

Thanks gordon.. but i dont want to use any inbuilt function. like listagg.. is there any common ANSI standard SQL for the same ? – logan Nov 21 '14 at 12:02
one more thing, do you think, whatever i have done using `UPDATE` query is correct ? – logan Nov 21 '14 at 12:03
What is the problem with my query using temp table ? – logan Nov 21 '14 at 12:06
Please help me to find what is the wrong with my query using temp table. I need to understand if there is any problem. Then i will also use inbuilt query you suggested – logan Nov 21 '14 at 12:10
@logan . . . Is there a maximum number of elements in a row? – Gordon Linoff Nov 21 '14 at 12:14
No. It can have `n` number of rows and any kind of `val` – logan Nov 21 '14 at 12:28

2 Answers2