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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Adding a title based on conditions?

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 Quantity
A 3
B 5
B 5
C 3

Should be:
A 3
B 10
C 3

But 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) Qty
FROM YourTable
GROUP BY ProductNumber

--
Chandu
Go to Top of Page

kfluffie
Posting Yak Master

103 Posts

Posted - 2012-12-24 : 05:15:44
quote:
Originally posted by bandi

SELECT ProductNumber, SUM(Quantity) Qty
FROM YourTable
GROUP BY ProductNumber

--
Chandu



So how will SQL know which rows to update the Processed flag in your query?

Best regards,
Tomas
Go to Top of Page

kfluffie
Posting Yak Master

103 Posts

Posted - 2012-12-24 : 05:17:02
Otherwise how can I add a title?


A 3
B 4
B 4
C 3

A 3
Product B
B 8
C 3

But only for the rows that has two or more rows (i.e. two or more product numbers).
Go to Top of Page

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
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-12-24 : 07:37:52
or this
DECLARE @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 title
FROM @TAB GROUP BY ProductNumber


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-24 : 08:00:32
quote:
Originally posted by madhivanan

or this
DECLARE @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 title
FROM @TAB GROUP BY ProductNumber


Madhivanan

Failing to plan is Planning to fail


Hi Madhivanan,
Your solution looks nice..

--
Chandu
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-12-27 : 08:07:55
quote:
Originally posted by bandi

quote:
Originally posted by madhivanan

or this
DECLARE @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 title
FROM @TAB GROUP BY ProductNumber


Madhivanan

Failing to plan is Planning to fail


Hi Madhivanan,
Your solution looks nice..

--
Chandu


Thanks

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -