CODEWITHSUNDEEP
sqlsql-server
Rafael Wille
Rafael Wille

Reputation: 11

How to join two rows into one on the same table based on ID

I have a table that looks like this: 

+------------+-------------+-------------+
carId | infoId | infoTitle | Description |  
+------------+-------------+-------------+

I have some descriptions related to the same car: 

+------------+-------------+-------------+
carId | infoId | infoTitle | Description |  
+------------+-------------+-------------+

1     | 11     | Wheels    | nice wheels |
1     | 12     | Paint     | some nice red painting |

I need to join theses two info into the same car. The output will look like this:

+------------+-------------+-------------+---------+------------+--------------+
carId | infoId | infoTitle | Description | infoId2 | infoTitle2 | Description2 |  
+------------+-------------+-------------+---------+------------+--------------+

1     | 11     | Wheels    | nice wheels | 12      | Paint      | some nice red painting |

The problem is that I don't have a fixed number for the infos that are related to the same car, so I need a query that add a new column to every info related to that car.

I've tryed to do something with SELECT DISTINCT, but obviously didn't worked.

Upvotes: 0

Views: 137

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

If you know the number of "items" per car, you can use conditional aggregation:

select carid,
       max(case when seqnum = 1 then infoid end) as infoid_1,
       max(case when seqnum = 1 then infotitle end) as infotitle_1,
       max(case when seqnum = 1 then description end) as description_1,
       max(case when seqnum = 2 then infoid end) as infoid_2,
       max(case when seqnum = 2 then infotitle end) as infotitle_2,
       max(case when seqnum = 2 then description end) as description_2
from (select t.*,
             row_number() over (partition by carid order by infoid) as seqnum
      from t
     ) t
group by carid;

You can easily extend this to more items. You can get the maximum number using:

select top (1) count(*)
from t
group by carid
order by count(*) desc;

If you don't know the maximum, then this is much trickier because a SQL query returns a fixed set of columns. You could use dynamic SQL to construct the SQL that you want. Or you could decide to change your data representation. For instance, you could aggregate the values into a JSON array.

Upvotes: 1

Related Questions