Combine multiple rows into one row with outer join sql

Question

I have the following data, that I want to display only 3 rows ( 1 row per Table1Id from table 1), but some ids have multiple entries in table2, which creates a new row when displaying data, but I want to combine table 2s data into a single row from table 1.

Table 1                                      Table 2
Table1Id     Name     State                  table2Id   Table1Id   City
  1          Bart     Idaho                     1          1       Boise
  2          Jenny    Utah                      2          2       Salt Lake
  3          Jill     Arizona                   3          3       Phoenix
                                                4          3       Yuma

Select * From Table1 FULL OUTER JOIN Table2 ON Table1.TableId = Table2.Table1Id

So far, it displays 4 rows like this:
Name      State     City
1.Bart    Idaho     Boise
2. Jenny   Utah      Salt Lake
3. Jill    Arizona   Phoenix
4. Jill    Arixona   Yuma

I want it to display 3 rows like this (with my gridview of course)
Name      State      City
1.Bart    Idaho      Boise
2. Jenny   Utah      Salt Lake
3. Jill    Arizona   Phoenix  Yuma

score 0 · Accepted Answer · edited May 23 '17 at 11:57

0

Similar to this question, you can use STUFF and XML PATH with a GROUP BY...

SELECT 
  [ID],
  [NAME],
  [STATE],
  STUFF((
    SELECT ', ' + [City] + ':' + CAST([Value] AS VARCHAR(MAX)) 
    FROM #YourTable 
    WHERE (ID = Results.ID) 
    FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
  ,1,2,'') AS NameValues
FROM #YourTable Results
GROUP BY ID

Since this is more of a display issue, it's usually better to do it on the front-end as opposed to the back.

edited May 23 '17 at 11:57

Community

1
1

answered Apr 03 '14 at 16:42

Eric Hotinger

8,957
5
36
43

so no way to do it with the join similar to how I have it? Reason is my select is way more complex and has multiple joins and a pivot, but was trying to make this hypothetical query to fit the way I'm joining that table. – mlg74 Apr 03 '14 at 16:52
If you want to basically do a `GROUP BY` on that end table with string concatenation into a single column, this is the simplest approach I've seen. – Eric Hotinger Apr 03 '14 at 16:57
i will accept your answer because it does answer the questions and will put a more complex question here https://stackoverflow.com/questions/22844744/combine-multiple-rows-of-joined-table-into-1-row – mlg74 Apr 03 '14 at 17:24

Combine multiple rows into one row with outer join sql

1 Answers1