Transforming rows to Columns with PIVOT

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)

Transforming rows to Columns with PIVOT

Author

Topic

phrankbooth
Posting Yak Master

162 Posts

Posted - 2014-10-23 : 17:42:11

Hi,

I have this set of rows:

Name, StringValue
EstimatedSecondsToCompletion, 2
OwnerAccount, 123
CurrentBackupRateInMegaBytesPerSecond, 500
ActualIncrementalBackupSizeInMegaBytes, 200
NumberOfNodes, 3
Status, Y
EstimatedSecondsToCompletion, 1
OwnerAccount, 4566
CurrentBackupRateInMegaBytesPerSecond, 457
ActualIncrementalBackupSizeInMegaBytes, 234
NumberOfNodes, 4
Status, Y

I'd like my result table to look like this:
EstimatedSecondsToCompletion, OwnerAccount, CurrentBackupRateInMegaBytesPerSecond, ActualIncrementalBackupSizeInMegaBytes, NumberOfNodes, Status
2, 123, 500, 200, 3, Y
1, 4566, 457, 234, 4, Y

I tried the PIVOT query pattern but it's placing one value per row then the rest are nulls. Like this:

2, NULL, NULL, NULL, NULL, NULL
NULL, 123, NULL, NULL, NULL, NULL
NULL, NULL, 500, NUll, NULL, NULL

etc...

using this:

select
pvt.[EstimatedSecondsToCompletion]
,pvt.[OwnerAccount]
,pvt.[CurrentBackupRateInMegaBytesPerSecond]
,pvt.[ActualIncrementalBackupSizeInMegaBytes]
,pvt.[NumberOfNodes]
,pvt.[Status]
from SourceTable
pivot (
min(StringValue)
for Name in
(
[EstimatedSecondsToCompletion]
,[OwnerAccount]
,[CurrentBackupRateInMegaBytesPerSecond]
,[ActualIncrementalBackupSizeInMegaBytes]
,[NumberOfNodes]
,[Status]
)
) pvt

Thanks!

--PhB

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-10-24 : 02:40:57

change this "from SourceTable"

to


FROM
(
    SELECT Name, StringValue
    FROM   SourceTable
) s

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

AASC
Starting Member

24 Posts

Posted - 2014-10-24 : 03:01:32

@phrankbooth this may help you.

create Table #SourceTable
(
ID Int identity(1,1),----------------- Replace ID Column With Unique Sequence number of your Source Table
Name Varchar(500),
StringValue Varchar(500)
)

insert into #SourceTable
select 'EstimatedSecondsToCompletion', '2'
Union all
select 'OwnerAccount', '123'
Union all
select 'CurrentBackupRateInMegaBytesPerSecond', '500'
Union all
select 'ActualIncrementalBackupSizeInMegaBytes', '200'
Union all
select 'NumberOfNodes', '3'
Union all
select 'Status', 'Y'
Union all
select 'EstimatedSecondsToCompletion', '1'
Union all
select 'OwnerAccount', '4566'
Union all
select 'CurrentBackupRateInMegaBytesPerSecond', '457'
Union all
select 'ActualIncrementalBackupSizeInMegaBytes', '234'
Union all
select 'NumberOfNodes', '4'
Union all
select 'Status', 'Y'

;WITH T AS
(
SELECT row_number()over (partition by Name order by ID) RID,Name, StringValue
FROM #SourceTable
)
SELECT [EstimatedSecondsToCompletion],[OwnerAccount],[CurrentBackupRateInMegaBytesPerSecond],[ActualIncrementalBackupSizeInMegaBytes]
,[NumberOfNodes],[Status]
FROM T
PIVOT ( MAX(stringValue)
FOR Name in
(
[EstimatedSecondsToCompletion]
,[OwnerAccount]
,[CurrentBackupRateInMegaBytesPerSecond]
,[ActualIncrementalBackupSizeInMegaBytes]
,[NumberOfNodes]
,[Status])

) P

drop table #SourceTable

phrankbooth
Posting Yak Master

162 Posts

Posted - 2014-10-24 : 13:05:47

This works, Thanks!!

--PhB

phrankbooth
Posting Yak Master

162 Posts

Posted - 2014-10-24 : 13:06:12

khtan, This only returns 1 row.

--PhB

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources