SQL Server multi pivot

Question

I have a table with the following data

+-----------+-----------+----------+----------+--------+
| SourceZip | TargetZip | TargetID | Distance | rowNum |
+-----------+-----------+----------+----------+--------+
|     00602 |     08831 |    14780 | 1567.579 | R1     |
|     00602 |     08831 |     6398 | 1567.579 | R2     |
|     00602 |     07747 |     4290 | 1570.665 | R3     |
|     00603 |     08831 |    14780 | 1561.272 | R1     |
|     00603 |     08831 |     6398 | 1561.272 | R2     |
|     00603 |     07747 |     4290 | 1564.328 | R3     |
+-----------+-----------+----------+----------+--------+

I need this in the following format

+-----------+-------------+-------------+-------------+-------------+-------------+-------------+
| SourceZip | R1_TargetID | R1_Distance | R2_TargetID | R2_Distance | R3_TargetID | R3_Distance |
+-----------+-------------+-------------+-------------+-------------+-------------+-------------+
|     00602 |       14780 |    1567.579 |        6398 |    1567.579 |        4290 |    1570.665 |
|     00603 |       14780 |    1561.272 |        6398 |    1561.272 |        4290 |    1564.328 |
+-----------+-------------+-------------+-------------+-------------+-------------+-------------+

I've been told that I can use a single pivot for this, but every example pivot I can find uses an aggregate function, and generates only one column for the pivoted data.

Is this something I can do with a single pivot, or do I need to generate 2 separate pivot tables (one each for TargetID and Distance) and then join them?

UPDATE

I ended up combining the fields I need into a single value that can parse out in the application the data is being passed to

select *
from  ( SELECT SourceZip, CONVERT(varchar(10), TargetID)+ '|' + CONVERT(varchar(10), Distance) as TargetSite, rowNum
        FROM Targets
      ) as SourceTable

pivot (min(TargetSite)
for rowNum in (R1
                ,R2 
                ,R3
                )
      ) as  pivottable

It's not ideal, but it works.

I would still appreciate any suggestions for multiple column pivoting

Answer 1

If its possible to convert to pivot column to same type, you can use this approach.

DECLARE @MyTable TABLE (SourceZip VARCHAR(10), TargetZip VARCHAR(10),TargetID INT, Distance VARCHAR(10), rowNum VARCHAR(10))

INSERT INTO @MyTable VALUES
('00602','08831','14780','1567.579','R1'),
('00602','08831','6398','1567.579','R2'),
('00602','07747','4290','1570.665','R3'),
('00603','08831','14780','1561.272','R1'),
('00603','08831','6398','1561.272','R2'),
('00603','07747','4290','1564.328','R3')

SELECT * FROM 
    (
        SELECT SourceZip,  
            CONVERT(VARCHAR(20),TargetID) [TargetID_Distance], 
            rowNum + '_TargetID' rowNum  
            FROM @MyTable
        UNION ALL 
        SELECT SourceZip, 
            CONVERT(VARCHAR(20),Distance) [TargetID_Distance], 
            rowNum + '_Distance' rowNum 
        FROM @MyTable
    ) SRC
    PIVOT (MAX([TargetID_Distance]) 
            FOR rowNum IN ( [R1_TargetID],
                            [R1_Distance], 
                            [R2_TargetID], 
                            [R2_Distance], 
                            [R3_TargetID], 
                            [R3_Distance] ) ) PVT

Result:

SourceZip  R1_TargetID          R1_Distance          R2_TargetID          R2_Distance          R3_TargetID          R3_Distance
---------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
00602      14780                1567.579             6398                 1567.579             4290                 1570.665
00603      14780                1561.272             6398                 1561.272             4290                 1564.328

Answer 2

So you want one row per SourceZip:

...
GROUP BY SourceZip;

Now you want a column of R1_TargetID:

SELECT
  SUM(CASE WHEN rowNum = 'R1' THEN TargetID END) R1_TargetID,

The case replaces everything that is not rowNum = 'R1' by null (due to the implied else null clause). null is ignored by sum. As long as only one row has rowNum = 'R1', it will be the same result as the original value.

The other columns can be done likewise:

SUM(CASE WHEN rowNum = 'R1' THEN Distance END) R1_Distance
...

More about this technique: http://modern-sql.com/use-case/pivot