How do I combine multiple SQL rows into one in a select query?

Question

Let's say I have data like so:

PersonID    Goal    
--------    ----    
1           one     
1           two 
1           three
2           alpha   
2           beta        

PersonID    Note
--------    ----
1           hello
1           world
2           i
2           like
2           squirrels

And I want to put all the goals and notes for each person into a single row:

PersonID    Goal 1  Goal 2  Goal 3  Note 1  Note 2  Note 3
--------    ------  ------  ------  ------  ------  ------
1           one     two     three   hello   world
2           alpha   beta            i       like    squirrels

With the maximum goal and note numbers computed to be equal to the maximum count of goals and notes belonging to any one person - so if I add a third person who has five goals then two more goal columns will be added, for instance.

Is there any way to write a SQL query that combines the rows in this fashion? Or do I have to do that sort of data manipulation in the application itself (as I am currently), which is slower?

edit: I'm using SQL Server and it would be OK to instead have a delimited list, e.g.

PersonID    Goals           Notes
--------    ------          ------ 
1           one|two|three   hello|world
2           alpha|beta      i|like|squirrels

The only way to do an unknown number of columns is to use dynamic SQL. The specific implementation of that will depend on the database platform you're actually using. — Eric Brandt
– Eric Brandt, Commented Jun 1, 2020 at 18:10

Griffin · Accepted Answer · 2020-06-01 21:11:57Z

0

It's impossible to return an unknown amount of columns in sql.

What you CAN do is use is GROUP_CONCAT if you use mysql (other databases have other solutions) https://www.geeksforgeeks.org/mysql-group_concat-function/ You will end up with data like this.

PersonID    Goal            Note 
--------    --------------  ------------------
1           one,two,three   hello,world
2           alpha,beta      i,like,squirrels

answered Jun 1, 2020 at 21:11

Griffin

8467 silver badges14 bronze badges

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

Collectives™ on Stack Overflow

How do I combine multiple SQL rows into one in a select query?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related