Select data in one row from multiply rows from different tables SQL Server

Question

I was searching here for answer, but couldn't find right answer to my question. I have tables:

----------    ----------    ----------
| offers |    |  bids  |    | users  |
----------    ----------    ----------
id            id            id
....          offer_id      first_name
....          user_id       last_name
              ....          .....

Now I need to each offer id assign user last and first name where

offers.id = bids.offers_id and bids.user_id = users.id

Example:

     offers                   bids                   users
------------------    ----------------------     ----------------
|id| ...| ....|       |id| offer_id| user_id|    |id| first_name| last_name|
------------------    -----------------------     --------------------------
|1 | ...|.....|       |1 |    1    |    2   |    | 2| Peter     | Jackson  |
|2 | ...|.....|       |2 |    1    |    3   |    | 3| Adam      | Black    |
                      |3 |    1    |    6   |    | 4| Roy       | Wright   |
                      |4 |    2    |    5   |    | 5| Eva       | Cekovsky |
                      |5 |    2    |    7   |    | 6| Martin    | Tyson    |
                                                 | 7| Vera      | Vornel   |

And the output should be like this

  offer_id                   full_name
--------------------------------------------------------------
|   1      |    Peter Jackson, Adam Black, Martin Tyson      |
--------------------------------------------------------------
|   2      |    Eva Cekowsky, Vera Vornel                    |
--------------------------------------------------------------

I can easily connect last with first name using CONCAT and do it if every data was in same table, but this,I can't figure it out.

score 3 · Answer 1 · answered Jan 23 '18 at 18:27

3

For MS SQL Server 2017, you can use the STRING_AGG function:

SELECT
    b.offer_id,
    STRING_AGG(CONCAT(u.first_name,' ',u.last_name), ', ') AS full_name
FROM bids b
JOIN users u on b.user_id = u.id
GROUP BY b.offer_id

You can force the comma-separated names to be in order, for example:

SELECT
    b.offer_id,
    STRING_AGG (CONCAT(u.first_name,' ',u.last_name), ', ')
        WITHIN GROUP (ORDER BY u.last_name, u.first_name ASC)
        AS full_name
FROM bids b
JOIN users u on b.user_id = u.id
GROUP BY b.offer_id

answered Jan 23 '18 at 18:27

Mark Maurice Williams

217
2
5

Always good to make use of the new functionality when you can. :) – Thom A Jan 23 '18 at 18:31
Thank you, on friends comp it worked perfectly,as he has SQL 2017, I got 2016, so only "no STRING_AGG" ..Is there something similar to STRING_AGG for earlier ver. of SQL Server? – widmopl Jan 24 '18 at 08:02
As far as I know, there's no simple drop-in substitute for STRING_AGG() in older versions of SQL Server. You can take the approach suggested here: https://stackoverflow.com/questions/13639262/optimal-way-to-concatenate-aggregate-strings – Mark Maurice Williams Jan 24 '18 at 17:26

score 1 · Answer 2 · answered Jan 23 '18 at 18:22

1

This should be what you're after. It uses FOR XML PATH and STUFF to create a delimited string:

CREATE TABLE #offer (id int);
INSERT INTO #offer
VALUES (1),(2);

CREATE TABLE #bid (id int,
                   offer_id int,
                   user_id int);
INSERT INTO #bid
VALUES (1,1,2),
       (2,1,3),
       (3,1,6),
       (4,2,5),
       (5,2,7);

CREATE TABLE #user (id int,
                    first_name varchar(10),
                    last_name varchar(10));
INSERT INTO #user
VALUES (2,'Peter','Jackson'),
       (3,'Adam','Black'),
       (4,'Roy','Wright'),
       (5,'Eva','Cekovsky'),
       (6,'Martin','Tyson'),
       (7,'Vera','Vornel');
GO
SELECT o.id AS offer_id,
       STUFF((SELECT ', ' + first_name + ' ' + last_name
              FROM #bid b
                   JOIN #user u ON b.user_id = u.id
              WHERE b.offer_id = o.id
              FOR XML PATH('')),1,2,'') AS Full_name
FROM #offer o;

GO
DROP TABLE #offer;
DROP TABLE #bid;
DROP TABLE #user;

Please comment if you have questions.

answered Jan 23 '18 at 18:22

Thom A

88,727
11
45
75

Thanks a lot, but here I got only response about syntax error near '' SELECT o.id AS offer_id, STUFF((SELECT ', ' + first_name + ' ' + last_name ''. And couldnt find why this error occured. – widmopl Jan 24 '18 at 08:16
@widmopl The SQL, on it's own, that I've provided runs fine. I'll need to know what it is you've changed to be able to know why it isn't working. *"Error Near..."* isn't going to tell me a lot I'm afraid. – Thom A Jan 24 '18 at 08:39
Sorry,I'm speechless.....Have to use this on 2 servers, and checked this on second, with MySQL, not MSSQL .. Of course your code above works fine , my stupid mistake. If I now want to change this query to my SQL, I have to use GROUP_CONCAT,right? – widmopl Jan 24 '18 at 08:56
@widmopl Honestly, not sure. I haven't used MySQL in about 12 years. If you need this is MySQL as well, you might need to either post again, or edit your post to tag MySQL and add that to your question. – Thom A Jan 24 '18 at 08:59
Ok,I'll try maybe new post with other tag. Thanks for answer once more, saved my day. – widmopl Jan 24 '18 at 09:07

Select data in one row from multiply rows from different tables SQL Server

2 Answers2