Merging Like 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

General SQL Server Forums

New to SQL Server Programming

Merging Like Rows

Author

Topic

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2008-01-20 : 01:30:24

Hi All,

I have a query that I'm working on, but instead of giving the query, I wanted to ask a basic syntax question. If more info is needed, let me know. If you have 2 rows that have a common relationship, but differing information in some fields, can you merge them all onto one row? I've done this with Sum(case) expressions, but I don't want to 'add' anything. In the following example, the ActivityID refers to a break. ActivityID can be:

0=Pick up
1=Drop Off
2=Lunch
3=Break

So if I wanted to see 2 breaks on 1 row in the following example, would this be possible:

Veh ActID ArrTime DepTime
1 3 7:00 8:00
1 3 10:00 11:00

Veh ActID ArrTime DepTime ArrTime DepTime
1 3 7:00 8:00 10:00 11:00

Thanks in advance for your help!

Craig

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2008-01-20 : 01:55:39

I should add that all the fields come from the same table

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-20 : 01:59:44

Are you using SQL Server 2000 or 2005 ?

KH
[spoiler]Time is always against us[/spoiler]

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2008-01-20 : 02:06:59

Our agency just converted to 2005, though I use an interface that is fairly limiting as I'm not in "IT"...it just allows me to do select statements and some of the syntax is a bit different than what I see our IT Supervisor use, but for all intents and purposes, yes, it's 2005

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-20 : 02:20:37

[code]DECLARE @sample TABLE
(
Veh int,
ActID int,
ArrTime datetime,
DepTime datetime
)
INSERT INTO @sample
SELECT 1, 3, '07:00', '08:00' UNION ALL
SELECT 1, 3, '10:00', '11:00'

SELECT Veh, ActID,
ArrTime1 = MAX(CASE WHEN row_no = 1 THEN ArrTime END),
DepTime1 = MAX(CASE WHEN row_no = 1 THEN DepTime END),
ArrTime2 = MAX(CASE WHEN row_no = 2 THEN ArrTime END),
DepTime2 = MAX(CASE WHEN row_no = 2 THEN DepTime END)
FROM (
SELECT Veh, ActID, ArrTime, DepTime,
row_no = row_number() OVER (PARTITION BY Veh, ActID ORDER BY Veh, ActID, ArrTime)
FROM @sample
) d
GROUP BY Veh, ActID[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources