Two rows from table merged into one

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 2000 Forums

Transact-SQL (2000)

Two rows from table merged into one

Author

Topic

jduncan
Starting Member

2 Posts

Posted - 2009-02-23 : 15:41:32

My apologies if this has been asked and answered before, but I've been racking my brain for Anyway to do this in SQL instead of an external application...

I have a table that basically consists of audit information.
As an example:
table tracking ( id varchar(255), act varchar(3), A varchar, B varchar, C varchar ...)

The 'id' is unique to TWO rows. There will only be either 2 or zero rows with an 'id'.
Of the two rows with the same 'id', one will have act = 'NEW', the other act = 'OLD'
The rest of the fields vary, but for this example generic strings are fine, as long as it is understood this data may be the same, different, nulled, anything between the two rows with the same 'id'

In the end, what I would like is a stored procedure, or user defined function, or even a view, which when run on the whole table, ends up returning a single row for each pair of rows per 'id'

If the original table has the following rows:
1, 'OLD', 'aaa','bbb','ccc'
1, 'NEW', 'aaa','bbb','ddd'
2, 'OLD', 'xxx','yyy','zzz'
2, 'NEW', 'aaa','yyy','zzz'

The output should be along the lines of:

id = '1', old_a = 'aaa', new_a = 'aaa', ..., old_c = 'ccc', new_c = 'zzz'
id = '2', old_a = 'xxx', new_a = 'aaa', ..., old_c = 'ccc', new_c = 'zzz'

Between stored procedures being able to make temp tables but not return them, and user functions being able to return tables but seemingly unable to build them dynamically in anyway, I don't see any way to do this short of an external program (gack!)

Any suggestions or ideas?

Thanks -- Jon

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-23 : 15:58:09

declare @t table (id int, act char(20), a char(10),b char(10))
insert @t
select 1, 'OLD', 'AAA','ccc' union all
select 1, 'NEW', 'AAA','ccc' union all
select 2, 'NEW', 'BBB','ddd' union all
select 2, 'OLD', 'BBB','ddd'

SELECT id,
MAX(CASE WHEN act = 'OLD' THEN a ELSE '' END) AS old_a,
MAX(CASE WHEN act = 'OLD' THEN b ELSE '' END) AS old_b,
MAX(CASE WHEN act = 'NEW' THEN a ELSE '' END) AS new_a,
MAX(CASE WHEN act = 'NEW' THEN b ELSE '' END) AS new_b
from @t
GROUP BY id

Result
1 AAA ccc AAA ccc
2 BBB ddd BBB ddd

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-02-23 : 16:44:44

[code]
declare @t table
(id int, act varchar(3), a varchar(10),b varchar(10),
primary key clustered ([id],act))

insert @t
select 1, 'old', 'aa1','cc1' union all
select 1, 'new', 'aa2','cc3' union all
select 2, 'new', 'bb2','dd3' union all
select 2, 'old', 'bb1','dd1'

select
a.id,
old_a = a.a,
new_a = b.a,
old_b = a.b,
new_b = b.b
from
@t a
join
@t b
on
a.id = b.id and
a.act = 'old' and
b.act = 'new'
order by
a.id

Results:

id old_a new_a old_b new_b
----------- ---------- ---------- ---------- ----------
1 aa1 aa2 cc1 cc3
2 bb1 bb2 dd1 dd3

(2 row(s) affected)

[/code]

CODO ERGO SUM

jduncan
Starting Member

2 Posts

Posted - 2009-02-26 : 09:20:45

Thank you! Worked like a charm

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources