locked
Placing Columns AFTER a pivoted column RRS feed

  • Question

  • Is it possible to place columns after a pivoted column?  For instance, I have a Test with the Questions for the test pivoted to form a single row, and would like the score and proficiency level to be shown after the questions, like so:

    Student       Test Name    Q1    Q2   Q3   Q4   Q5    Score    Proficiency Level

    Student 1     Test 1          A      C    B      D    C       85.3    Distinguished

    Student 2     Test 2          B      C    B      D   D        92.3    Proficient

    My query simply wants to put the pivoted columns at the end of the row...

    Select * 
    from  (
         Select distinct
    	  null			StudentNo,
    	  t.TestName	 	    AssessmentTitle,
    	  q.QuestionNumber,
    	  q.RawAnswer,
    	  null				Score,
    	  null				PerfLevel,
    	From	Test t
    		Join Question q on q.TestID = t.TestID
    		 and t.TestID = 410832) as src
    	Pivot (max(RawAnswer)
    	For QuestionNumber in ([1],[2],[3],[4],[5])) as pvt)
    
    Wednesday, January 19, 2011 7:32 PM

Answers

  • Yes, just don't use SELECT *, instead use SELECT <list of the columns you want in the order you want them>.  Then you can have any order you want.  For example, the following put VendorID after the pivoted columns.

    USE AdventureWorks
    GO
    SELECT [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5, VendorID
    FROM 
    (SELECT PurchaseOrderID, EmployeeID, VendorID
    FROM Purchasing.PurchaseOrderHeader) AS p
    PIVOT
    (
    COUNT (PurchaseOrderID)
    FOR EmployeeID IN
    ( [164], [198], [223], [231], [233] )
    ) AS pvt
    ORDER BY VendorID;
    
    Tom

    • Proposed as answer by Naomi N Wednesday, January 19, 2011 7:53 PM
    • Marked as answer by puffster Wednesday, January 19, 2011 8:01 PM
    Wednesday, January 19, 2011 7:49 PM
  • You need to explicitly list the columns in the order you want instead of select * from.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by puffster Wednesday, January 19, 2011 8:01 PM
    Wednesday, January 19, 2011 7:55 PM

All replies

  • Yes, just don't use SELECT *, instead use SELECT <list of the columns you want in the order you want them>.  Then you can have any order you want.  For example, the following put VendorID after the pivoted columns.

    USE AdventureWorks
    GO
    SELECT [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5, VendorID
    FROM 
    (SELECT PurchaseOrderID, EmployeeID, VendorID
    FROM Purchasing.PurchaseOrderHeader) AS p
    PIVOT
    (
    COUNT (PurchaseOrderID)
    FOR EmployeeID IN
    ( [164], [198], [223], [231], [233] )
    ) AS pvt
    ORDER BY VendorID;
    
    Tom

    • Proposed as answer by Naomi N Wednesday, January 19, 2011 7:53 PM
    • Marked as answer by puffster Wednesday, January 19, 2011 8:01 PM
    Wednesday, January 19, 2011 7:49 PM
  • You need to explicitly list the columns in the order you want instead of select * from.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by puffster Wednesday, January 19, 2011 8:01 PM
    Wednesday, January 19, 2011 7:55 PM
  • That was embarrassingly easy...I'll try to save a tough one for you all the next time :-)
    Wednesday, January 19, 2011 8:02 PM