CODEWITHSUNDEEP
sql-serverpivot
John
John

Reputation: 23

Newbie SQL Pivot query

long time listener, first time caller...

I have a table (Tracker) as per the following:

ID | Works Order    |Activity   |Status
---|----------------|-----------|------
1  | W123456789-001 |10A        |GOOD
2  | W123456789-002 |10A        |GOOD
3  | W123456789-003 |10A        |SCRAP
4  | W123456789-001 |20A        |GOOD
5  | W123456789-002 |20A        |GOOD

...where the Works Order number W123456789-001 is made up of the actual works order number; W123456789, followed by the part number; 001. The 'Activites' are fixed as 10A, 20A, 30A & 110A, the status is either 'GOOD' or 'SCRAP'. I would like to summarise & output it like the following:

Works Order  |10A |20A |30A |110A
-------------|----|----|----|----
W123456789   |2   |2   |0   |0

...where the number in cols 10,20,30 & 110A are the count of the 'GOOD's for each activity.

I can get most of the way there with the following code;

select [WO_Part],[10A G],[20A G],[30A G],[110AG]
from
(
  select
  [Activity],
  LEFT([WOPart],10) AS [WO_Part],
  iIf([Status]='GOOD ',1,0) AS [STATUS],
  [ACTIVITY]+LEFT([STATUS],1) AS [CONCAT]
  from Tracker
) d
pivot
(
  SUM([Status])
  for [CONCAT] in ([10A G],[20A G],[30A G],[110AG])
) piv;

but, I get multiple rows per Works Order with the results spread over a row per activity, like below:

Works Order  |10A |20A |30A |110A
-------------|----|----|----|----
W123456789   |2   |0   |0   |0 
W123456789   |0   |2   |0   |0

How should I alter my query so the results are combined on a single row?

Works Order  |10A |20A |30A |110A
-------------|----|----|----|----
W123456789   |2   |2   |0   |0

Thank you in advance!

Upvotes: 2

Views: 41

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

With pivot, extra columns in the FROM clause result in extra rows. So you need to be careful about what you select:

select [WO_Part], [20A G],[30A G],[110AG]
from (select LEFT([WOPart], 10) AS [WO_Part],
             (case when [Status] = 'GOOD ' then 1 else 0 end) AS [STATUS],
             [ACTIVITY]+LEFT([STATUS],1) AS [CONCAT]
      from Tracker
     ) d
pivot (SUM([Status])
       for [CONCAT] in ([20A G],[30A G],[110AG])
     ) piv;

This behavior is one reason why I prefer conditional aggregations.

Upvotes: 1

Related Questions