MS SQL Query to concatenate the  column values

Rajeshbala
Rajeshbala used Ask the Experts™
on
Hi,
   I am new to MS SQL. I need a help with a query (might be simple). I have a table named "Student" with std_id and course as columns

Std_id      course
0123         Cell
0123         Bio
0123         Stat
1123         Mis
1123         Bio
I want to list the courses of each student as a single column value ( separated by comma)..i mean the result should be as below

Std_id    Course
0123     Cell, Bio,Stat
1123      Mis, Bio


Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
This should do:

SELECT DISTINCT t1.Std_id,
substring(
(
    SELECT  ', ' + course
    FROM  ur_table t2
    where t1.Std_id = t2.Std_id
    FOR XML PATH('')
) , 3, 1000) AS course
FROM ur_table t1
SharathData Engineer

Commented:
check this also.
select distinct Std_Id,
       stuff(( select distinct ','+convert(varchar,course)
                from  Student as t2 where t1.Std_ID = t2.Std_ID
               order by  ','+convert(varchar,course)
                 for xml path('')), 1, 1, '') 
  from Student t1

Open in new window

11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

SELECT
   t1.Std_id ,
   MemberList = substring((SELECT ( ', ' + course )
                           FROM student t2
                           WHERE t1.Std_id  = t2.Std_id
                           ORDER BY
                             Std_id ,
                              course
                           FOR XML PATH( '' )
                          ), 3, 1000 )FROM Student t1
GROUP BY Std_id

Commented:
create below function ..(replace ur table and table name and id ..plz check)

use this function once  you create it..
then use like this

select distinct id,[dbo].[Concat_name] (id) from  [dbo].[tbSample]
group by id ,name


replace your table name in [dbo].[tbSample] ,columns id,name
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE  function [dbo].[Concat_name] ( @nid int)
returns  varchar(3232)
Begin
declare @result varchar(3232)
declare @result1 varchar(3232)
declare @vCount int
declare @vIndex int
declare @vDelimeter char(3)
declare @nid1 int

set @vCount= (select count(isnull([dbo].[tbSample].[id],0))
 from [dbo].[tbSample] where isnull([dbo].[tbSample].[id],0)= @nid)
set @vDelimeter=','
set @vIndex=1
set @result= ''
set @result1= ''
while @vIndex <=  @vCount

begin
 
set @result1 =
( select e.[Name]
from(select row_number()over (order by [dbo].[tbSample].[name] desc)as rowid, [name]
 from [dbo].[tbSample] where isnull([dbo].[tbSample].[id],0)= @nid) e
where e.rowid = @vIndex
)
if @vIndex<>1
begin
set @result =  @result+','+@result1
end
else
begin
set @result =  @result1
end
set @vIndex = @vIndex+1

end

return @result

end

Open in new window

Commented:
changes for above function with your desired table (i avoided the cursors as it kills time) :

line 16 :replace your column id

line 17 : replace your table

line 27 : don't replace "e" -that is table alias name ..you can replace name with your coulmn name

line 28 : replace your column name

line 29 : replace your table name

that's it.. create function and use select query from code snippet
in the code ,replace your cloumn names and tables
select distinct id,[dbo].[Concat_name] (id) from  [dbo].[tbSample]
group by id ,name

Open in new window

SharathData Engineer

Commented:
Did you try my post? What's wrong with that?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial