Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
kfluffie
Posting Yak Master
103 Posts |
Posted - 2012-12-24 : 04:48:49
|
Hi,I have a set of order lines which are being retrieved by a C# application.These order lines are than verified and ordered through another system.When the order lines have the same product number I would like to group them together as well as it's quantity.For example:ProductNumber QuantityA 3B 5B 5C 3Should be:A 3B 10C 3But I also have a bit field called Processed so when the user checks this field (changes it from false to true) it means that the order line has been processed and C# or SQL needs to be able to update the original rows from this "virtual" created row.Can anyone help me with this?Best regards,Tomas |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-12-24 : 04:56:19
|
SELECT ProductNumber, SUM(Quantity) QtyFROM YourTableGROUP BY ProductNumber--Chandu |
|
|
kfluffie
Posting Yak Master
103 Posts |
Posted - 2012-12-24 : 05:15:44
|
quote: Originally posted by bandi SELECT ProductNumber, SUM(Quantity) QtyFROM YourTableGROUP BY ProductNumber--Chandu
So how will SQL know which rows to update the Processed flag in your query?Best regards,Tomas |
|
|
kfluffie
Posting Yak Master
103 Posts |
Posted - 2012-12-24 : 05:17:02
|
Otherwise how can I add a title?A 3B 4B 4C 3A 3Product BB 8C 3But only for the rows that has two or more rows (i.e. two or more product numbers). |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-12-24 : 05:53:31
|
May be this......?DECLARE @TAB TABLE(ProductNumber char(1), qty int)INSERT INTO @TAB VALUES('A', 3),('B', 4),('B', 4),('C', 3);with CTE AS ( SELECT ProductNumber, SUM(qty) qty FROM @TAB GROUP BY ProductNumber )SELECT ISNULL(c.ProductNumber, t1.ProductNumber), ISNULL(c.qty, t1.qty), CASE WHEN t1.qty != c.qty THEN 'Product'+t1.ProductNumber ELSE '' END processed FROM @TAB t1 LEFT JOIN CTE c ON t1.ProductNumber = c.ProductNumber AND t1.qty != c.qty --Chandu |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2012-12-24 : 07:37:52
|
or thisDECLARE @TAB TABLE(ProductNumber char(1), qty int)INSERT INTO @TAB VALUES('A', 3),('B', 4),('B', 4),('C', 3)SELECT ProductNumber, SUM(qty) as qty, case when count(*)>1 then 'Product '+ProductNumber else '' end as titleFROM @TAB GROUP BY ProductNumber MadhivananFailing to plan is Planning to fail |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-12-24 : 08:00:32
|
quote: Originally posted by madhivanan or thisDECLARE @TAB TABLE(ProductNumber char(1), qty int)INSERT INTO @TAB VALUES('A', 3),('B', 4),('B', 4),('C', 3)SELECT ProductNumber, SUM(qty) as qty, case when count(*)>1 then 'Product '+ProductNumber else '' end as titleFROM @TAB GROUP BY ProductNumber MadhivananFailing to plan is Planning to fail
Hi Madhivanan,Your solution looks nice..--Chandu |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2012-12-27 : 08:07:55
|
quote: Originally posted by bandi
quote: Originally posted by madhivanan or thisDECLARE @TAB TABLE(ProductNumber char(1), qty int)INSERT INTO @TAB VALUES('A', 3),('B', 4),('B', 4),('C', 3)SELECT ProductNumber, SUM(qty) as qty, case when count(*)>1 then 'Product '+ProductNumber else '' end as titleFROM @TAB GROUP BY ProductNumber MadhivananFailing to plan is Planning to fail
Hi Madhivanan,Your solution looks nice..--Chandu
Thanks MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|