Merge multiple row values to a single row

[ak1dnar]

Please have a look at this sample tables.

Table : users [id | name]

1001 | USER-1
1002 | USER-2
1003 | USER-3

Table: Schools [s_id | s_name | userid]

[CODE=sql]
1 | School A | 1001
2 | School B | 1001
3 | School C | 1002
[/CODE]

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.

create database sample1
GO
use sample1
GO
create table users(
id int primary key IDENTITY (1001,1),
name varchar(50) 
)
GO
insert into users(name)values('USER-1')
insert into users(name)values('USER-2')
insert into users(name)values('USER-3')
GO
create table schools(
s_id int primary key iDENTITY (1,1),
s_name varchar(128),
userid int not null
)
GO
insert into schools(s_name,userid)values('School A','1001')
insert into schools(s_name,userid)values('School B','1001')
insert into schools(s_name,userid)values('School C','1002')
insert into schools(s_name,userid)values('School D','1003')
GO
select * from users
select * from schools

[deepuv04]

Hi,
To convert rows into columns you need to use a function which will convert rows into columns.

and the code is :

[code= sql]

create function Get_SName
( @userid int)
returns varchar(max)
as
begin
declare @s_name varchar(max)
set @s_name = ''
SELECT @s_name = @s_name + S_name + ', ' from schools where userid = 1001

SELECT @s_name = substring(@s_na me,1,len(@s_nam e) - 2)

return(@s_name)

end


select id,name,dbo.Get _sname(id) as School from users

[/code]

thanks

[ak1dnar]

Hi,
To convert rows into columns you need to use a function which will convert rows into columns.

and the code is :

[code= sql]

create function Get_SName
( @userid int)
returns varchar(max)
as
begin
declare @s_name varchar(max)
set @s_name = ''
SELECT @s_name = @s_name + S_name + ', ' from schools where userid = 1001

SELECT @s_name = substring(@s_na me,1,len(@s_nam e) - 2)

return(@s_name)

end


select id,name,dbo.Get _sname(id) as School from users

[/code]

thanks

Thanks for the code.

Anyway I tried it after altering some stuffs. but ended up with some erroneous result.

	  create function Get_SName

	  (  @userid int)

	  returns varchar(128)

	  as

	  begin

		  declare @s_name varchar(128)

		  set @s_name = ''
 
		  SELECT @s_name = @s_name + S_name + ', ' from schools 

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

		  return(@s_name)

	  end

Once excuted the function using this

 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?

[deepuv04]

Thanks for the code.

Anyway I tried it after altering some stuffs. but ended up with some erroneous result.

	  create function Get_SName

	  (  @userid int)

	  returns varchar(128)

	  as

	  begin

		  declare @s_name varchar(128)

		  set @s_name = ''
 
		  SELECT @s_name = @s_name + S_name + ', ' from schools 

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

		  return(@s_name)

	  end

Once excuted the function using this

 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
[code=sql]
alter function Get_sname
( @userid int)

returns varchar(max)
as
begin
declare @s_name varchar(max)
set @s_name = ''

SELECT @s_name = @s_name + S_name + ', ' from schools where userid = @userid
SELECT @s_name = substring(@s_na me,1,len(@s_nam e) - 2)
return(@s_name)

end

select id,name,dbo.Get _sname(id) as School from users

[/code]

thanks

[ak1dnar]

It worked for me. Thank you so much for your help.
And one more thing, Could you please explain how this line working.

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

[deepuv04]

It worked for me. Thank you so much for your help.
And one more thing, Could you please explain how this line working.

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_na me,1,len(@s_nam e) - 2)

Thanks

[ak1dnar]

Hi deepuv04, I really apreciate your help on this. thanks again. see you around.