Merging Multiple columns data to a single row in SQL [duplicate]

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
– Taryn, Commented Jul 19, 2013 at 16:14

tilley31 · Accepted Answer · 2013-07-19 16:12:46Z

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, 2013 at 16:12

tilley31

68814 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nishant Over a year ago

hey everyone i want a separate column. Reg_type1,Reg_type2,Reg_type3.... are all separate columns.

Bob Jarvis - Слава Україні · Accepted Answer · 2013-07-19 21:39:49Z

1

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.

answered Jul 19, 2013 at 21:39

Bob Jarvis - Слава Україні

50.2k10 gold badges81 silver badges119 bronze badges

Comments

mti2935 · Accepted Answer · 2013-07-19 16:03:12Z

-1

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

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

answered Jul 19, 2013 at 16:03

mti2935

12.1k4 gold badges33 silver badges34 bronze badges

1 Comment

FUD Over a year ago

there is no Reg_type<n> column

Collectives™ on Stack Overflow

Merging Multiple columns data to a single row in SQL [duplicate]

3 Answers 3

1 Comment

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Linked

Related