Retrieve data from several rows and write one row

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)

Retrieve data from several rows and write one row

Author

Topic

abbepierre94
Starting Member

2 Posts

Posted - 2014-01-25 : 15:53:39

Hello !

I'm face to a design issue. There is an input table with 5 fields.
This table contains every action that call-agents perform, like transfers and other actions. My goal is to design an SQL query (or script) that puts every transfer action in one row.
Here is the input table.

What do you suggest please ?
Thanks a lot !

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-01-26 : 23:32:05

try see if covers all your scenario

;with
cte as
(
	select	*, rn = row_number() over(partition by id_call order by id_sequence)
	from	input_table
)
select	c1.id_call,
	type_call	= c1.type_call,
	emitter_agent	= c1.agent,
	receiver_agent	= c3.agent,
	duration	= c2.duration
from	cte c1
	inner join cte c2	on	c1.id_call	= c2.id_call
				and	c1.rn		= c2.rn - 1
	inner join cte c3	on	c1.id_call	= c3.id_call
				and	c1.rn		= c3.rn - 2
where	c1.type_call	<> ''

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

abbepierre94
Starting Member

2 Posts

Posted - 2014-02-11 : 07:03:44

Thanks a lot, it works !!
Actually I should have thought about that way :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources