CODEWITHSUNDEEP
sqlsql-serverreporting
Greg
Greg

Reputation: 11

Creating a new column based on data from an existing column

Consider a system to track repairs. I have a table with customer data and a common id key. I have a second table with a column to show which type of repair part was used on each id key and how many were used. Their definitions are below:

order_information
order_id  |  cust_last_name
465          Smith
899          Williams
512          Johnson
345          Fenton
122          Bowles
944          Cooper

parts_usage
order_id  |  part_type  |  part_quantity
465          Part 1        5
465          Part 2        4
899          Part 1        2
899          Part 2        8
899          Part 3        6
512          Part 3        1
345          Part 2        4
345          Part 3        5
122          Part 2        3
944          Part 1        2

I'd like to run a query for reporting that will return the part's pieces broken out like so:

order_id  |  Part 1  |  Part 2  |  Part 3  |  Total
465          5          4                     9
899          2          8          6          16
512                                1          1
345                     4          5          9
122                     3                     3
944          2                                2

Is it possible to do this with a query so that my reports can show how many of each part was used on each repair ticket?

As you can see, each order_id can have multiple part types and unique quantities. I want to break the different part types (I have 3 total) into 3 separate columns with their totals listed by order_id.

Upvotes: 1

Views: 1244

Answers (1)

Ian Preston
Ian Preston

Reputation: 39586

select order_id, [Part 1], [Part 2], [Part 3], Total
from 
(
  select oi.order_id
    , part_type
    , part_quantity
    , Total = sum(part_quantity) over (partition by oi.order_id)
  from order_information oi
    inner join parts_usage pu on oi.order_id = pu.order_id
) as parts
pivot 
(
  sum(part_quantity) for part_type in ([Part 1], [Part 2], [Part 3])
) as pvt
order by order_id

This works for me.

I have ordered the resultset by order_id as well; there doesn't appear to be a specific order in your example results but it is mentioned in the question details.

You can see the key is to combine the PIVOT with a SUM aggregate window function.

SQL Fiddle with demo.

Upvotes: 1

Related Questions