Best way to transpose multiple rows onto 1 row?

Question

Post reply

Best way to transpose multiple rows onto 1 row?

Viewing 9 posts - 16 through 23 (of 23 total)

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

Carolyn Richardson SSCrazy Eights Points: 8357 More actions · Answer 1

You could play around with this to make it more readable but I think it does display the data in the format you want.

SELECT DISTINCT

A.[StaffNo],

A.[Name],

STUFF(( SELECT ' | ' + StartDate +' | ' + EndDate +' | ' + CAST(Salary as VARCHAR (15))

FROM #tempsalary B

WHERE B.[StaffNo] = A.[StaffNo]

FOR XML PATH('') ), 2, 1, '') As Detail

FROM #tempsalary AS A

JOIN #tempsalary B ON B.[StaffNo] = A.[StaffNo]

Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists[/url]

Carolyn Richardson SSCrazy Eights Points: 8357 More actions · Answer 2

Duplicate deleted

Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists[/url]

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

Carolyn Richardson (3/30/2010)
You could play around with this to make it more readable but I think it does display the data in the format you want.
SELECT DISTINCT
A.[StaffNo],
A.[Name],
STUFF(( SELECT ' | ' + StartDate +' | ' + EndDate +' | ' + CAST(Salary as VARCHAR (15))
FROM #tempsalary B
WHERE B.[StaffNo] = A.[StaffNo]
FOR XML PATH('') ), 2, 1, '') As Detail
FROM #tempsalary AS A
JOIN #tempsalary B ON B.[StaffNo] = A.[StaffNo]

great answer

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

i replaced "| |" with | (single)

SELECT DISTINCT

A.[StaffNo],

A.[Name]

FROM tempsalary AS A

JOIN tempsalary B ON B.[StaffNo] = A.[StaffNo]

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

There are some good things here, including some stuff on dynamic crosstabs: http://www.sommarskog.se/dynamic_sql.html

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

steve-893342 SSCarpal Tunnel Points: 4439 More actions · Answer 6

An alternative approach is to concatenate the StartDate, EndDate, Salary details into one block and then to PIVOT the data based on this concatenated block. Then in the final SELECT statement, the StartDate, EndDate, Salary components can be resolved by simple string manipulation

IF NOT OBJECT_ID('tempdb.dbo.#tempsalary') IS NULL DROP TABLE #tempsalary

SELECT 1234 [StaffNo], 'Tom' [Name], '01/04/1982' [StartDate], '01/06/1992' [EndDate], 20000 [Salary] INTO #tempsalary 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]

;

DECLARE @DetailCount INT

DECLARE @SQL NVARCHAR(MAX)

DECLARE @DetailList VARCHAR(MAX)

DECLARE @DetailSplit VARCHAR(MAX)

DECLARE @i INT

DECLARE @ivarchar VARCHAR

SELECT @DetailCount = MAX(CT) FROM (SELECT COUNT(*) AS CT FROM #tempsalary GROUP BY StaffNo) AS Z

SET @DetailList = ''

SET @DetailSplit = ''

SET @i = 1

WHILE @i <= @DetailCount

BEGIN

SET @ivarchar = CONVERT(VARCHAR, @i)

SET @DetailList = @DetailList + '[' + @ivarchar + '],'

SET @DetailSplit = @DetailSplit + 'LEFT([' + @ivarchar + '], CHARINDEX(''|'', [' + @ivarchar + ']) - 1) AS StartDate' + @ivarchar + ',' + CHAR(13) + CHAR(10) +

'SUBSTRING([' + @ivarchar + '], CHARINDEX(''|'', [' + @ivarchar + ']) + 1, CHARINDEX(''|'', [' + @ivarchar + '], CHARINDEX(''|'', [' + @ivarchar + ']) + 1) - CHARINDEX(''|'', [' + @ivarchar + ']) - 1) AS EndDate' + @ivarchar + ',' + CHAR(13) + CHAR(10) +

'REVERSE(LEFT(REVERSE([' + @ivarchar + ']), CHARINDEX(''|'', REVERSE([' + @ivarchar + '])) - 1)) AS Salary' + @ivarchar + ',' + CHAR(13) + CHAR(10)

SET @i = @i + 1

END

SET @DetailList = LEFT(@DetailList, LEN(@DetailList) - 1)

SET @DetailSplit = LEFT(@DetailSplit, LEN(@DetailSplit) - 3)

SET @SQL =

'

;

WITH

tempsalarycombine AS

(

SELECT ROW_NUMBER() OVER (PARTITION BY StaffNo ORDER BY StartDate) AS ROW,

StaffNo, Name, StartDate + ''|'' + EndDate + ''|'' + CONVERT(VARCHAR, Salary) AS Details FROM #tempsalary

)

,

ctePivot AS

(

SELECT *

FROM tempsalarycombine

PIVOT

(

MAX(Details) FOR ROW IN

(

' + @DetailList + '

)

AS pvt

)

SELECT StaffNo, Name,

' + @DetailSplit + '

FROM ctePivot

'

EXEC SP_EXECUTESQL @SQL, N'@innerDetailList VARCHAR(MAX), @innerDetailSplit VARCHAR(MAX)', @DetailList, @DetailSplit

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

Jeff Moden (3/30/2010)
Actually... that's Lynn's article on Dynamic Tally Tables.

How odd. I distinctly remember looking for yours at the time. Not sure I have even read Lynn's.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

derrysql Old Hand Points: 308 More actions · Answer 8

Thanks to everyone who responded to my question.

With the various replies I was able to obtain what I required.

rg

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

derrysql (3/31/2010)
Thanks to everyone who responded to my question.
With the various replies I was able to obtain what I required.
rg

Very cool... just one thing left for you to do... post the code you came up with so we can "learn back". 😉

--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)