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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
David ToddSenior Database AdministratorCommented:
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
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FairfieldAuthor Commented:
It only give me one column, not all of them.
0
David ToddSenior Database AdministratorCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.