SQL SERVER Combine multiple rows with multiple columns into single row with single column

Question

Ok guys this has got me rolling my eyes for a few days now. Let's say i have two tables (or more) like below.

TABLE1

ID  | NAME      | DATE          | MORE COLS....
1   | MARK      | 2018-31-10    | ....
2   | JOHN      | 2018-29-10    | ....
...

TABLE2

FK_TBL1_ID | Data1     | Data2     | 
1          | 11111     | 33333     | 
1          | 22222     | 44444     | 
2          | 12345     | 67899     | 
...

And i would like to combine multiple columns of each row with same id of these tables into A SINGLE ROW and a SINGLE COLUMN like below. Each new "row" will be divided from the previous with ';'.

QUERY RESULT

ID  | NAME      | DATE          |       Data1Data2        | 
1   | MARK      | 2018-31-10    |  1111 3333; 2222 4444;  | 
2   | JOHN      | 2018-29-10    |  12345 67899            |

How would you go about doing that? i found examples on FOR XML PATH but only when there are multiple rows with two columns and combined on different rows.

I've removed the conflicting tags for you. Please don't tag irrelevant RDBMS to your question, it can actually make things more confusing for those trying to answer, or delay you getting one (as the volunteers don't know what Syntax would be valid for the RDBMS you are actually using). — Thom A
– Thom A ♦, Commented Jun 25, 2018 at 11:24

Gordon Linoff · Accepted Answer · 2018-06-25 12:05:13Z

3

Here is an example with your data:

select t1.*,
       stuff( (select '; ' + coalesce(data1, '') + ' ' + coalesce(data2, '')
               from table2 t2
               where t2.FK_TBL1_ID = t1.id
               for xml path ('')
              ), 1, 2, ''
            ) as Data1Data2
from table1 t1;

edited Jun 25, 2018 at 12:05

answered Jun 25, 2018 at 11:00

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Alex Over a year ago

Wow that was quick, thanks Gordon! I edited the question a bit, made a mistake on result set. Does that change things? Also could you please explain why is it "1, 2" instead of "1,1" as STUFF parameters?

Alex Over a year ago

Also i just noticed that if there is a NULL value in one of the columns of a row then nothing appears on the result set. Why is that and how do i avoid it?

SqlKindaGuy Over a year ago

@JasonA Write ISNULL(data1,'') and ISNULL(data2,'') then it should fix if any value is null and then put LTRIM around stuff LTRIM(STUFF((code

Collectives™ on Stack Overflow

SQL SERVER Combine multiple rows with multiple columns into single row with single column

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related