Convert multiple rows onto 1 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 2005 Forums

Transact-SQL (2005)

Convert multiple rows onto 1 row

Author

Topic

opensourcederry
Starting Member

12 Posts

Posted - 2010-03-29 : 07:54:08

Hi all,

SQL 2005.

I imagine I have the following simplified data set/table which shows the history trail of an employees salary. A blank end date idicates it is the current salary.

Staff No Name StartDate End Date Salary
1234 Tom 01/04/1982 01/6/1992 £20,000
1234 Tom 02/06/1992 02/06/1998 £25,000
1234 Tom 03/06/1998 £35,000
9867 Dick 01/03/2005 £23,000
5678 Harry 23/04/2004 25/06/2006 £18,000
5678 Harry 26/06/2006

What I need to do is to have the details for each employee returned on one row each as per below

Staff No Name Start Date End Date Salary Start Date End Date Salary...and so on
1234 Tom ...all salary detail
9867 Dick ...all salary detail
5678 Harry ...all salary detail

The main issue is that I do not know in advance how many rows each employee has. A new employee may have one row whereas a long timer who has been employed for 20 years will have numerous rows indicating the history. So staff no and name will be 'static' while start date, end date and salary will need to be repeated on one line for each salary band the employee has had.

Any ideas on how I can achieve this?

Thanks,

rg

(ddl for above sample table below)

WITH tempsalary AS
(
SELECT 1234 [StaffNo], 'Tom' [Name], '01/04/1982' [StartDate], '01/06/1992' [EndDate], 20000 [Salary] UNION ALL
SELECT 1234 [StaffNo], 'Tom' [Name], '02/06/1992' [StartDate], '02/06/1998' [EndDate], 25000 [Salary] UNION ALL
SELECT 1234 [StaffNo], 'Tom' [Name], '03/06/1992' [StartDate], '' [EndDate], 35000 [Salary] UNION ALL
SELECT 9867 [StaffNo], 'Dick' [Name], '01/03/2005' [StartDate], '' [EndDate], 23000 [Salary] UNION ALL
SELECT 5678 [StaffNo], 'Harry' [Name], '23/04/2004' [StartDate], '25/06/2006' [EndDate], 18000 [Salary] UNION ALL
SELECT 5678 [StaffNo], 'Harry' [Name], '26/06/2006' [StartDate], '' [EndDate],19000 [Salary]
)

select * from tempsalary

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-29 : 08:25:00

Best to do it in your application. Row concatenation is trivially easy there (and fast).

There are approaches in sql but they all have their disadvantages.

Look for DYNAMIC PIVOT here.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-29 : 08:26:06

In particular:
http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables

has a nice introduction.

Again -- MUCH EASIER IN an application.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-29 : 08:40:51

or refer

http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources