Joint rows - 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 2008 Forums

Transact-SQL (2008)

Joint rows

Author

Topic

kshoberalb
Starting Member

2 Posts

Posted - 2014-01-31 : 15:57:48

I have a Transaction table view that returns transaction rows for each id summed to columns. Some transaction rows are joint transactions and usually split equally between two ids. Each joint row has the corresponding id number in the jointid column.

Id Summary1 Summary2 Jointid
A1 100.00 200.00 B2
B2 100.00 200.00 A1
C3 100.00 100.00 NULL

I need to sum the two joint row summary columns where the id = jointid but only return one row. It is not important which row is returned.

The easiest way would be to create a unique id common to both rows and use row_number() over (partition by) and select row 1, but I cannot find a common unique id.

Any suggestions would be appreciated.

kshoberalb

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-01-31 : 18:55:23

I'n not sure if I really understand your requirements but hopefully, this will get you started:[CODE]declare @SumTbl table (
Id varchar(2),
Summary1 money,
Summary2 money,
Jointid varchar(2)
)

insert into @SumTbl (
Id, Summary1, Summary2, Jointid
) values
('A1', 100.00, 200.00, 'B2'),
('B2', 100.00, 200.00, 'A1'),
('C3', 100.00, 100.00, NULL)

--/**/select * from @SumTbl

select --*
t.Id,
sum(t.Summary1) totSummary1,
sum(t.Summary2) totSummary2
from (
select
a.id,
a.Summary1,
a.Summary2
from
@SumTbl a
where
a.Id < a.jointid
or a.Jointid is null

--------------------
union all
--------------------

select
a2.jointid,
a2.Summary1,
a2.Summary2
from
@SumTbl a1
inner join
@SumTbl a2
on a1.Id = a2.Jointid
and a2.Id = a1.Jointid
and a1.Id < a2.id
) t
group by
t.Id
order by
t.Id[/CODE]

===============================================================================
There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber (1894-1961)

kshoberalb
Starting Member

2 Posts

Posted - 2014-02-01 : 09:51:13

Thanks for the quick response. I noticed that you mentioned that you were not sure what I am looking for and you are correct. I did not do a very good job of explaining what I am attempting to do.

I would like to simply find the paired rows and return just one row from each pair. All rows with a Null in the jointid column are not paired and should remain as is.

The table contains thousands of paired rows with each row having the paired row's ID number in the jointid column. I am able to find each pair individually as I illustrated, but I cannot figure out how to eliminate one of the rows. The techniques needs to be applied to the entire table.

Id Jointid
A B
B A

Isolate all of the paired rows (should only be two each) and eliminate one of the rows.

Id Jointid
A B

or (the single row returned is not important)

B A

Regards

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-03 : 04:20:25

[code]
SELECT *
FROM Table t
WHERE NOT EXISTS (SELECT 1
FROM Table
WHERE Jointid = t.id
AND id = t.Jointid
)
OR t.id < t.Jointid
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources