Combining a variable number of records into a single record

user_5644666 0
Viewed 23

The gist of the problem is that I need to combine multiple records from Table A into a single record, based on a shared Id and insert that into Table B. Each Id can have a maximum of three records associated with it with a minimum of 1, being the preferred destinations for the Id. If the record has less than the maximum number of preferences, I want to set those columns to NULL in Table B.

Here's an example:

Table A 

ID | Preference| Destination
--------------------------
10 | 1         | Building A
10 | 2         | Building B
10 | 3         | Building C
23 | 1         | Building B
23 | 2         | Building A
45 | 1         | Building C

Table B

ID | Destination1 | Destination2 | Destination3
-----------------------------------------------
   |              |              |

I want to combine the records in Table A so that it displays like so in Table B

ID | Destination1 | Destination2 | Destination3
-----------------------------------------------
10 | Building A   | Building B   | Building C   
23 | Building B   | Building A   | NULL    
45 | Building C   | NULL         | NULL

Any help is greatly appreciated!

sql-server sql
1 Answers

You can use conditional aggregation:

select id,
       max(case when preference = 1 then destination end) as destination1,
       max(case when preference = 2 then destination end) as destination2,
       max(case when preference = 3 then destination end) as destination3
from t
group by id;
user_11440350
Related