pivot problem

Question

Post reply

pivot problem

vick.ram79

SSCommitted

Points: 1637
More actions
June 26, 2009 at 10:35 am

#201846

Hi gurus,
I am trying to put data in a pivot but end up getting additional rows ... Is there a way to fix this?
Here is the table generation script:
create table #foo (
batchid int
, paramName varchar(50)
, paramValue varchar(50)
)
insert into #foo(batchid, paramName, paramValue)
select 1, 'outfile', 'file1.log' union all
select 2, 'outfile', 'file2.log' union all
select 2, 'outfolder', 'C:\temp'
select * from #foo
Now I want to pivot it so I write the following:
Select
batchID As BatchID
, Case When paramName = 'outfolder' Then paramValue
Else NULL
End As OutFileLocation
, Case When paramName = 'outfile' Then paramValue
Else NULL
End As OutFileName
From #foo
I get the result:
batchid OutFileLocation OutFileName
1 NULL file1.log
2 NULL file2.log
2 C:\temp NULL
But this is not the effect that I want.
BatchID 1 is fine, but I want rows for BatchId 2 to appear in the same row (instead of 2 different rows), since they belong to the same batchid...
I know why its appearing twice, I just dont know how to coalsce the 2 rows for batchid=2, into 1.
How To Post[/url]

Viewing 6 posts - 1 through 5 (of 5 total)

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

vick.ram79 SSCommitted Points: 1637 More actions · Answer 1

I must be feeling the friday effect .... for those interested , here is the fix:

select batchID, [outfolder], [outfile]

from (Select batchID, paramName, paramValue From #foo) tmp

Pivot

(Max(paramvalue)

for paramName in (outfolder, outfile)

) As pt

How To Post[/url]

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

vick.ram79 (6/26/2009)
I must be feeling the friday effect .... for those interested , here is the fix:
select batchID, [outfolder], [outfile]
from (Select batchID, paramName, paramValue From #foo) tmp
Pivot
(Max(paramvalue)
for paramName in (outfolder, outfile)
) As pt

Relatively speaking, PIVOTs are a bit slow. Take a gander at the following article... there are some performance test results at the bottom of the article. For mor complicated pivots, you may actually find that good ol' cross-tabs are actually faster.

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

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

vick.ram79 SSCommitted Points: 1637 More actions · Answer 3

Thanks Jeff...

I modified the code...

For those interested, it reads thus:

Select

batchID As BatchID

, Min(Case When paramName = 'outfolder' Then paramValue Else NULL End) As OutFileLocation

, Min(Case When paramName = 'outfile' Then paramValue Else NULL End) As OutFileName

From #foo

Group By batchID

How To Post[/url]

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

Thanks for the feedback, Vick. If you want that to fly, convert it to a bit of pre-aggregation. Like this...

[font="Courier New"];WITH

ctePreAgg AS

(

SELECT BatchID, ParamName, MIN(ParamValue) AS MinParamValue

FROM #Foo

GROUP BY BatchID, ParamName

)

SELECT BatchID,

MIN(CASE WHEN ParamName = 'outfolder' THEN ParamValue ELSE NULL END) AS OutFileLocation

MIN(CASE WHEN ParamName = 'outfile' THEN ParamValue ELSE NULL END) AS OutFileName

FROM ctePreAgg

GROUP BY BatchID[/font]

Put an index on BatchID, ParamName with an "INCLUDE" on ParamValue to make it a bit faster, still.

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

vick.ram79 SSCommitted Points: 1637 More actions · Answer 5

Nice ... I like squeezing the last drop of juice from my machine too 🙂

How To Post[/url]