pivoting a simple table

Question

pivoting a simple table

vchimakurthi

Newbie

Points: 3
More actions
January 5, 2012 at 5:44 am

#265901

hi,
iam trying to pivot my table.
Cycles Cycle1394
Business Objective test1394
Background bb394
Out Of Scope nn394
Acceptance Criteria394
Solution 394
Work Package394
i need to have like
linkid Cycles BusinessObjectivies BackGround ...
394 cycles1 test1 bb
the columns are dymanic so i use dynamic sql
declare @collist as varchar(max)
select @collist =
stuff(
(
select distinct ',['+vcparametername+']'
from ReportParameter where tiObjectType=2
for xml path('')
),1,1,'')
select @collist
declare @dq as varchar(max)
set @dq='select ilinkid,'+@collist +'from (
select ilinkid,vcparametername,vcValue from ReportParameter rp
inner join ReportParameterValue rv on rp.iReportParameterID=rv.iReportParameterID
where tiObjectType=2) as s
pivot
(
vcValue for vcParameterName in('+@collist+')
)as p'
exec @dq
its with execution error invalid identifier
how can i achieve this

Viewing 3 posts - 1 through 2 (of 2 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

i tried to help and started scripting, but you simply didn't provide enough data.

you originally posted sample data with two columns...i had to dig thru your post to figure out the column names of vcparametername and linkid.

later in the post, you mention two other columns, not in the data ,tiObjectType, vcValue and a third column that might just be misspelled ilinkid

you didn't provide enough testable code that we can recreate your error, nor identify any fixes to help with.

i tried building a CTE/temp table, but without a lot of the missing pieces, noone will be able to help you.

post a CREATE TABLE and INSERT INTO script that has representative data you want to pivot.

this is what i had before i gave up.

With ReportParameter (vcparametername,linkid,tiObjectType)

AS

(

SELECT 'Cycles Cycle1','394',2 UNION ALL

SELECT 'Business Objective test1','394',2 UNION ALL

SELECT 'Background bb','394',2 UNION ALL

SELECT 'Out Of Scope nn','394',2 UNION ALL

SELECT 'Acceptance Criteria','394',2 UNION ALL

SELECT 'Solution','394',2 UNION ALL

SELECT 'Work Package','394',2 )

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ashley.wardell SSC Eights! Points: 983 More actions · Answer 2

Hi there mate

You need and aggregator around your vcValue column I think here. Example MAX()

Imagine you had two rows with the same paramter name. SQL need to know how to deal with this.

If you could supply your create table statements it would be easier to work on.