locked
Pivot RRS feed

  • Question

  • I'm working on getting my data to be in pivot format and trying to use the PIVOT function.

    Example:

    Student_Evaluation_Id               question          Answer_Ds
    715418*1347886310*q7p0m3    eval               Formal First Test
    715418*1347886310*q7p0m3    stud              Successful
    715418*1347886310*q7p0m3    inst                Successful

    Desired result:

    Student_Evaluation_Id                       eval                                                  stud                            inst

    715418*1347886310*q7p0m3           Formal First Test                              Successful                   Successful

    My query:

    select se.Student_Evaluation_Id,

    case when fq.Question_Ds like 'Type%' then 'eval'
         when fq.Question_Ds like 'inst%' then 'inst'
         when fq.Question_Ds like 'stud%' then 'stud'
    end as question,
    seq.Answer_Ds

    from (Student_Evaluation_Question seq


    INNER JOIN Form_Question_Subfield fqs
        ON fqs.Form_Id = seq.Form_Id
        and fqs.Subfield_Id = seq.Form_Question_Id
        and fqs.Subfield_Type in ('radio','select')
    INNER JOIN Form_Question fq
        ON fq.Form_Question_Id = fqs.Form_Question_Id
        and fq.Form_Id = fqs.Form_Id
    INNER JOIN Student_Evaluation se
        ON se.Student_Evaluation_Id = seq.Student_Evaluation_Id)

    Wednesday, October 17, 2012 2:51 PM

Answers

  • For the sake of ease of representation, I've changed the datatype of your first column.

    Hope this helps:

    CREATE TABLE #UsePivot
    (
     Student_Evaluation_Id INT, 
     Question VARCHAR(20),
     Answer_Ds VARCHAR(50)
    )
    
    INSERT #UsePivot
    SELECT 10, 'Eval','Formal First Test' UNION ALL
    SELECT 10, 'stud','Successful' UNION ALL
    SELECT 10, 'inst', 'Successful' UNION ALL
    SELECT 20, 'Eval', 'Successful' UNION ALL
    SELECT 20, 'Stud', 'Fail' UNION ALL
    SELECT 20, 'inst', 'Successful'
    
    
    
    
    
    SELECT P.* FROM 
    #UsePivot 
    PIVOT (MAX(Answer_Ds) FOR Question IN ([Eval],[stud],[inst]))P
    
    DROP TABLE #UsePivot


    Please mark as answer if this answers your question. Please mark as helpful if you found this post was helpful.

    • Marked as answer by nickswoca Wednesday, October 17, 2012 3:37 PM
    Wednesday, October 17, 2012 3:09 PM
  • there u go

    create table #T
    (Student_Evaluation_Id  varchar (50),question varchar (50) , Answer_Ds Varchar(50))
    Insert into #T (Student_Evaluation_Id ,question , Answer_Ds)
    Values 
    ('715418*1347886310*q7p0m3','eval ', 'Formal First Test'),
     ('715418*1347886310*q7p0m3','stud','Successful'),
     ('715418*1347886310*q7p0m3','inst','Successful')
     
     select * from #t
     
      select * from #T
     pivot (max (answer_ds) for question in (Eval,stud,inst)) as pvt


    Please mark as helpful and propose as answer if you find this as correct!!! Thanks,Miss Never Giveup .

    • Marked as answer by nickswoca Wednesday, October 17, 2012 3:37 PM
    Wednesday, October 17, 2012 3:20 PM

