How to convert multiple row data to multiple column using PIVOT in SQL-SERVER?

Question

I am looking for an efficient way to convert rows to columns in SQL server using Pivot. I already got the output using aggregate function with cases and joins separately but I am hoping that PIVOT is a clear way to deal with this kind of rows to column transformation.

The example data is

-------------------------------------
| ID | Row | First |Last | Postal |
------------------------------------
| 1  | 1   | John  | Doe | B4K    |
| 1  | 2   | Matt  | Kev | 2H1    |
| 2  | 1   | Hary  | Lot | L26    |
| 2  | 2   | Fork  | Har | M3R    |
| 3  | 1   | Yuv   | Hal | L39    |
------------------------------------

This is my result:

| ID | First1 |Last1 | Postal1 | First2 |Last2 | Postal2 |
 -----------------------------------------------------------
| 1  | John   | Doe  | B4K     | Matt   | Kev  | 2H1     |
| 2  | Hary   | Lot  | L26     | Fork   | Har  | M3R     |
| 3  | Yuv    | Hal  | L39     | NULL   | NULL | NULL    |
-----------------------------------------------------------

How could I achieve this result using PIVOT?

Change your aggregate function to get expected result. You do not need Pivot — uzi
– uzi, Commented Feb 6, 2018 at 8:18
If you have no idea how many rows you need to turn into columns (in your case 2,2,1) pivot cannot help you. You should construct dynamic pivot and it would be complex query — sepupic
– sepupic, Commented Feb 6, 2018 at 8:25
I do know the maximum limits for each IDs. So, in that case @Denis UnPivot/Pivot should still work. — OmGanesh
– OmGanesh, Commented Feb 7, 2018 at 4:09

Denis Rubashkin · Accepted Answer · 2018-02-06 10:24:30Z

3

Your data:

CREATE TABLE #Test (
    ID          INT,
    [Row]       INT,
    [First]     VARCHAR(32),
    [Last]      VARCHAR(32),
    [Postal]    VARCHAR(32)
)

INSERT #Test
VALUES
(1, 1, 'John', 'Doe', 'B4K'),
(1, 2, 'Matt', 'Kev', '2H1'),
(2, 1, 'Hary', 'Lot', 'L26'),
(2, 2, 'Fork', 'Har', 'M3R'),
(3, 1, 'Yuv', 'Hal', 'L39')

To get your expected result you have to use UNPIVOT before using PIVOT like below:

SELECT *
FROM (
    SELECT ID, ITEM + CAST([Row] AS VARCHAR) AS Name, VALUE
    FROM (
        SELECT *
        FROM #Test
    )s
    UNPIVOT
    (VALUE FOR Item IN ([First], [Last], [Postal])) p
) src
PIVOT
(MAX(VALUE) FOR Name IN ([First1], [Last1], [Postal1], [First2], [Last2], [Postal2])
) pvt

Obviously, the line (MAX(VALUE) FOR Name IN ([First1], [Last1], [Postal1], [First2], [Last2], [Postal2]) depends on count of different values in the field [Row] so the query has to be created dynamically.

Instead of using UNPIVOT/PIVOT you could create dynamically a query which will use OUTER APPLY:

SELECT DISTINCT ID,
    t1.[First]  AS First1,
    t1.[Last]   AS Last1,
    t1.Postal   AS Postal1,
    t2.[First]  AS First2,
    t2.[Last]   AS Last2,
    t2.Postal   AS Postal2
FROM #Test t

    OUTER APPLY (
        SELECT TOP 1 [First], [Last], [Postal]
        FROM #Test
        WHERE ID = t.ID
            AND [Row] = 1
        ) t1

    OUTER APPLY (
        SELECT TOP 1 [First], [Last], [Postal]
        FROM #Test
        WHERE ID = t.ID
            AND [Row] = 2
        ) t2

edited Feb 6, 2018 at 10:24

answered Feb 6, 2018 at 10:10

Denis Rubashkin

2,2311 gold badge11 silver badges16 bronze badges

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

2 Comments

OmGanesh Over a year ago

Thanks for your different perspectives to solve this issue. Do you have any idea if Pivot is better than Aggregate method or not?

Denis Rubashkin Over a year ago

I don't think that anyone can tell you which will be more efficient without knowledge of your indexing and table size. You should analyze the execution plan of these two queries and then choose an appropriate solution.

Collectives™ on Stack Overflow

How to convert multiple row data to multiple column using PIVOT in SQL-SERVER?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related