Best way to transpose multiple rows onto 1 row?

Question

Post reply

Best way to transpose multiple rows onto 1 row?

derrysql

Old Hand

Points: 308
More actions
March 26, 2010 at 5:08 am

#218502

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

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150467 More actions · Answer 1

Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs[/url]

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

Always post table defintion , index definition and sql code to get response faster

and also spend some time with the link For Quick Result in my signature.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Paul White SSC Guru Points: 150467 More actions · Answer 3

Bhuvnesh (3/26/2010)
Always post table defintion , index definition and sql code to get response faster
and also spend some time with the link For Quick Result in my signature.

Well that's always nice of course, but there was enough detail in the original post to see that a dynamic pivot is required, hence the link to Jeff's articles.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

derrysql Old Hand Points: 308 More actions · Answer 4

Hi al,

Below is ddl for above example table.

Thanks,

rg

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

Can you post required columns for staffno for 1234 so that i can get rough idea/hint ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

derrysql Old Hand Points: 308 More actions · Answer 6

Bhuvnesh (3/29/2010)
Can you post required columns for staffno for 1234 so that i can get rough idea/hint ?

Hi Bhuvnesh,

The result set would look like

StaffNo Name StartDate EndDate Salary StartDate EndDate Salary

1234 Tom 01/04/1982 01/06/1992 20000 02/06/1992 02/06/1998 25000

(In case above Tom would have another StartDate EndDate Salary listed for his third band but do not have space to put it here.)

I do not know in advance how many records each staff member has.

Thanks,

rg

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 7

derrysql (3/29/2010)
StaffNo Name StartDate EndDate Salary StartDate EndDate Salary

first of all we cant have two columns with the same name.So looks like we need to play hard with data. means first we need to set different columns with diff name then playing with data.it might results in bad approach or badly represented report(result).

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Paul White SSC Guru Points: 150467 More actions · Answer 8

Bhuvnesh (3/29/2010)
first of all we cant have two columns with the same name.

Really? You sure? 😉

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 9

Paul White NZ (3/29/2010)
Bhuvnesh (3/29/2010)
first of all we cant have two columns with the same name.
Really? You sure? 😉

Sorry i forgot to considered alias 😀 or something else ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 10

What I want to know is why this particular denormalization of perfectly good data is required? 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

K Cline Ten Centuries Points: 1391 More actions · Answer 11

All questions of proper naming and data modeling aside...

This is probably not the most elegant solution, but it should work (assuming I understood the initial post correctly). I'm sure someone else can build off of it and make some improvements. Uses the assistance of a tally table to create the repeater effect. Since I didn't know the name of your source table I arbitrarily called it "SalaryHistory". Outputs data with repeating field names as was originally posted.

Declare @Sql NVarChar(MAX),

@MaxCount Int;

-- Find the maximum number of repeated values.

Select @MaxCount = MAX(StaffCount)

From

(

Select COUNT(StaffNo) As StaffCount

From dbo.SalaryHistory

Group By StaffNo

) SubQuery;

-- Build the query string.

Set @Sql = N'Select StaffNo, [Name]'

-- This is the section that gives you the repeater.

+ CAST(

(

Select N', MAX(Case RowNumber When '

+ CAST(N As NVarChar)

+ N' Then StartDate End) As [StartDate], MAX(Case RowNumber When '

+ CAST(N As NVarChar)

+ N' Then EndDate End) As [EndDate], SUM(Case RowNumber When '

+ CAST(N As NVarChar)

+ N' Then Salary End) As [Salary] '

From dbo.Tally

Where (N <= @MaxCount)

Order By N

For XML Path('')

) As NVarChar(MAX))

-- Selection criteria.

+ N'

From

(

Select ROW_NUMBER() Over (Partition By StaffNo Order By StaffNo, StartDate) As [RowNumber],

StaffNo, [Name], StartDate, EndDate, Salary

From dbo.SalaryHistory

) As SalaryHistory

Group By StaffNo, [Name]

Order By StaffNo; ';

Execute sp_executesql @Sql;

Print @Sql;

Hope this helps. Best of luck.

derrysql Old Hand Points: 308 More actions · Answer 12

K Cline (3/29/2010)
Uses the assistance of a tally table to create the repeater effect.

Hi,

Thank you for this. What sort of structure would the 'Tally' table have?

rg

Paul White SSC Guru Points: 150467 More actions · Answer 13

derrysql (3/30/2010)
Thank you for this. What sort of structure would the 'Tally' table have?

Details here: http://www.sqlservercentral.com/articles/67899/

Notice: same author (Jeff Moden) as the Dynamic Pivot article I linked earlier

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 14

Paul White NZ (3/30/2010)
derrysql (3/30/2010)
Thank you for this. What sort of structure would the 'Tally' table have?
Details here: http://www.sqlservercentral.com/articles/67899/
Notice: same author (Jeff Moden) as the Dynamic Pivot article I linked earlier

Actually... that's Lynn's article on Dynamic Tally Tables.

DerrySql,

Lynn's article is great. If you want to know how a Tally table works to replace certain loops with a high performance set-based solution, take a look at the following article. It does a one-to-one comparison between a While Loop and a Tally table.

http://www.sqlservercentral.com/articles/T-SQL/62867/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)