All replies

  • For the sake of ease of representation, I've changed the datatype of your first column.

    Hope this helps:

    CREATE TABLE #UsePivot
    (
     Student_Evaluation_Id INT, 
     Question VARCHAR(20),
     Answer_Ds VARCHAR(50)
    )
    
    INSERT #UsePivot
    SELECT 10, 'Eval','Formal First Test' UNION ALL
    SELECT 10, 'stud','Successful' UNION ALL
    SELECT 10, 'inst', 'Successful' UNION ALL
    SELECT 20, 'Eval', 'Successful' UNION ALL
    SELECT 20, 'Stud', 'Fail' UNION ALL
    SELECT 20, 'inst', 'Successful'
    
    
    
    
    
    SELECT P.* FROM 
    #UsePivot 
    PIVOT (MAX(Answer_Ds) FOR Question IN ([Eval],[stud],[inst]))P
    
    DROP TABLE #UsePivot


    Please mark as answer if this answers your question. Please mark as helpful if you found this post was helpful.

    • Marked as answer by nickswoca Wednesday, October 17, 2012 3:37 PM
    Wednesday, October 17, 2012 3:09 PM
  • That works but how would I do it when I have roughly 400 unique ids?

    Nick

    Wednesday, October 17, 2012 3:14 PM
  • there u go

    create table #T
    (Student_Evaluation_Id  varchar (50),question varchar (50) , Answer_Ds Varchar(50))
    Insert into #T (Student_Evaluation_Id ,question , Answer_Ds)
    Values 
    ('715418*1347886310*q7p0m3','eval ', 'Formal First Test'),
     ('715418*1347886310*q7p0m3','stud','Successful'),
     ('715418*1347886310*q7p0m3','inst','Successful')
     
     select * from #t
     
      select * from #T
     pivot (max (answer_ds) for question in (Eval,stud,inst)) as pvt


    Please mark as helpful and propose as answer if you find this as correct!!! Thanks,Miss Never Giveup .

    • Marked as answer by nickswoca Wednesday, October 17, 2012 3:37 PM
    Wednesday, October 17, 2012 3:20 PM
  • If you are talking about 400 unique Student_Evaluation_Ids, then the above query will work. As you can see, I have added another Unique Id i.e., 100 and 200 and it worked fine. Same is the case with Answer_Ds

    But if you are talking about the Question column having more than the 3 values listed above, then you would have to use dynamic pivot. 

    Here's a link that might help you with Dynamic Pivot:

    http://www.simple-talk.com/blogs/2007/09/14/pivots-with-dynamic-columns-in-sql-server-2005/

    I Hope I understood your question right.


    Please mark as answer if this answers your question. Please mark as helpful if you found this post was helpful.

    • Proposed as answer by Naomi N Wednesday, October 17, 2012 5:23 PM
    Wednesday, October 17, 2012 3:26 PM
  • Thanks for all the help.  I got the results the way I need them.  Do you know how i could use this code and create a view?
    Wednesday, October 17, 2012 4:21 PM
  • very simple u can not create view on Temptables ,

    so here is the syntax :

    create table T
    (Student_Evaluation_Id  varchar (50),question varchar (50) , Answer_Ds Varchar(50))
    Insert into T (Student_Evaluation_Id ,question , Answer_Ds)
    Values 
    ('715418*1347886310*q7p0m3','eval ', 'Formal First Test'),
     ('715418*1347886310*q7p0m3','stud','Successful'),
     ('715418*1347886310*q7p0m3','inst','Successful')
     
     --select * from #t
     
     create view [VwNew]as 
      select * from T
     pivot (max (answer_ds) for question in (Eval,stud,inst)) as pvt 


    Please mark as helpful and propose as answer if you find this as correct!!! Thanks,Miss Never Giveup .

    Wednesday, October 17, 2012 4:30 PM
  • I got it to work.  thanks.

    exec ('create view test
    as
    select * from
    T
    PIVOT (max(answer_ds) for question in ([Eval],[stud], [inst])) as pvt')

    Wednesday, October 17, 2012 4:35 PM
  • I don't think that will work.  Won't the view be static?
    Wednesday, October 17, 2012 4:39 PM
  • I created it and it worked.. dont know what is meant by static view, sorry

    Please mark as helpful and propose as answer if you find this as correct!!! Thanks,Miss Never Giveup .

    Wednesday, October 17, 2012 4:48 PM
  • Because the view is selecting from T and isn't selecting from the source tables.  So if tonight another evaluation is added the view will only reflect T and not the source data.  Does that make sense?

    Wednesday, October 17, 2012 4:55 PM
  • T was  my source table...

    Please mark as helpful and propose as answer if you find this as correct!!! Thanks,Miss Never Giveup .

    Wednesday, October 17, 2012 7:57 PM