pivot tablehelp

  • Hi Friends,

    I need a help in constructing a query for pivot a table values

    I am having a table like below

    [CREATE TABLE [dbo].[Output_Info](

    [Common_Id] [int] NOT NULL,

    [Signal] [real] NOT NULL,

    [Detect] [nchar](10) NOT NULL,

    [PValue] [real] NOT NULL,

    [EAID] [int] NOT NULL,

    )]

    INSERT INTO [MDADB].[dbo].[Output_Info] ([Common_Id],[Signal],[Detect],[PValue],[EAID])

    VALUES

    (1,12.3,'P',42.5,22)

    INSERT INTO [MDADB].[dbo].[Output_Info] ([Common_Id],[Signal],[Detect],[PValue],[EAID])

    VALUES

    (1,12.3,'P',32.5,23)

    INSERT INTO [MDADB].[dbo].[Output_Info] ([Common_Id],[Signal],[Detect],[PValue],[EAID])

    VALUES

    (2,32.3,'P',43.5,22)

    INSERT INTO [MDADB].[dbo].[Output_Info] ([Common_Id],[Signal],[Detect],[PValue],[EAID])

    VALUES

    (2,8.3,'P',2.5,23)

    For each common_Id, I would like to get the rows as

    common_Id Signal_EAID,Detect_EAID,PValue_EAID

    sO THE result column headers should look like this

    commonId Signal_22 Detect_22 PValue_22 Signal_23 Detect_23 PValue_23

    112.3p42.5 12.3 P 32.5

    2 32.3 P 43.5 8.3 P 2.5

    Is it possible to do this?

    I am confused about this and got stuck here.

    Please help me.

    Thanks,

    -Maria

  • I'm not sure how to approach this with PIVOT because you aren't grouping on EAID, you are actually using it to divide into two sets of columns. You can get the result you wanted by using a summary query that gives you the max values of several case expressions. This solution assumes that the only values in EAID are 22 or 23, and that you will not have multiple rows with a given Common_ID/EAID combination.

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

    -- most of this is just setup of your data, skip to the bottom for the summary query

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

    declare @output_info TABLE (

    [Common_Id] [int] NOT NULL,

    [Signal] [real] NOT NULL,

    [Detect] [nchar](10) NOT NULL,

    [PValue] [real] NOT NULL,

    [EAID] [int] NOT NULL

    )

    INSERT INTO @output_info ([Common_Id],[Signal],[Detect],[PValue],[EAID])

    VALUES

    (1,12.3,'P',42.5,22)

    INSERT INTO @output_info([Common_Id],[Signal],[Detect],[PValue],[EAID])

    VALUES

    (1,12.3,'P',32.5,23)

    INSERT INTO @output_info ([Common_Id],[Signal],[Detect],[PValue],[EAID])

    VALUES

    (2,32.3,'P',43.5,22)

    INSERT INTO @output_info ([Common_Id],[Signal],[Detect],[PValue],[EAID])

    VALUES

    (2,8.3,'P',2.5,23)

    select * from @output_info

    ---------------------- Everything above was just to set up. This is how we roll. ----------

    select common_id

    ,max(case when EAID = 22 then Signal else null end) as Signal22

    ,max(case when EAID = 22 then Detect else null end) as Detect22

    ,max(case when EAID = 22 then Pvalue else null end) as Pvalue22

    ,max(case when EAID = 23 then Signal else null end) as Signal23

    ,max(case when EAID = 23 then Detect else null end) as Detect23

    ,max(case when EAID = 23 then Pvalue else null end) as Pvalue23

    from @output_info -- change this to your table name

    group by common_id

    order by common_id

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • Hi,

    Thanks for your reply.

    Actually, the EAID is dynamic. It will vary and so I will have to have dynamic column.

    How will I write a query in such a situation.

    Thanks in advance.

    -Maria

  • Neither my approach nor PIVOT is able to create the column names for you. You have to identify all of them in advance, which means you would have to identify all possible values for EAID in advance. If you cannot do that, you will have to do a select distinct EAID and use those values to create and execute dynamic SQL.

    Something like this. The @dynsql string is built from opening and closing constants, and in between it replaces the 'XX' strings with the distinct EIAD values from the subquery dt (derived table) to create both the case expressions and your column names.

    declare @dynSQL nvarchar(4000)

    set @dynsql = 'select common_id'

    select @dynsql = @dynsql+replace(',max(case when EAID = XX then Signal else null end) as SignalXX

    ,max(case when EAID = XX then Detect else null end) as DetectXX

    ,max(case when EAID = XX then Pvalue else null end) as PvalueXX','XX',EAID)

    from (select distinct EAID from output_info) as dt

    select @dynSQL = @dynSQL+' from output_info group by common_id order by common_id'

    print @dynSQL -- just so you can see what the query looks like

    exec sp_executesql @dynsql

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • Again using RAC:

    Exec Rac

    @transform='Max(Signal) as Signal & Max(Detect) as Detect & Max(PValue) as PValue',

    @rows='Common_ID',

    @pvtcol='EAID',

    @from='output_info',

    @row_totals='n',@grand_totals='n',@rowbreak='n',

    @rotate='nest',

    @racheck='y',@shell='n'

    Common_ID Signal_22 Detect_22 PValue_22 Signal_23 Detect_23 PValue_23

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

    1 12.3 P 42.5 12.3 P 32.5

    2 32.3 P 43.5 8.3 P 2.5

    Post back if you have questions. Pretty easy huh:)

    Visit RAC @

    www.rac4sql.net

    www.beyondsql.blogspot.com

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

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