sql query to join and concat values

Question

sql server: 2012

These are 3 tables.

First table is the user's (consultants) table The second one is the prices table, each user from the first column has a price The last table define in what area (city) the consultants offer services

Consultants      Prices           Area
uid | Name       uid | Price      uid | City
----|-----      -----|-------     ----|------
1   | Dave        1  |  5           1 | NY
2   | Adrian      2  |  7           1 | LA
3   | John        4  |  5           4 | NY
4   | Lee                           4 | NO
                                    4 | LA 
                                    4 | SF

I need to select from all the table something like this:

uid  |  Price   | City
-----|----------|-------
 1   |    5     | NY, LA
-----|----------|-------
 2   |    7     | 
-----|----------|-------
 4   |    5     | NY, NO, LA, SF
-----|----------|-------

How do I do this select? Any idea?

You can use `FOR XML PATH`. Have a look [here](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-sql-server) for example. — Giorgos Betsos, Jun 20 '15 at 21:56
It is helpful to tag database questions with both the appropriate software (MySQL, Oracle, DB2, ...) and version, e.g. `sql-server-2014`. Differences in syntax and features often affect the answers. — HABO, Jun 20 '15 at 23:27

score 1 · Accepted Answer · answered Jun 20 '15 at 23:28

1

As you don't specified Database, Assuming SQL Server 2008, You can Use STUFF function and Group BY Clause to concanate multiple rows into Single Comma separated Values

Select C.uid, P.price, ISNULL(A.City,'') City
From Consultants C
     INNER JOIN Prices P ON C.uid = P.uid
     LEFT JOIN (
                 SELECT  UID
                         ,STUFF((SELECT ', ' + CAST(city AS VARCHAR(10)) [text()]
                         FROM Area
                         WHERE UID = t.UID
                         FOR XML PATH(''), TYPE)
                         .value('.','NVARCHAR(MAX)'),1,2,' ') CITY
                  FROM Area t
                  GROUP BY UID ) A 
      ON C.uid = A.uid

Working SQL Fiddle

answered Jun 20 '15 at 23:28

HaveNoDisplayName

8,291
106
37
47

Thank you! You just saved me! – Zelter Ady Jun 21 '15 at 00:20
1

what is your Database, Sql Server or My SQL – HaveNoDisplayName Jun 21 '15 at 00:49
My satabase is SqlServer. Your solution works perfect! Thank you again! – Zelter Ady Jun 21 '15 at 00:52

score 1 · Answer 2 · answered Jun 21 '15 at 03:02

may be this way also you can do

declare @consultants table (uid int,name varchar(10))
insert into @consultants (uid,name)values (1,'dave'),(2,'adrian'),(3,'jon'),(4,'lee')
declare @Prices table (uid int,price int)
insert into @Prices(uid,price)values (1,5),(2,7),(4,5)

declare @Area table (uid int,city varchar(5))
insert into @Area(uid,city)values (1,'NY'),(1,'LA'),(4,'NY'),(4,'LA'),(4,'SF'),(4,'NO')

select t.uid,tt.price,ISNULL(STUFF((select  +', '+ city from @Area where uid = t.uid FOR XML PATH(''), TYPE)
                         .value('.','NVARCHAR(MAX)'),1,2,' '),'')CITY  from @consultants t INNER JOIN @Prices tt
ON t.uid = tt.uid

sql query to join and concat values

2 Answers2