Doubt in Pivot

Question

Post reply

Doubt in Pivot

sabarish

SSC-Addicted

Points: 420
More actions
April 27, 2010 at 2:04 am

#97278

Hi Everyone ,

I have a table with data as shown below

UniqueId ParameterName ParameterValue

-------------------------------------------------------------

300 Parameter Name1 Parameter Value300_1

300 Parameter Name2 Parameter Value300_2

300 Parameter Name3 Parameter Value300_3

301 Parameter Name1 Parameter Value301_1

301 Parameter Name2 Parameter Value301_2

301 Parameter Name3 Parameter Value301_3

302 Parameter Name1 Parameter Value302_1

302 Parameter Name2 Parameter Value302_2

302 Parameter Name3 Parameter Value302_3

The datatype of the columns are

UniqueId - numeric(18, 0) , NOT NULL

ParameterName - varchar(50) NULL

ParameterValue - varchar(50) NULL

How do i write a pivot statement so that i can get the o/p as the following

UniqueId Parameter Name1 Parameter Name2 Parameter Name2

------------------------------------------------------------------------------

300 Parameter Value300_1 Parameter Value300_2 Parameter Value300_3

301 Parameter Value301_1 Parameter Value301_2 Parameter Value301_3

302 Parameter Value302_1 Parameter Value302_2 Parameter Value302_3

Regards

Sabarish

wwewew

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

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

You might want to have a look at the CrossTab article referenced in my signature. This will show you an alternative to the PIVOT statement. If you need to display a varying number of columns you might want to continue with reading DynamicCrossTabs (also referenced in my signature).

And, yet another option would be to do the pivoting in the presentation layer. ReportingServices does a good job with that sort of requirements...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 2

Like this:



select

    UniqueId,

    max(case when ParameterName = 'Parameter Name1' then ParameterValue else null end) as 'Paramater Name1',

    max(case when ParameterName = 'Parameter Name2' then ParameterValue else null end) as 'Paramater Name2',

    max(case when ParameterName = 'Parameter Name3' then ParameterValue else null end) as 'Paramater Name3'

from

    dbo.MyTable

group by

    UniqueId

order by

    UniqueId;

sabarish SSC-Addicted Points: 420 More actions · Answer 3

sabarish

SSC-Addicted

Points: 420

April 27, 2010 at 8:29 pm

#1158620

Thanks for the reply... will try this and get back

wwewew

Maddy...! SSC Eights! Points: 995 More actions · Answer 4

Hi.,

if u really want try pivot property in sql server then jst try this.............

select * from

(

select unique_id,parameter_name,parameter_value from dbo.MyTable

) ttbl

PIVOT

(

max(parameter_value) --in case of max u can use sum,avg any aggregate function

for parameter_name in ([Parameter Name1],[Parameter Name2],[Parameter Name3])

) AS PIVOT_TBL

give me

sabarish SSC-Addicted Points: 420 More actions · Answer 5

sabarish

SSC-Addicted

Points: 420

April 30, 2010 at 3:05 am

#1160269

Thanks , both the cross tab and pivot worked

wwewew