SQL Server 2012: Pivoting(?) fixed number of rows as columns

Question

I have a table called Car:

CREATE TABLE [dbo].[Car](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Brand] [nchar](10) NOT NULL
)

and one called Passenger:

CREATE TABLE [dbo].[Passenger](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [CarID] [int] NOT NULL,
    [Name] [nchar](10) NOT NULL
)

Containing the following data:

+----+------------+
| ID |   Brand    |
+----+------------+
|  1 | Ford       |
|  2 | Audi       |
+----+------------+

+----+-------+------------+
| ID | CarID |    Name    |
+----+-------+------------+
|  1 |     1 | Joe        |
|  2 |     1 | Jack       |
|  3 |     1 | Jim        |
|  4 |     2 | Bob        |
|  5 |     2 | Frank      |
+----+-------+------------+

Also, I have a limit for the number of passengers in a car = 5. (Business logic)

What I'd like to do is to display this data in a View table, like the following example:

+----+------------+------------+------------+------------+------------+------------+
| ID |   Brand    | Passenger1 | Passenger2 | Passenger3 | Passenger4 | Passenger5 |
+----+------------+------------+------------+------------+------------+------------+
|  1 | Ford       | Joe        | Jack       | Jim        | NULL       | NULL       |
|  2 | Audi       | Bob        | Frank      | NULL       | NULL       | NULL       |
+----+------------+------------+------------+------------+------------+------------+

So, there if there are less than 5 passengers, the rest of the columns is NULL, and we don't need to handle more than 5 passengers.

Ordering of passengers is not really needed, but NULLs must be at the end (e.g. Frank, Bob, NULL, NULL, NULL is OK for Car ID 2, but Frank, NULL, NULL, Bob, NULL is not.) If it is also possible to order the passengers, then I'd prefer to order them by their IDs.

How can I do this data transformation using SQL Server 2012?

Pரதீப் · Accepted Answer · 2016-03-01T15:54:00.100

Use Pivot operator to pivot the data

Use Row_number window function to generate the passenger number so the car without all the 5 passengers will have NULL values at the end

;WITH cte
     AS (SELECT c.id,
                c.Brand,
                'Passenger'+ Cast(Row_number() OVER(partition BY c.id ORDER BY p.id) AS VARCHAR(50)) p_name,
                NAME
         FROM   [Car] c
                JOIN [Passenger] p
                  ON c.ID = p.CarID)
SELECT *
FROM   cte
       PIVOT (Max(NAME)
             FOR p_name IN(Passenger1,
                           Passenger2,
                           Passenger3,
                           Passenger4,
                           Passenger5 ))pv
ORDER  BY id

Conditional Aggregate method

;WITH cte
     AS (SELECT c.id,
                c.Brand,
                'Passenger'+ Cast(Row_number() OVER(partition BY c.id ORDER BY p.id) AS VARCHAR(50)) p_name,
                NAME
         FROM   [Car] c
                JOIN Passenger p
                  ON c.ID = p.CarID)
SELECT id,
       Brand,
       [Passenger1]=Max(CASE WHEN p_name = 'Passenger1' THEN NAME END),
       [Passenger2]=Max(CASE WHEN p_name = 'Passenger2' THEN NAME END),
       [Passenger3]=Max(CASE WHEN p_name = 'Passenger3' THEN NAME END),
       [Passenger4]=Max(CASE WHEN p_name = 'Passenger4' THEN NAME END),
       [Passenger5]=Max(CASE WHEN p_name = 'Passenger5' THEN NAME END)
FROM   cte
GROUP  BY id,
          Brand
ORDER  BY id

Result:

╔════╦════════════╦════════════╦════════════╦════════════╦════════════╦════════════╗
║ id ║   Brand    ║ Passenger1 ║ Passenger2 ║ Passenger3 ║ Passenger4 ║ Passenger5 ║
╠════╬════════════╬════════════╬════════════╬════════════╬════════════╬════════════╣
║  1 ║ Ford       ║ Joe        ║ Jack       ║ Jim        ║ NULL       ║ NULL       ║
║  2 ║ Audi       ║ Bob        ║ Frank      ║ NULL       ║ NULL       ║ NULL       ║
╚════╩════════════╩════════════╩════════════╩════════════╩════════════╩════════════╝

score 0 · Answer 2 · answered Mar 01 '16 at 15:55

UNTESTED:

We use row_num() over partition by card ID to get a constant seat number for the 5 positions add a P because I'm unsure if SQL server can have a column star with number and then pivot the data based on these 5 derived values.

Select brand, P1, P2, P3, P4, P5
FROM 
  (
  SELECT brand, name, 'P' & row_Number() Over (partition by CarID order by P.name) as PNum
  FROM car C
  INNER JOIN passenger P
    on C.CardID = P.CardID
  ) src
  PIVOT 
  (
    max(src.name)
    FOr pnum in ([P1],[P2],[P3],[P4],[P5])
  ) as PT

SQL Server 2012: Pivoting(?) fixed number of rows as columns

2 Answers2