Merging 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 2005 Forums

Transact-SQL (2005)

Merging rows (?)

Author

Topic

ch9862
Yak Posting Veteran

76 Posts

Posted - 2007-01-26 : 17:02:11

Here's an issue I need help with.

TableA has 3 fields:
ID, Rating (char(1)), RDate

ID identifies entity, Rating is it's rating, and RDate - date when
rating was performed. Some entities will have one record, some
multiple.

TableB has 2 fields:
ID, Ratings (char(2))

ID is the same ID as in TableA, Ratings contains two most recent
ratings (concatenated) for this particular ID. If there is only one
rating, I need to insert some predefined character in place of the other one.

In other words, for these four records in TableA:
1, A, 11/02/2006
2, C, 05/04/2005
2, B, 07/11/2003
2, D, 12/28/2004

I'd have these 2 records in TableB:
1, Ax (most recent rating A, no previous rating)
2, CD (most recent C, previous D)

How do I write the query to populate TableB?

TIA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-26 : 17:20:57

[code]-- prepare sample data
declare @sample table (id int, cat varchar(2), dt datetime)

insert @sample
select 1, 'A', '11/02/2006' union all
select 2, 'C', '05/04/2005' union all
select 2, 'B', '07/11/2003' union all
select 2, 'D', '12/28/2004'

-- show the result
select id,
min(case when seq = 1 then cat else 'x' end) + min(case when seq = 2 then cat else 'x' end) as ratings
from (
select s1.id,
s1.cat,
(select count(*) from @sample as s2 where s2.id = s1.id and s2.dt >= s1.dt) as seq
from @sample as s1
) as d
where seq < 3
group by id
order by id[/code]
Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-26 : 17:23:12

And with SQL Server 2005 syntax

-- prepare sample data
declare @sample table (id int, cat varchar(2), dt datetime)

insert	@sample
select	1, 'A', '11/02/2006' union all
select	2, 'C', '05/04/2005' union all
select	2, 'B', '07/11/2003' union all
select	2, 'D', '12/28/2004'

-- show the result
select		id,
		min(case when seq = 1 then cat else 'x' end) + min(case when seq = 2 then cat else 'x' end) as ratings
from		(
			select	s1.id,
				s1.cat,
				row_number() over (partition by id order by dt desc) as seq
			from	@sample as s1
		) as d
where		seq < 3
group by	id
order by	id

Peter Larsson
Helsingborg, Sweden

ch9862
Yak Posting Veteran

76 Posts

Posted - 2007-01-26 : 20:00:24

Thanks so much - it's very helpful.

Out of curiosity - is there a way to make the first version handle the same dates? What I mean is data like:
2, A, 12/03/2005
2, A, 12/03/2005
2, A, 12/03/2005

Thanks again for the help!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-27 : 04:00:13

[code]-- prepare sample data
declare @sample table (id int, cat varchar(2), dt datetime)

insert @sample
select 1, 'A', '11/02/2006' union all
select 2, 'C', '05/04/2005' union all
select 2, 'B', '07/11/2003' union all
select 2, 'D', '12/28/2004' union all
select 2, 'A', '12/03/2005' union all
select 2, 'A', '12/03/2005' union all
select 2, 'A', '12/03/2005'

-- show the result
select id,
min(case when seq = 1 then cat else 'x' end) + min(case when seq = 2 then cat else 'x' end) as ratings
from (
select distinct s1.id,
s1.cat,
(select count(distinct s2.cat) from @sample as s2 where s2.id = s1.id and s2.dt >= s1.dt) as seq
from @sample as s1
) as d
where seq < 3
group by id
order by id[/code]

Peter Larsson
Helsingborg, Sweden

ch9862
Yak Posting Veteran

76 Posts

Posted - 2007-01-27 : 10:30:14

Great, but what I meant was how to get two "most recent" records where there may be a bunch with the same date. It might not make sense in my case (the same entity is not going to be rated more then once at one time, esp. not differently rated), I'm only curious.

In other words what about:

-- prepare sample data
declare @sample table (id int, cat varchar(2), dt datetime)

insert	@sample
select	1, 'A', '11/02/2006' union all
select	2, 'B', '12/03/2005' union all
select	2, 'C', '12/03/2005' union all
select	2, 'A', '12/03/2005'

Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-28 : 04:41:42

Will your specs change more times?

-- prepare sample data
declare @sample table (id int, cat varchar(2), dt datetime)

insert	@sample
select	1, 'A', '11/02/2006' union all
select	2, 'B', '12/03/2005' union all
select	2, 'C', '12/03/2005' union all
select	2, 'A', '12/03/2005'

-- stage the data
declare @stage table (rowid int identity(1, 1), id int, cat varchar(2), dt datetime)

insert		@stage (id, cat, dt)
select		id,
		cat,
		dt
from		@sample
order by	id,
		dt desc,
		cat

-- show the output
select		d.id,
		s1.cat + isnull(nullif(s2.cat, s1.cat), 'x') as ratings
from		(
			select		id,
					min(rowid) as minrow,
					case when max(rowid) <= min(rowid) then min(rowid) else min(rowid) + 1 end as maxrow
			from		@stage
			group by	id
		) as d
inner join	@stage as s1 on s1.rowid = d.minrow
inner join	@stage as s2 on s2.rowid = d.maxrow

Peter Larsson
Helsingborg, Sweden

ch9862
Yak Posting Veteran

76 Posts

Posted - 2007-01-28 : 10:01:24

Thank you very much.

I apologize if I came across as a nitpicker. Your first answer fits my data very well, I don't have duplicate date/id records. I was simply curious - I kept asking myself 'what if..', but since I don't know SQL well, I couldn't come up with answers.

Your posts helped me learn something, and I'm grateful for it. Again - I'm sorry if my posts annoyed you.

Thanks!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-01-28 : 13:41:32

quote:
Originally posted by ch9862

Thank you very much.

I apologize if I came across as a nitpicker. Your first answer fits my data very well, I don't have duplicate date/id records. I was simply curious - I kept asking myself 'what if..', but since I don't know SQL well, I couldn't come up with answers.

Your posts helped me learn something, and I'm grateful for it. Again - I'm sorry if my posts annoyed you.

Thanks!

You didn't come across as a "nitpicker". You came across as someone who really didn't know what they want.

When someone answers your post and you reply with a followup that changes the requirements, it makes people think that you haven't bothered to think though the requirements.

Peter is the "Saint" because he's willing to help people when they do this. Most of us don't bother, because we see topics like that go on for many pages and find it extremely frustrating.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources