Doubt in Pivot

Question

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
------------------------------------------------------------------------------
300Parameter Value300_1 Parameter Value300_2 Parameter Value300_3
301Parameter Value301_1 Parameter Value301_2 Parameter Value301_3
302Parameter 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: 442467 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

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

Thanks , both the cross tab and pivot worked

wwewew