January 11, 2012 at 4:40 am
Hi Guys,
I am scratching my head a little on this one - as I need to get a solution quite quickly, I was hoping for some guidance.
Firstly, I must apologise for such a simple request ...
I have a table of Operations against a Stock Code with different Routes eg.
StockCode Route Operation
PARTA 0 1
PARTA 0 2
PARTA 0 3
PARTA 0 4
PARTA 1 1
PARTA 1 2
PARTA 2 1
I want to end up with a "view" of ;
StockCode Route_0 Count_0 Route_1 Count_1 Route_2 Count_2
PARTA 0 4 1 2 2 1
So my script is .....
select
StockCode
, (CASE Route When '0' THEN Route Else ' ' END) as Route_0
, (CASE Route When '0' THEN Count( Operation ) Else 0 END) as CountOperations_0
, (CASE Route When '1' THEN Route Else ' ' END) as Route_1
, (CASE Route When '1' THEN Count( Operation ) Else 0 END) as CountOperations_1
, (CASE Route When '2' THEN Route Else ' ' END) as Route_2
, (CASE Route When '2' THEN Count( Operation ) Else 0 END) as CountOperations_2
, (CASE Route When '3' THEN Route Else ' ' END) as Route_3
, (CASE Route When '3' THEN Count( Operation ) Else 0 END) as CountOperations_3
from BomOperations
group by StockCode, Route
order by StockCode
But the results are one record per route which is to do with the Group - how do I change it so I get one line ??
Hopefully this is clear ?
Thanks
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.
January 11, 2012 at 5:13 am
BEGIN TRAN
--Sample data
CREATE TABLE BomOperations (StockCode CHAR(5), Route TINYINT, Operation TINYINT)
INSERT INTO BomOperations
SELECT 'PARTA', 0, 1
UNION ALL SELECT 'PARTA', 0, 2
UNION ALL SELECT 'PARTA', 0, 3
UNION ALL SELECT 'PARTA', 0, 4
UNION ALL SELECT 'PARTA', 1, 1
UNION ALL SELECT 'PARTA', 1, 2
UNION ALL SELECT 'PARTA', 2, 1
--Actual query
SELECT StockCode,
MAX(CASE Route WHEN 0 THEN Route ELSE NULL END) AS Route_0,
MAX(CASE Route WHEN 0 THEN Counted ELSE NULL END) AS CountOperations_0,
MAX(CASE Route WHEN 1 THEN Route ELSE NULL END) AS Route_1,
MAX(CASE Route WHEN 1 THEN Counted ELSE NULL END) AS CountOperations_1,
MAX(CASE Route WHEN 2 THEN Route ELSE NULL END) AS Route_2,
MAX(CASE Route WHEN 2 THEN Counted ELSE NULL END) AS CountOperations_2,
MAX(CASE Route WHEN 3 THEN Route ELSE NULL END) AS Route_3,
MAX(CASE Route WHEN 3 THEN Counted ELSE NULL END) AS CountOperations_3
FROM (SELECT StockCode, Route, Operation, COUNT(*) OVER(PARTITION BY Route) AS Counted
FROM BomOperations) a
GROUP BY StockCode
ORDER BY StockCode
ROLLBACK
January 11, 2012 at 5:22 am
Also, if Operation always contains every Operation then you could do it like this instead: -
--Actual query version 2
SELECT StockCode,
MAX(CASE Route WHEN 0 THEN Route ELSE NULL END) AS Route_0,
MAX(CASE Route WHEN 0 THEN Counted ELSE NULL END) AS CountOperations_0,
MAX(CASE Route WHEN 1 THEN Route ELSE NULL END) AS Route_1,
MAX(CASE Route WHEN 1 THEN Counted ELSE NULL END) AS CountOperations_1,
MAX(CASE Route WHEN 2 THEN Route ELSE NULL END) AS Route_2,
MAX(CASE Route WHEN 2 THEN Counted ELSE NULL END) AS CountOperations_2,
MAX(CASE Route WHEN 3 THEN Route ELSE NULL END) AS Route_3,
MAX(CASE Route WHEN 3 THEN Counted ELSE NULL END) AS CountOperations_3
FROM (SELECT StockCode, Route, MAX(Operation) AS Counted
FROM BomOperations
GROUP BY StockCode, Route) a
GROUP BY StockCode
ORDER BY StockCode
If you test the performance, I bet that this version is slightly better.
January 11, 2012 at 5:25 am
I may have misunderstood (which is quite possible!) but is this what you are after?
declare @test-2 as table
(
StockCode Varchar (10)
,[Route] Int
,Operation Int
)
Insert @test-2
Values ('PARTA', 0, 1),
('PARTA', 0, 2) ,
('PARTA', 0, 3) ,
('PARTA', 0, 4) ,
('PARTA', 1, 1) ,
('PARTA', 1, 2) ,
('PARTA', 2, 1)
SELECT *
FROM
PIVOT
(
COUNT([Route])
FOR
Operation IN
([0],[1], [2],[3],[4])
) as Piv
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
January 11, 2012 at 5:39 am
Cadavre (1/11/2012)
Also, if Operation always contains every Operation then you could do it like this instead: -
--Actual query version 2SELECT StockCode,
MAX(CASE Route WHEN 0 THEN Route ELSE NULL END) AS Route_0,
MAX(CASE Route WHEN 0 THEN Counted ELSE NULL END) AS CountOperations_0,
MAX(CASE Route WHEN 1 THEN Route ELSE NULL END) AS Route_1,
MAX(CASE Route WHEN 1 THEN Counted ELSE NULL END) AS CountOperations_1,
MAX(CASE Route WHEN 2 THEN Route ELSE NULL END) AS Route_2,
MAX(CASE Route WHEN 2 THEN Counted ELSE NULL END) AS CountOperations_2,
MAX(CASE Route WHEN 3 THEN Route ELSE NULL END) AS Route_3,
MAX(CASE Route WHEN 3 THEN Counted ELSE NULL END) AS CountOperations_3
FROM (SELECT StockCode, Route, MAX(Operation) AS Counted
FROM BomOperations
GROUP BY StockCode, Route) a
GROUP BY StockCode
ORDER BY StockCode
If you test the performance, I bet that this version is slightly better.
Absolutely Perfect !!
Brilliant - and I can understand how this works ( which is always a benefit ! )
Many Thanks for you rquick efficient help.
Regards
Steve
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.
January 11, 2012 at 6:21 am
No problem.
Did you test both methods to see which performs better?
January 11, 2012 at 6:24 am
To be honest - Performance is not an issue -
31920 records
consolidated to 10280 in less than a second - I will convert this to a view and then do my analysis - but is only a short term tidy up routine
I need to move Route 1 to Route 0 if it is empty etc.....
This is a great help - thank you again.
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply