Stored Proc (Pivot Columns Rankin )

Question

5.00/5 (1 vote)

See more:

SQL-Server-2008

SQL

Declare @paramList VARCHAR(MAX)
SET @paramList = STUFF((
                    SELECT DISTINCT ',[' + Question + ']'
                     FROM View_S FOR XML PATH('')
                    )  ,1,1,'')
Print @paramList


DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT RollNo,SName,'; + @paramList
+ 'FROM( SELECT RollNo,SName,Results,Question FROM View_S )src
PIVOT(Max(Results) FOR Question IN (' + @paramList +')) pvt'

EXEC sp_executesql @query

i write this query and its working this output is
___________
Q4|Q3|Q1|Q2
-----------

but i want it in order wise
___________
Q1|Q2|Q3|Q4
-----------
what i do please help me

[Edit]Code block added[/Edit]

Posted 9-Mar-13 1:34am

Arbaz Ali

Updated 9-Mar-13 1:48am

Thomas Daniels

v2

Add a Solution

2 solutions

Solution 2

Try this:

SQL

Declare @paramList VARCHAR(MAX)
SET @paramList = STUFF((
                    SELECT DISTINCT '],[' + Question
                    FROM View_S
                    ORDER BY  '],[' + Question
                    FOR XML PATH('')) ,1,2,'') + ']'
PRINT @paramList

Posted 9-Mar-13 9:03am

Maciej Los

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Michael Haephrati · Accepted Answer · 2013-03-09T08:30:00

Solution 1

You just need to add ORDER BY RollNo to your SQL query, followed by the order (acceding or descending). For acceding use ASC and for decending use DESC

Further reading:
http://www.w3schools.com/sql/sql_orderby.asp[^]

Posted 9-Mar-13 8:30am

Michael Haephrati

Stored Proc (Pivot Columns Rankin )

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Stored Proc (Pivot Columns Rankin )

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us