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 as table
(
StockCode Varchar (10)
,[Route] Int
,Operation Int
)
Insert @Test
Values ('PARTA', 0, 1),
('PARTA', 0, 2) ,
('PARTA', 0, 3) ,
('PARTA', 0, 4) ,
('PARTA', 1, 1) ,
('PARTA', 1, 2) ,
('PARTA', 2, 1)
SELECT *
FROM
@test
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 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 StockCodeIf 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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy