Merging Multiple columns data to a single row in SQL

Question

I have a table which has data like as following

Name |Reg_Type

aaa   reg_type1

aaa   reg_type2

bbb   reg_type3

aaa   reg_type5

bbb   reg_type4

bbb   reg_type1

However, I want a sql for returning result set like:-

Name| Reg_type1|  Reg_type2  |Reg_type3  |Reg_type4  |Reg_type5  |Reg_type6

aaa      Y          Y            N           N          Y            N
bbb      Y          N            Y           Y          N            N

Do you want separate columns or do you want the values concatenated? — Taryn, Jul 19 '13 at 16:14

score 2 · Answer 1 · answered Jul 19 '13 at 16:12

2

The listagg function is what you need. You can learn about it here:

http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php

SELECT name, LISTAGG(REG_TYPE,'|') WITHIN GROUP (ORDER BY reg_type) AS REG
FROM REG
GROUP BY NAME;

answered Jul 19 '13 at 16:12

tilley31

668
13
19

hey everyone i want a separate column. Reg_type1,Reg_type2,Reg_type3.... are all separate columns. – Nishant Jul 19 '13 at 17:32

score 1 · Answer 2 · answered Jul 19 '13 at 21:39

Try a pivot query:

select * from mytable
  pivot (count(reg_type)
           for reg_type in ('reg_type1',
                            'reg_type2',
                            'reg_type3',
                            'reg_type4',
                            'reg_type5'));

which gives results:

NAME    'REG_TYPE1'  'REG_TYPE2'  'REG_TYPE3'  'REG_TYPE4'  'REG_TYPE5'
aaa      2            1            0            0            1
bbb      0            0            1            1            0

SQLFiddle here.

Share and enjoy.

score -1 · Answer 3 · answered Jul 19 '13 at 16:03

-1

In Oracle, the concatenation operator is ||. So, it would be

Select Name || '|' || Reg_type1 || '|' || Reg_type2 ..... from tablename .....

answered Jul 19 '13 at 16:03

mti2935

11,465
3
29
33

there is no Reg_type column – FUD Jul 19 '13 at 16:03

Merging Multiple columns data to a single row in SQL

3 Answers3