0

I am using SQL Server 2014, and I have two tables:

number        id
------------------
36-23         1
36-23         2

id         value
------------------
1          asia   
2          europe

The number column is of type varchar. I want to write a query to return the following results:

number       Name   
---------------------------
36-23        asia,europe

I am wondering how can I do this with the help of query or functions in SQL Server.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mithun Adhikari
  • 1
  • 1
    https://stackoverflow.com/questions/13639262/optimal-way-to-concatenate-aggregate-strings – AaronHolland Apr 30 '18 at 03:56
  • [How to concatenate text from multiple rows into a single text string in SQL server? ](https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv) hope this may helpful to you. – Sirisha Apr 30 '18 at 04:09

1 Answers1

0

I think using STUFF is the easiest way to go forward here.

CREATE TABLE tableID
    ([number] varchar(50), [id] int)
;

INSERT INTO tableID
    ([number], [id])
VALUES
    ('36-23', 1),
    ('36-23', 2)
;

CREATE TABLE tableLoc
    ([id] int, [value] varchar(50))
;

INSERT INTO tableLoc
    ([id], [value])
VALUES
    (1, 'asia'),
    (2, 'europe')
;

SELECT tableID.number, tableLoc.value INTO temp1
  FROM tableID INNER JOIN tableLoc ON tableID.id = tableLoc.id;

SELECT *, STUFF((
            SELECT DISTINCT ', ' + value
            FROM temp1
            WHERE number = t.number
            FOR XML PATH('')), 1, 2, '')
FROM (
    SELECT DISTINCT number
    FROM temp1
) t
Moffen
  • 1,817
  • 1
  • 14
  • 34