Grouping by entity - 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)

Grouping by entity

Author

Topic

waterduck
Aged Yak Warrior

982 Posts

Posted - 2010-08-11 : 13:00:14

declare @account table(acc_no char(1), primary_name char(50), amount int)
insert into @account select
1,'waterduck',10 union all select
2,'waterduck',20 union all select
3,'waterduck',30 union all select
4,'waterduck',40 union all select
5,'waterduck',60;
declare @relationship table(acc_no char(1), minor_name char(50), flag int)
insert into @relationship select
1,'waterduck',1 union all select
2,'waterduck',1 union all select
2,'waterduck mom',2 union all select
3,'waterduck',1 union all select
3,'waterduck mom',2 union all select
4,'waterduck',1 union all select
4,'waterduck mom',2 union all select
4,'waterduck dad',3 union all select
5,'waterduck',1 union all select
5,'waterduck dad',2;

/*
Heres the business rule
waterduck
- had 1 account(alone) ---- one entity
- had 2 account(with mom) ---- one entity
- had 1 account(with mom & dad) ---- one entity
- had 1 account(with dad) ---- one entity

@account store all the main holder of the account
@relationship store all the main & minor holder of the account, main holder flag = 1, minor holder flag = 2
*/

--current thought of query, expected output same with this query
select substring(name, 0, charindex('$',name)), SUM(amount)
from(
select
ltrim(rtrim(primary_name))+(select '$'+ltrim(rtrim(cast(minor_name as varchar))) from @relationship relationship where relationship.acc_no = account.acc_no for xml path('')) name
, amount
from @account account)a
group by name

so long din post anything

Hope can help...but advise to wait pros with confirmation...

waterduck
Aged Yak Warrior

982 Posts

Posted - 2010-08-11 : 13:03:09

woot, my question was...is there any better query/method to do this kind of grouping? any method will do BUT remind that both of this 2 table are more than 4m rows of records.

Hope can help...but advise to wait pros with confirmation...

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-11 : 13:39:20

your results are just


(No column name)	(No column name)
waterduck	10
waterduck	60
waterduck	50
waterduck	40

which I'm not sure what it means..what do you want as a result set?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

waterduck
Aged Yak Warrior

982 Posts

Posted - 2010-08-11 : 18:14:08

waterduck 10(account 1)
waterduck 60(account 2 & account 3)
waterduck 50(account 4)
waterduck 40(account 5)

i wanted same result as my query but i couldn't think a better way to select this "entity".

Hope can help...but advise to wait pros with confirmation...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources