SQL get multiple values of columns in one row

Question

I am using MS Sql server 2008 R2. I have a query that gives me output like this

Col1....Col2
CV1.....AV1
CV1.....AV2
CV2.....AV3
CV2.....AV4

The query is

select Tab1.Col1, Tab2.Col2 
from Table1 Tab1 
JOIN Table2 Tab2 on Tab1.PKID = Tab2.FKID

What I want is one row for each distinct values in Col1 and in Col2 all the values related to col1 with comma or pipeline delimiter

Col1....Col2
CV1.....AV1,AV2
CV2.....AV3,AV4

Can anyone help me on this?

Basically I need something like group_concat that is available in My sql

http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005 — ntalbs, Jun 19 '13 at 09:59
What is your query? If you don't post your query we won't know how to help you and tell you what you're doing wrong. — Radu Gheorghiu, Jun 19 '13 at 10:00
AFAIK there is no string concatenation aggregate function in standard sql. There are workarounds for different database engines. It would help if you will specify your database engine or just search for `string concatenation %DATABASE_VENDOR_NAME%`. For example: [PostgreSQL](http://stackoverflow.com/q/43870/451518), [MSSQL](http://stackoverflow.com/q/273238/451518), [Oracle](http://stackoverflow.com/q/468990/451518) — default locale, Jun 19 '13 at 10:01
http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string — Darshan Mehta, Jun 19 '13 at 10:26
possible duplicate of [Can I concatenate multiple MySQL rows into one field?](http://stackoverflow.com/questions/276927/can-i-concatenate-multiple-mysql-rows-into-one-field) — default locale, Jun 19 '13 at 11:03
http://stackoverflow.com/questions/8868604/sql-group-concat-function-in-sql-server — Praveen Prasannan, Jun 20 '13 at 04:25

Dinup Kandel · Answer 1 · 2013-06-20T04:09:03.830

CREATE TABLE a(
  Col1 varchar(50),
  Col2 varchar(20));   

INSERT INTO a (Col1,Col2) values ('CV1','AV1');
INSERT INTO a (Col1,Col2) values ('CV1','AV2');
INSERT INTO a (Col1,Col2) values ('CV2','AV3');
INSERT INTO a (Col1,Col2) values ('CV2','AV4');   

 with t as (SELECT Col1,(CAST(Col2 AS nvarchar (12))) as col2 from a )
    Select distinct T2.Col1,
                   substring((Select ','  +   T1.col2  AS [text()]
                    From t T1
                    Where T1.Col1 = T2.Col1
                    ORDER BY T1.Col1
                    For XML PATH ('')),2, 100) [col2]
             From t T2

Try this query. I am doing it in sql server. check at sqlfidddle

http://sqlfiddle.com/#!3/7ab28/1

SQL get multiple values of columns in one row

1 Answers1