Sort pivot table columns

I have this code to dynamically create a pivot table.  I need to have the columns sorted by name, is this possible?
DECLARE @Columns varchar(8000)
select @columns = isnull(@columns+',','') + '['+[DOKST]+']' from DRAP_1 group by [DOKST]

exec ('SELECT * 

From

   (SELECT [DOKNR], [DOKST] as EventCode, [DATUM] as EventDate from DRAP_1
    union
    SELECT [DOKNR], [DOKST] as EventCode, [DATUM] as EventDate from DRAP_1) as src

PIVOT

   (max(eventdate) FOR Eventcode IN ('+@columns+') )AS pvt

order by 1,2')
;

Open in new window

FairfieldAsked:
Who is Participating?
 
SharathData EngineerCommented:
Did you run the code I suggested. I have included the ORDER BY when storing the column names in @Columns variable.

DECLARE @Columns varchar(8000)
select @columns = isnull(@columns+',','') + '['+[DOKST]+']' from DRAP_1 group by [DOKST] order by 1

exec ('SELECT *

From

   (SELECT [DOKNR], [DOKST] as EventCode, [DATUM] as EventDate from DRAP_1
    union
    SELECT [DOKNR], [DOKST] as EventCode, [DATUM] as EventDate from DRAP_1) as src

PIVOT

   (max(eventdate) FOR Eventcode IN ('+@columns+') )AS pvt

order by 1,2')
;
0
 
SharathData EngineerCommented:
Do you mean the data in the column names or the ordering of the columns in the result set?
If ordering of the columns in the result set, you can try this.
DECLARE @Columns varchar(8000)
select @columns = isnull(@columns+',','') + '['+[DOKST]+']' from DRAP_1 group by [DOKST] order by 1

exec ('SELECT * 

From

   (SELECT [DOKNR], [DOKST] as EventCode, [DATUM] as EventDate from DRAP_1
    union
    SELECT [DOKNR], [DOKST] as EventCode, [DATUM] as EventDate from DRAP_1) as src

PIVOT

   (max(eventdate) FOR Eventcode IN ('+@columns+') )AS pvt

order by 1,2')
;

Open in new window

0
 
FairfieldAuthor Commented:
The column names only
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
David ToddSenior DBACommented:
Hi,

If the code to create the pivot table is dynamic, then surely in that code you can sort the columns, no?

Regards
  David
0
 
FairfieldAuthor Commented:
I thought that is what the Order by 1,2 did but when I run the code the names are not sorted.
0
 
FairfieldAuthor Commented:
It only give me one column, not all of them.
0
 
David ToddSenior DBACommented:
Hi,

Just an observation:

order by
  1, 2

is a _worst_ practice. Its nearly always better to use the column names or alias's than their ordinal number.

Regards
  David
0
 
SharathData EngineerCommented:
Can you post the result of your query and the expected result?
0
 
FairfieldAuthor Commented:
Here is the output and I would like to have the columns soreted alphabetically. output.txt
0
 
SharathData EngineerCommented:
Your columns in the order of CO, A2, AF, L1, RC etc. Where did you sort them?
0
 
FairfieldAuthor Commented:
I didn't sort them, I just use the code as is.  I would like the code to sort them alphabetically
0
 
SharathData EngineerCommented:
I think you are looking for this. But what is the purpose of UNION in your code. You are doing UNION on same table and same column names. You can avoid the UNION and query the table only once.
DECLARE @Columns varchar(8000)
select @columns = isnull(@columns+',','') + '['+[DOKST]+']' from DRAP_1 group by [DOKST] order by 1
declare @sql nvarchar(4000)
select @sql = 'SELECT DOKNR, ' + @Columns + 

' From

   (SELECT [DOKNR], [DOKST] as EventCode, [DATUM] as EventDate from DRAP_1
    union
    SELECT [DOKNR], [DOKST] as EventCode, [DATUM] as EventDate from DRAP_1) as src

PIVOT

   (max(eventdate) FOR Eventcode IN ('+@columns+') )AS pvt

order by 1,2'
exec(@sql)

Open in new window

0
 
FairfieldAuthor Commented:
The results were not sorted as anticipated.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.