Pivoting a table with multiple columns in SQL

Question

My goal here is to take a list of two corresponding store numbers and provide an output similar to:

Ultimate goal: produce a list of closest stores by travel time and distance based on source data of 2 rows per zip9 where each row is the travel time in distance, and in time, to a store in question.

The result is that each zip code has 2 stores to choose from, and the requirement is being able to return one row with both options.

+-----------+---------------+---------------------+-------------------+-------------------------+
|    zip    | Shortest_time | Shortest_time_store | Shortest_distance | Shortest_distance_store |
+-----------+---------------+---------------------+-------------------+-------------------------+
|  70011134 | 38.7035       |                  75 | 21.3124           |                     115 |
|  70011186 | 38.4841       |                  75 | 21.4144           |                     115 |
|  70011207 | 39.1567       |                  75 | 21.1826           |                     115 |
| 100013232 | 22.976        |                 145 | 9.5031            |                     115 |
| 112075140 | 21.888        |                 145 | 7.3705            |                     115 |
+-----------+---------------+---------------------+-------------------+-------------------------+

Original dataset

+---------------+--------------------------+-----------------------+------------------+
| CORRECTED_ZIP | SourceOrganizationNumber | Travel Time (Minutes) | Distance (Miles) |
+---------------+--------------------------+-----------------------+------------------+
|      70011134 |                       75 | 38.7035               | 26.8628          |
|      70011134 |                      115 | 39.3969               | 21.3124          |
|      70011186 |                       75 | 38.4841               | 26.7609          |
|      70011186 |                      115 | 39.6389               | 21.4144          |
|      70011207 |                       75 | 39.1567               | 31.2771          |
|      70011207 |                      115 | 39.188                | 21.1826          |
|     100013232 |                      115 | 28.6561               | 9.50311          |
|     100013232 |                      145 | 22.976                | 10.0307          |
|     112075140 |                      115 | 36.1803               | 7.37053          |
|     112075140 |                      145 | 21.888                | 9.50123          |
+---------------+--------------------------+-----------------------+------------------+

Dataset after I've modified it with this query:

SELECT TOP 1000 [corrected_zip]
              , TRY_CONVERT( DECIMAL(18, 4), ROUND([Travel Time (Minutes)], 4)) AS [Unit of Measurement]
              , [SourceOrganizationNumber]
              , 'Time' AS                                                          [Type]
FROM                       [db].[dbo].[my_table_A] [tt]
WHERE                     [tt].[CORRECTED_ZIP] IN('070011134', '070011186', '070011207', '112075140', '100013232')
AND [Travel Time (Minutes)] IN
(
    SELECT MIN([Travel Time (Minutes)])
    FROM   [db].[dbo].[my_table_A]
    WHERE  [CORRECTED_ZIP] = [tt].[CORRECTED_ZIP]
    GROUP BY [CORRECTED_ZIP]
)
UNION ALL
SELECT TOP 1000 [corrected_zip]
              , TRY_CONVERT( DECIMAL(18, 4), ROUND([Distance (Miles)], 4))
              , [SourceOrganizationNumber]
              , 'Distance'
FROM            [db].[dbo].[my_table_A] [tt]
WHERE           [tt].[CORRECTED_ZIP] IN('070011134', '070011186', '070011207', '112075140', '100013232')
AND [Distance (Miles)] IN
(
    SELECT MIN([Distance (Miles)])
    FROM   [db].[dbo].[my_table_A]
    WHERE  [CORRECTED_ZIP] = [tt].[CORRECTED_ZIP]
    GROUP BY [CORRECTED_ZIP]
)
ORDER BY [CORRECTED_ZIP];

+---------------+---------------------+--------------------------+----------+
| corrected_zip | Unit of Measurement | SourceOrganizationNumber |   Type   |
+---------------+---------------------+--------------------------+----------+
|      70011134 | 38.7035             |                       75 | Time     |
|      70011134 | 21.3124             |                      115 | Distance |
|      70011186 | 21.4144             |                      115 | Distance |
|      70011186 | 38.4841             |                       75 | Time     |
|      70011207 | 39.1567             |                       75 | Time     |
|      70011207 | 21.1826             |                      115 | Distance |
|     100013232 | 9.5031              |                      115 | Distance |
|     100013232 | 22.976              |                      145 | Time     |
|     112075140 | 21.888              |                      145 | Time     |
|     112075140 | 7.3705              |                      115 | Distance |
+---------------+---------------------+--------------------------+----------+

Data after I attempted to pivot it

+---------------+--------------------------+----------+---------+
| corrected_zip | SourceOrganizationNumber | Distance |  Time   |
+---------------+--------------------------+----------+---------+
|     070011134 |                      115 | 21.3124  | NULL    |
|     070011134 |                       75 | NULL     | 38.7035 |
|     070011186 |                      115 | 21.4144  | NULL    |
|     070011186 |                       75 | NULL     | 38.4841 |
|     070011207 |                      115 | 21.1826  | NULL    |
|     070011207 |                       75 | NULL     | 39.1567 |
|     100013232 |                      115 | 9.5031   | NULL    |
|     100013232 |                      145 | NULL     | 22.9760 |
|     112075140 |                      115 | 7.3705   | NULL    |
|     112075140 |                      145 | NULL     | 21.8880 |
+---------------+--------------------------+----------+---------+

It seems like my issue is picking the correct store ID as opposed to grouping by store ID?

GMB · Accepted Answer · 2020-04-15 19:11:58Z

3

You can use row_number() twice in a subquery(once to rank by time, another by distance), and then do conditional aggregation in the outer query:

select
    corrected_zip,
    min(travel_time) shortest_time,
    min(case when rnt = 1 then source_organization_number end) shortest_time_store,
    min(distance) shortest_distance,
    min(case when rnd = 1 then source_organization_number end) shortest_distance_store
from (
    select 
        t.*,
        row_number() over(partition by corrected_zip order by travel_time) rnt,
        row_number() over(partition by corrected_zip order by distance) rnd
    from mytable t
) t
group by corrected_zip

answered Apr 15, 2020 at 19:11

GMB

224k25 gold badges103 silver badges151 bronze badges

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

1 Comment

Sean Over a year ago

The answer is always so simple once I finally decide to post. I can't believe I forgot that I can run multiple windowed functions at the same time.

Collectives™ on Stack Overflow

Pivoting a table with multiple columns in SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related