sql server - Join the SQL Bridge table

Question

I have the following tables

User

+--------+------+
| UserID | Name |
+--------+------+
| User1  | John |
| User2  | Mike |
+--------+------+

Device

+----------+------------+------------+------+
| DeviceID | DeviceName | DeviceType | Good |
+----------+------------+------------+------+
|        1 | Device1    | A          |    0 |
|        2 | Device2    | A          |    1 |
|        3 | Device7    | B          |    0 |
|        4 | Device8    | B          |    1 |
|        5 | Device11   | C          |    0 |
|        6 | Device12   | C          |    1 |
+----------+------------+------------+------+

UserDevice

 +--------------+--------+----------+
| UserDeviceID | UserID | DeviceID |
+--------------+--------+----------+
| z            | User1  |        1 |
| y            | User1  |        3 |
| x            | User1  |        5 |
| w            | User2  |        2 |
| v            | User2  |        4 |
| u            | User2  |        6 |
+--------------+--------+----------+

I want to join these tables like below

+----------+-------------+-------------+-------------+
| UserName | DeviceTypeA | DeviceTypeB | DeviceTypeC |
+----------+-------------+-------------+-------------+
| User1    | Device1     | Device7     | Device11    |
| User2    | Device2     | Device8     | Device22    |
+----------+-------------+-------------+-------------+

I tried all types of join query but unfortunately, I could not get the data in the above format. Could someone please help?

question from:https://stackoverflow.com/questions/65601684/join-the-sql-bridge-table

Answer 1

You can do this using PIVOT (documentation)

SELECT P.[Name], P.A AS DeviceTypeA, P.B AS DeviceTypeB, P.C AS DeviceTypeC
   FROM
      (
         SELECT U.[Name], D.DeviceName, D.DeviceType
            FROM [User] U
            INNER JOIN DeviceUser DU
               ON DU.UserID = U.UserID
            INNER JOIN Device D
               ON D.DeviceID = DU.DeviceID
      ) AS X
      PIVOT
      (
         MAX(X.DeviceName)
         FOR X.DeviceType IN([A], [B], [C])
      ) AS P;

Note however that this will return only one device name for a certain user and device type even if there are more devices of a device type for a that user.