CODEWITHSUNDEEP
sql
Gautam
Gautam

Reputation: 1740

SQL get multiple values of columns in one row

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

Upvotes: 0

Views: 2553

Answers (1)

Dinup Kandel
Dinup Kandel

Reputation: 2505

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

Upvotes: 1

Related Questions