Combine Multiple Columns And Records Into Comma Separated One Column In MS SQL Server. In this example i am going to describe how to combine multiple columns and records in one column in MS SQL.
Here is the scenario
I have a table having Employees names and their respective Department names,
now i want to show Employees names separated by comma into one column and respective Department name in another column.
My table schema is shown in the image below
And this is Data into table
I want output in following format
Department FirstName
IT amiT,Emp1,Emp5
Admin Shobhit, Emp3,Emp7
and so on
To get this desired result we need to write below mentioned query
And the output of this SQL Query would be
Hope this helps
Here is the scenario
I have a table having Employees names and their respective Department names,
now i want to show Employees names separated by comma into one column and respective Department name in another column.
My table schema is shown in the image below
And this is Data into table
I want output in following format
Department FirstName
IT amiT,Emp1,Emp5
Admin Shobhit, Emp3,Emp7
and so on
To get this desired result we need to write below mentioned query
SELECT DISTINCT
Department,
EmpNames = substring( ( SELECT ', ' + FirstName
FROM Employees e2
WHERE e2.Department = e1.Department FOR XML path(''), elements
),2,500)
FROM Employees e1
And the output of this SQL Query would be
Hope this helps
If you like this post than join us or share
20 comments:
It help me. Thanks.
I've changed "substring((SELECT ...), 2,500)" to "stuff((SELECT ...), 1, 1, '')" so it's not tied to the varchar length.
substring((SELECT ...), 2,500)" to "stuff((SELECT ...), 1, 1, '')"
why not work?
please read this
Syntax:
SUBSTRING ( value_expression ,start_expression , length_expression )
your length_expression = 1
Good post. It helped me to create comma field. I put sql for above code sample:
create table emp(id int,fname varchar(50),dept varchar(50))
insert into emp values(1,'A','IT');
insert into emp values(2,'B','IT');
insert into emp values(3,'C','Sale');
insert into emp values(4,'D','Sale')
SELECT DISTINCT
Dept, EmpNames = substring( ( SELECT ', ' + Fname
FROM Emp e2
WHERE e2.Dept = e1.Dept FOR XML path(''), elements
),2,500)
FROM Emp e1
Regards,
Nilesh
I Could not get the correct Result
SELECT DISTINCT
Membercode, EmpNames = substring( ( SELECT ', ' + Childname
FROM Memberchildren e2
WHERE e2.Childname = e1.Childname FOR XML path(''), elements
),1,500)
FROM Memberchildren e1 where e1.Membercode='T005'
T005 , NEWCHILDSSSS
T005 , NEWTEST234RRRRR
T005 , NEWTESTTTTTTTTTT
T005 , TEEST
T005 , TESTSSSSSSSS
I could kiss you!
I could kiss you! Thanks!
Awesome! Exactly what I needed. Thanks for posting
Awesome! Exactly what I needed. Thanks for posting.
thanks very much for this post. It was very help full. I was facing the similar problem.
Thanks again
Sql Query For Multiple Row To Single Column
http://www.bdlab.byethost32.com/index.php/sql-server/97-sql-query-for-multiple-row-to-single-column
There is also a possibility to fix this on SQL2000 or older versions (which do not have the FOR XML PATH option).
You can do this by joining the table as much times as you have multiple rows.
An example:
CREATE table #temp (col1 nvarchar(10), col2 nvarchar(1))
INSERT INTO #temp values('ComputerA', 'C')
INSERT INTO #temp values('ComputerA', 'D')
INSERT INTO #temp values('ComputerA', 'E')
INSERT INTO #temp values('ComputerB', 'C')
INSERT INTO #temp values('ComputerC', 'C')
INSERT INTO #temp values('ComputerC', 'D')
SELECT x1.col1
, REPLACE(ISNULL(MIN(x1.col2), ' ') + ', '
+ ISNULL(MIN(x2.col2), ' ') + ', '
+ ISNULL(MIN(x3.col2), ' ')
, ', ', '') AS driveletters
FROM #temp x1
LEFT JOIN #temp x2
ON x1.col1 = x2.col1
AND x2.col2 > x1.col2
LEFT JOIN #temp x3
ON x1.col1 = x3.col1
AND x3.col2 > x2.col2
GROUP BY x1.col1
DROP TABLE #temp
In this example, if you have a maximum of 26 driveletters, you have to do 25 left joins.
Hope this helps.
Regards,
Peter Elzinga
Thanks Man! That's what I needed! ;o)
RGDS,
-- cs
tried this and I'm having trouble in asp with how to form the response.write
SELECT DISTINCT
COMPANYID, Result = substring( ( SELECT ', ' + STATUS FROM CALLLOGS e2 WHERE e2.COMPANYID = e1.COMPANYID FOR XML path(''), elements),1,500)
FROM CALLLOGS e1
This gives me a error. Can some one help me in solving this problem.
@Above: What error you are getting ? , query seems fine
hi
This is a neat applet; however, I'd like to see this done in SQL Server 2000. Any ideas?
Fracking amazing, switched from a clunky coalesce function, to this, WOW!
Really thanks, appreciating
Awesome ....
Post a Comment