Post reply

Help w/View Pivot

  • April 10, 2009 at 8:35 am

    #136565

    I'm receiving data from a form in a question - answer format. Is it possible to make view of the data showing with the question as column headers and the response as row data. Short data sample below.

    SessionID Question Response

    1 Name Mike

    1 City Tulsa

    1 State OK

    1 Device Hammer

    I want to view the data like this

    SessionID Name City State Device

    1 Mike Tulsa OK Hammer

    Michael

  • April 10, 2009 at 2:18 pm

    #975958

    Sure. Have a look at this and let me know if you have any questions.

    declare @sample table (SessionID int, question varchar(30), response varchar(30))

    insert into @sample

    select 1, 'Name', 'Mike' union all

    select 1, 'City', 'Tulsa' union all

    select 1, 'State', 'OK' union all

    select 1, 'Device', 'Hammer' union all

    select 2, 'Name', 'Colonel Mustard' union all

    select 2, 'City', 'Library' union all

    select 2, 'State', '' union all

    select 2, 'Device', 'Revolver'

    --

    select SessionID

    ,max(case when question = 'Name' then response else '' end) as Name

    ,max(case when question = 'City' then response else '' end) as City

    ,max(case when question = 'State' then response else '' end) as State

    ,max(case when question = 'Device' then response else '' end) as Device

    from @sample

    group by SessionID

    order by SessionID

    __________________________________________________

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

  • April 10, 2009 at 2:27 pm

    #975960

    Thanks for the help.

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

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