Post reply

pivoting a simple table

  • January 5, 2012 at 5:44 am

    #265901

    hi,

    iam trying to pivot my table.

    Cycles Cycle1 394

    Business Objective test1 394

    Background bb 394

    Out Of Scope nn 394

    Acceptance Criteria 394

    Solution 394

    Work Package 394

    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

  • January 5, 2012 at 7:37 am

    #1429016

    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!

  • January 5, 2012 at 9:08 am

    #1429092

    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.

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

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