| 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 |
|
|
|
|
|
|
Transforming rows to Columns with PIVOT