CODEWITHSUNDEEP
sqlexcelt-sql
mendokusai
mendokusai

Reputation: 37

T-SQL: Distinct selection with values based on duplicates listed as subcolumns in a query to Excel

I'm trying to make a combined listing for weight of input and output for a given production. However, as a non-IT person, my SQL skills are rather limited. I'm using excel to create a connection to the SQL-server and send a query for getting the data I want.

Database:

My database is on a Microsoft SQL 2012 server, so I'd have to make my query using T-SQL.

My tables:

Products

|------------|-------------|---------------|-----------|---------|-----------|----------|
| PrimaryID  | PrimaryName | Primaryweight | SubID     | OrderID | SubWeight | CutTime  |
|------------|-------------|---------------|-----------|---------|-----------|----------|
| P0000001   | NameOne     | 1234          | S00000001 | 12345   | 123       | datetime |
| P0000001   | NameOne     | 1234          | S00000002 | 12346   | 234       | datetime |
| P0000001   | NameOne     | 1234          | S00000003 | 12347   | 345       | datetime |
| P0000002   | NameOne     | 1235          | S00000004 | 12345   | 456       | datetime |
| P0000002   | NameOne     | 1235          | S00000005 | 12346   | 567       | datetime |
| P0000002   | NameOne     | 1235          | S00000006 | 12347   | 678       | datetime |
| P0000003   | NameTwo     | 2345          | S00000007 | 12335   | 789       | datetime |
| P0000003   | NameTwo     | 2345          | S00000008 | 12336   | 321       | datetime |
| P0000004   | NameTwo     | 2346          | S00000009 | 12335   | 432       | datetime |
| P0000004   | NameTwo     | 2346          | S00000010 | 12336   | 543       | datetime |
| P0000005   | NameThree   | 3456          | S00000011 | 12355   | 654       | datetime |
| P0000005   | NameThree   | 3456          | S00000012 | 12356   | 765       | datetime |
| P0000005   | NameThree   | 3456          | S00000013 | 12357   | 876       | datetime |
|------------|-------------|---------------|-----------|---------|-----------|----------|

Orders

|---------|------------|
| OrderID | ProdLaneID |
|---------|------------|
| 12335   | 1          |
| 12336   | 2          |
| 12345   | 1          |
| 12346   | 2          |
| 12347   | 3          |
| 12355   | 1          |
| 12356   | 2          |
| 12357   | 3          |
|---------|------------|

What I want:

Output to include

PrimaryID

ProductType

PrimaryWeight

SubWeightLine1 (SubWeight if a SubID has been sent to ProdLaneID 1, else NULL)

SubWeightLine2 (SubWeight if a SubID has been sent to ProdLaneID 2, else NULL)

SubWeightLine3 (SubWeight if a SubID has been sent to ProdLaneID 3, else NULL)

Output Table

|-----------|-------------|---------------|----------------|----------------|----------------|
| PrimaryID | ProductType | Primaryweight | SubWeightLine1 | SubWeightLine2 | SubWeightLine3 |
|-----------|-------------|---------------|----------------|----------------|----------------|
| P0000001  | NameOne     | 1234          | 123            | 234            | 345            |
| P0000002  | NameOne     | 1235          | 456            | 567            | 678            |
| P0000003  | NameTwo     | 2345          | 789            | 321            |                |
| P0000004  | NameTwo     | 2346          | 432            | 543            |                |
| P0000005  | NameThree   | 3456          | 654            | 765            | 876            |
|-----------|-------------|---------------|----------------|----------------|----------------|

My problem:

How to go about listing each unique PrimaryID with its PrimaryWeight and respective SubWeight matched to ProdLineID (as the table illustrates)?

All my attempts to make a functional query with various combinations of SELECT DISTINCT, CASE WHEN THEN ELSE END and JOIN and have not worked.

Any input as to how this could be achieved would be greatly appreciated.

Upvotes: 0

Views: 25

Answers (1)

Radim Bača
Radim Bača

Reputation: 10701

Use conditional aggregation and outer join

select PrimaryId, 
       PrimaryName as ProductType, 
       PrimaryWeight,
       max(case when o.prodlaneid = 1 then p.subweight end) SubWeightLine1,
       max(case when o.prodlaneid = 2 then p.subweight end) SubWeightLine2,
       max(case when o.prodlaneid = 3 then p.subweight end) SubWeightLine3
from product p
left join orders o on p.orderid = o.orderid
group by PrimaryId, PrimaryName, PrimaryWeight

Upvotes: 1

Related Questions