By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,379 Members | 1,380 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,379 IT Pros & Developers. It's quick & easy.

Merge multiple row values to a single row

ak1dnar
Expert 100+
P: 1,584
Please have a look at this sample tables.

Table : users [id | name]

Expand|Select|Wrap|Line Numbers
  1. 1001 | USER-1
  2. 1002 | USER-2
  3. 1003 | USER-3

Table: Schools
[s_id | s_name | userid]

Expand|Select|Wrap|Line Numbers
  1. 1 | School A | 1001
  2. 2 | School B | 1001
  3. 3 | School C | 1002
  4.  
I need to join these table records like in this format.

USER-1 | Shcool A, School B

Is this possible with using a query it self ?
here is the sample scripts to test it.

Expand|Select|Wrap|Line Numbers
  1. create database sample1
  2. GO
  3. use sample1
  4. GO
  5. create table users(
  6. id int primary key IDENTITY (1001,1),
  7. name varchar(50) 
  8. )
  9. GO
  10. insert into users(name)values('USER-1')
  11. insert into users(name)values('USER-2')
  12. insert into users(name)values('USER-3')
  13. GO
  14. create table schools(
  15. s_id int primary key iDENTITY (1,1),
  16. s_name varchar(128),
  17. userid int not null
  18. )
  19. GO
  20. insert into schools(s_name,userid)values('School A','1001')
  21. insert into schools(s_name,userid)values('School B','1001')
  22. insert into schools(s_name,userid)values('School C','1002')
  23. insert into schools(s_name,userid)values('School D','1003')
  24. GO
  25. select * from users
  26. select * from schools
Jun 23 '08 #1
Share this Question
Share on Google+
6 Replies


deepuv04
Expert 100+
P: 227
Hi,
To convert rows into columns you need to use a function which will convert rows into columns.

and the code is :

Expand|Select|Wrap|Line Numbers
  1.  
  2. create function Get_SName
  3. (  @userid int)
  4. returns varchar(max)
  5. as 
  6. begin
  7.     declare @s_name varchar(max)
  8.     set @s_name = ''
  9.     SELECT @s_name = @s_name + S_name + ', ' from schools where userid = 1001
  10.  
  11.     SELECT @s_name = substring(@s_name,1,len(@s_name) - 2)
  12.  
  13.     return(@s_name)
  14.  
  15. end
  16.  
  17.  
  18. select id,name,dbo.Get_sname(id) as School from users
  19.  
  20.  
thanks
Jun 23 '08 #2

ak1dnar
Expert 100+
P: 1,584
Hi,
To convert rows into columns you need to use a function which will convert rows into columns.

and the code is :

Expand|Select|Wrap|Line Numbers
  1.  
  2. create function Get_SName
  3. (  @userid int)
  4. returns varchar(max)
  5. as 
  6. begin
  7.     declare @s_name varchar(max)
  8.     set @s_name = ''
  9.     SELECT @s_name = @s_name + S_name + ', ' from schools where userid = 1001
  10.  
  11.     SELECT @s_name = substring(@s_name,1,len(@s_name) - 2)
  12.  
  13.     return(@s_name)
  14.  
  15. end
  16.  
  17.  
  18. select id,name,dbo.Get_sname(id) as School from users
  19.  
  20.  
thanks

Thanks for the code.

Anyway I tried it after altering some stuffs. but ended up with some erroneous result.
Expand|Select|Wrap|Line Numbers
  1.       create function Get_SName
  2.  
  3.       (  @userid int)
  4.  
  5.       returns varchar(128)
  6.  
  7.       as
  8.  
  9.       begin
  10.  
  11.           declare @s_name varchar(128)
  12.  
  13.           set @s_name = ''
  14.  
  15.           SELECT @s_name = @s_name + S_name + ', ' from schools 
  16.  
  17.           SELECT @s_name = substring(@s_name,1,len(@s_name) - 1)
  18.  
  19.           return(@s_name)
  20.  
  21.       end
  22.  
Once excuted the function using this
Expand|Select|Wrap|Line Numbers
  1.  select id,name,dbo.Get_sname(id) as School from users

It gives me the result as;

1001 USER-1 School A, School B, School C, School D
1002 USER-2 School A, School B, School C, School D
1003 USER-3 School A, School B, School C, School D

But I was expecting something like this.
1001 USER-1 School A, School B
1002 USER-2 School C
1003 USER-3 School D

Any Idea about this?
Jun 23 '08 #3

deepuv04
Expert 100+
P: 227
Thanks for the code.

Anyway I tried it after altering some stuffs. but ended up with some erroneous result.
Expand|Select|Wrap|Line Numbers
  1.       create function Get_SName
  2.  
  3.       (  @userid int)
  4.  
  5.       returns varchar(128)
  6.  
  7.       as
  8.  
  9.       begin
  10.  
  11.           declare @s_name varchar(128)
  12.  
  13.           set @s_name = ''
  14.  
  15.           SELECT @s_name = @s_name + S_name + ', ' from schools 
  16.  
  17.           SELECT @s_name = substring(@s_name,1,len(@s_name) - 1)
  18.  
  19.           return(@s_name)
  20.  
  21.       end
  22.  
Once excuted the function using this
Expand|Select|Wrap|Line Numbers
  1.  select id,name,dbo.Get_sname(id) as School from users

It gives me the result as;

1001 USER-1 School A, School B, School C, School D
1002 USER-2 School A, School B, School C, School D
1003 USER-3 School A, School B, School C, School D

But I was expecting something like this.
1001 USER-1 School A, School B
1002 USER-2 School C
1003 USER-3 School D

Any Idea about this?

Hi,
No need to change the function. By mistake i have given some hard coded value (1001) inside the function, just change the value to @userid then it will work fine.
and the modified code is
Expand|Select|Wrap|Line Numbers
  1. alter function Get_sname
  2. (  @userid int)
  3.  
  4. returns varchar(max)
  5. as
  6. begin
  7.     declare @s_name varchar(max)
  8.     set @s_name = ''
  9.  
  10.     SELECT @s_name = @s_name + S_name + ', ' from schools where userid = @userid
  11.     SELECT @s_name = substring(@s_name,1,len(@s_name) - 2)
  12.     return(@s_name)
  13.  
  14. end
  15.  
  16. select id,name,dbo.Get_sname(id) as School from users
  17.  
  18.  
thanks
Jun 24 '08 #4

ak1dnar
Expert 100+
P: 1,584
It worked for me. Thank you so much for your help.
And one more thing, Could you please explain how this line working.

Expand|Select|Wrap|Line Numbers
  1. SELECT @s_name = substring(@s_name,1,len(@s_name) - 2)
Jun 24 '08 #5

deepuv04
Expert 100+
P: 227
It worked for me. Thank you so much for your help.
And one more thing, Could you please explain how this line working.

Expand|Select|Wrap|Line Numbers
  1. SELECT @s_name = substring(@s_name,1,len(@s_name) - 2)
Hi,

The statement
SELECT @s_name = @s_name + S_name + ', ' from schools where userid = @userid
is used to append the names seperated by a comma (, ). When the above statement is
exectuted, at the end of the string @s_name one extra comma will be added.

To eleminate the extra added comma i used this statement
( taking the substring as from starting position to end position - 2)

SELECT @s_name = substring(@s_name,1,len(@s_name) - 2)

Thanks
Jun 24 '08 #6

ak1dnar
Expert 100+
P: 1,584
Hi deepuv04, I really apreciate your help on this. thanks again. see you around.
Jun 24 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.