Joining together 2 entries - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Joining together 2 entries

Author

Topic

jcnewman83
Starting Member

6 Posts

Posted - 2010-12-13 : 12:38:36

OK, this is one of those problems where I cannot see the wood through the trees so I am asking for some assistance.

I have a table like so.

Product amount1 value1
BAC 1000 1000
BACREN 2000 2000

What I want to do is bring these 2 together as 1 single entry.

can someone help me with the best way of achieving this??

Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-12-13 : 13:39:44

Please give more examples so we can see the number of different cases.

No, you're never too old to Yak'n'Roll if you're too young to die.

jcnewman83
Starting Member

6 Posts

Posted - 2010-12-14 : 04:18:46

quote:
Originally posted by webfred

Please give more examples so we can see the number of different cases.

No, you're never too old to Yak'n'Roll if you're too young to die.

the full result is below.
CommTyoe Product CaseCount GrossNew GrossnewInc GrossNewFee Provider
Renewals GI 1101 307 123 698 provider3
Renewals ASU 4 112 392 277 provider1
Renewals BAC 233 795 206 202 provider1
Renewals BACREN 3508 950 142 105 provider1
Renewals ASU 2586 685 212 160 provider2
Renewals BAC 6291 198 611 442 provider2
Renewals DIASU 7 176 114 668 provider2
Renewals DIBAC 23 447 292 178 provider2

apologies for it being out of alignment but I could not find a way to add a table.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-12-14 : 04:34:09

So we can see different "Products":

GI
ASU
BAC
BACREN
DIASU
DIBAC

but what would be the rule to treat different entries as ONE Product or not?

No, you're never too old to Yak'n'Roll if you're too young to die.

jcnewman83
Starting Member

6 Posts

Posted - 2010-12-14 : 05:21:17

quote:
Originally posted by webfred

So we can see different "Products":

GI
ASU
BAC
BACREN
DIASU
DIBAC

but what would be the rule to treat different entries as ONE Product or not?

No, you're never too old to Yak'n'Roll if you're too young to die.

OK, so the products are liked to provider however to add further complication I am only trying to join the BAC and BACREN entries together for provider 1

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-12-14 : 05:55:29

SELECT
LEFT(Product,3) as Product,
SUM(CaseCount) as CaseCount,
SUM(GrossNew) as GrossNew
FROM YourTable
WHERE LEFT(Product,3) = 'BAC' AND Provider = 'provider1'
GROUP BY LEFT(Product,3)

edit: added: AND Provider = 'provider1'

No, you're never too old to Yak'n'Roll if you're too young to die.

jcnewman83
Starting Member

6 Posts

Posted - 2010-12-14 : 06:08:37

quote:
Originally posted by webfred

SELECT
LEFT(Product,3) as Product,
SUM(CaseCount) as CaseCount,
SUM(GrossNew) as GrossNew
FROM YourTable
WHERE LEFT(Product,3) = 'BAC' AND Provider = 'provider1'
GROUP BY LEFT(Product,3)

edit: added: AND Provider = 'provider1'

No, you're never too old to Yak'n'Roll if you're too young to die.

Thanks webfred

sorry you had the task of drawing blood from a stone while trying to get the correct info from me.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-12-14 : 06:59:02

No problem, you can make it better next time

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources