Combine multiple rows into one row

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Combine multiple rows into one row

Author

Topic

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2011-04-07 : 22:08:36

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-07 : 22:17:04

you are not using Microsoft SQL Server right ?

If you are using SQL Server, depending on the version, you can use the UDF or for xml method

KH
[spoiler]Time is always against us[/spoiler]

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2011-04-07 : 22:39:41

I am not using Microsoft SQL Server. I am using Oracle Database 11g.

Which do you recommend? I just need the SQL statement to achieve the above result really. Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-07 : 22:53:29

i don't know. SQLTeam is for SQL Server. Most of us are not familiar with Oracle. There is an oracle forum in dbforums.com. Try there

KH
[spoiler]Time is always against us[/spoiler]

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-04-08 : 09:59:11

Violating First Normal Form (1NF)is a bad idea in any SQL product. Please read a book on RDBMS, so you will know the fundamentals.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2011-04-08 : 13:43:18

quote:
Originally posted by shaoen01

Hi all,

I would like to find out how am i able to combine multiple rows into one row without using cursor if possible. I am currently using SQLPlus and appreciate if someone could guide me.

My current result set:
user id|user name|group name
1|user1|group1
1|user1|group2
2|user2|group1
2|user2|group3

What i wish to achieve:
user id|user name|group name
1|user1|group1,group2
2|user2|group1,group3

Can someone assist please? Your help is greatly appreciated!

If the number of rows where need to concatenate is low, then you can choice a set-based solution like this:


SELECT A.user_id, A.user_name, A.group_name + ',' + B.group_name AS group_name
  FROM TableName AS A
       CROSS JOIN TableName AS B
 WHERE A.group_name < B.group_name
   AND A.user_id = B.user_id;

______________________

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-04-08 : 14:34:25

No, you can not avoid row by row processing for that problem in Oracle. Better return your current set to client and let client make concatenations.

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources