Solved

Please help on PIVOT Query in SQL 2005

Posted on 2009-07-09
4
221 Views
Last Modified: 2012-05-07
Hello,

I need to turn data columns into data a row using PIVOT query in SQL 2005 or crosstab query? Please see attached image for more information. Thank you very much
MWSnap122.jpg
0
Comment
Question by:ixcorp
  • 2
  • 2
4 Comments
 
LVL 41

Expert Comment

by:ralmada
ID: 24817451
Are the specs and hearder texts fixed values? If so you can try something like this:

with CTE as (

select PartNumber, cast(Spec as varchar) as tcolumn

from yourtable

union all

select PartNumber, HeaderText as tcolumn

from yourtable

)
 

select Partnumber,

	[100] as Spec1,

	[60] as Spec2,

	...

from

(select * from CTE) o

pivot (max(tcolumn) for tcolumn in ([100], [60], [0.017], ... [VDSS,max,(V)]...)p

Open in new window

0
 

Author Comment

by:ixcorp
ID: 24817834
Hi ralmada,

Thanks for quick response. Unfortunately the specs and header texts are not fixed values. I should have clarify this in my question. See attached image for more information.

Thanks,
Dat
MWSnap123.jpg
0
 
LVL 41

Accepted Solution

by:
ralmada earned 500 total points
ID: 24818012
What about something like this then?

with CTE as (

select PartNumber, cast(Spec as varchar) as tcolumn, 'spec' + cast(row_number() over (partition by PartNumber order by PartNumber) as varchar) as rn

from yourtable

union all

select PartNumber, cast(HeaderText) as tcolumn, 'header' + cast(row_number() over (partition by PartNumber order by PartNumber) as varchar) as rn

from yourtable

)
 

select 	PartNumber,

	[spec1],

	[spec2],

	...

	[header1],

	...

from

(select * from CTE) o

pivot (max(tcolumn) for rn in ([spec1], [spec2], .... , [header1], [header2], ....)p

Open in new window

0
 

Author Closing Comment

by:ixcorp
ID: 31601779
Working perfectly. Thank you very much!
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server agent & maintenance task issue ? 8 65
passing parameter in sql procedure 9 56
Problem with SqlConnection 4 156
How to simplify my SQL statement? 14 50
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

937 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now