Concatenate values from SQL and bind to Datatable

Question

Hope you can help...

I have two SQL tables, one has users and the other has user locations that I need to combine into a C# datatable.

The basic query to join them isn't the issue, the problem is that a user may appear in the locations table more than once. In this case I need to pass each location to one row rather than list the user multiple times with multiple locations.

It is part of an ADO.NET proximity search so the current query stands as...

                DECLARE @lat float
                DECLARE @lon float
                SET @lat = @varLat
                SET @lon = @varLon
                DECLARE @point geography = geography::Point(@lat, @lon, 4326);
                DECLARE @distance float
                SET @distance = @varDistance
                SELECT t1.locationId, t1.city, t2.[firstName]+' '+[lastName] as name
                FROM _Locations t1 INNER JOIN _Users t2 ON t2.userId = t1.userId
                WHERE @point.STDistance(centerPoint) <= @distance

I feel like LINQ may help but it's not something I have used before.

http://stackoverflow.com/questions/6170419/what-is-the-equivalent-of-string-join-on-tsql# — Tim Schmelter, Apr 27 '15 at 11:37
Thanks Tim but if i've understood it correctly, that would only work if I specified the Id to concatenate the values on. I need it to do it whenever the id is repeated. — Tinyy Jed, Apr 27 '15 at 11:58

Eldar · Accepted Answer · 2015-04-28T09:11:38.963

2

There is really not enough information to give you correct answer. It could be done either in sql or in code. That is the T-SQL way.

SELECT u.ID, u.Name, 
stuff(
(select ', ' + ul.ID from UserLocations where ul.UserID = u.ID for xml path('')), 
1, 2, null) locs 
    FROM Users u

UPD:

SELECT t2.[firstName]+' '+[lastName] as name,
       STUFF(
            (SELECT t1.locationId, t1.city FROM _Locations t1 
              WHERE t1.userId = t2.userId 
               AND @point.STDistance(centerPoint) <= @distance FOR XML PATH('')), 
            1, 2, NULL) locs
FROM _Users t2

edited Apr 28 '15 at 09:11

answered Apr 27 '15 at 12:19

Eldar

104
6

Thanks Eldar, happy to provide more information, never posted a question before so wasn't sure what you needed. – Tinyy Jed Apr 27 '15 at 12:26
What do you utilizing to work with your database: ADO.NET, LINQtoSQL, EF or something? Could you provide that piece of code so I can suggest you "in code" solutions. – Eldar Apr 28 '15 at 05:23
I have edited original question to include it now. It's part of an ADO.NET proximity search. – Tinyy Jed Apr 28 '15 at 07:42
That worked great, never knew that method existed. Thanks Eldar! :) – Tinyy Jed Apr 28 '15 at 09:53

score 0 · Answer 2 · answered Apr 28 '15 at 10:11

Final working query is...

 SELECT t2.[firstName]+' '+[lastName] as name,
        STUFF(
                (
                  SELECT ', ' + t1.city FROM _Locations t1 
                  WHERE t1.userId = t2.userId
                  AND @point.STDistance(centerPoint) <= @distance FOR XML PATH(''), type
                 ).value('.', 'varchar(max)'), 1, 1, ''
              )
          FROM _Users t2

Concatenate values from SQL and bind to Datatable

2 Answers2