0

I am facing an issue to convert two different columns into two comma separated lists. I am having two columns with Name Bachelor and Masters which are holding different digress. Now I need to convert both of them into comma separate lists

create table #user (Bachelor varchar(25),Masters varchar(25))

insert into #user (Bachelor,Masters) values ('B.A','M.A')
insert into #user (Bachelor,Masters) values ('B.E','MCA')
insert into #user (Bachelor,Masters) values ('B.Tech','M.Tech')

I need two columns which are having comma separate values as below. 

Bachelor         Master

B.A,B.E,B.Tech   M.A,MCA,M.Tech

Your help is appreciated.

Harsh Gupta
  • 307
  • 2
  • 14
  • Why `mysql` tag if you are searchig sor an SQL-server solution? – Jens Jul 30 '16 at 19:14
  • Thanks Jens to point out this thing. You are right I need the solution in Sql server. – Harsh Gupta Jul 30 '16 at 19:17
  • 1
    Possible duplicate of [How to make a query with group\_concat in sql server](http://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server) – Jens Jul 30 '16 at 19:18
  • In this post Stuff function is used and do we need to use the Stuff function for both columns differently or is there any-other best way to do this? – Harsh Gupta Jul 30 '16 at 19:23
  • @HarshGupta - your last comment should go under the posted answer that used the `Stuff()` function not this general comments area. Poster will be alerted in doing so. – Parfait Jul 30 '16 at 22:05
  • Jen, the users assign a tag in a manner consonant with their grasp of the problem space in general and the problem in particular. That should set the stage and the tone for any attendant subsequent help. – Bruce David Wilner Jul 31 '16 at 00:56

1 Answers1

1
SELECT
  STUFF((
    SELECT
      ',' + Bachelor
    FROM #user
    FOR XML PATH('')
  ), 1, 1, '') AS Bachelor,
  STUFF((
    SELECT
      ',' + Masters
    FROM #user
    FOR XML PATH('')
  ), 1, 1, '') AS Masters
Dmitry Bargatin
  • 32
  • 4