0

I do have a Dynamic SQL statement because I need to pivot my data on a specific column. I am able to order my columns according to a specific column id that I created, but my problem is that I am not able to order the data in the output set.

DECLARE @PivotTableSQL2 NVARCHAR(MAX)
SET @PivotTableSQL2 = N'
  Select ''regionMailMerge'',''tblTransactions''

  SELECT nasParticipant, nomParticipant, prenomParticipant, dateTransaction,typeTransaction,'+@PivotColumnHeaders+'
  FROM (
    SELECT top 10000
      H.nasParticipant,
      H.nomParticipant,
      H.prenomParticipant,
      H.dateTransaction,
      H.profil,
      H.ValeurTransaction,
      H.idTri,
      h.typeTransaction
    FROM T_SommaireRapportTransactions H
    ORDER BY H.nomParticipant, H.prenomParticipant
  ) AS PivotData
  PIVOT (
    SUM(ValeurTransaction)
    FOR profil IN (
      ' + @PivotColumnHeaders + '
    )
  ) AS PivotTable
ORDER BY idTri'


EXECUTE(@PivotTableSQL2)

My query works, but the data is not ordered by nomParticipant, prenomParticipant. I also tried without the TOP 10000, but I keep receiving the following error :

Msg 1033, Level 15, State 1, Line 17
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

What am I doing wrong?

Thanks for your help, I searched the web and wasn't able to find a proper answer for my problem.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Samuel
  • 1
  • `ORDER BY H.nomParticipant, H.prenomParticipant` must be in the end of query, where `ORDER BY idTri` stands now. – gofr1 Feb 26 '16 at 16:03
  • i tried that as well but I received the following error: Msg 4104, Level 16, State 1, Line 23 The multi-part identifier "PivotData.nomParticipant" could not be bound. Msg 4104, Level 16, State 1, Line 23 The multi-part identifier "PivotData.prenomParticipant" could not be bound. – Samuel Feb 26 '16 at 16:08
  • Oh! I removed the alias and it worked! Thanks for your help! – Samuel Feb 26 '16 at 16:11
  • @Samuel See my answer on **[SQL Server Query Error -ORDER BY clause is invalid in views](https://stackoverflow.com/questions/36697511/sql-server-query-error-order-by-clause-is-invalid-in-views/61702593#61702593)**. – Murat Yıldız May 09 '20 at 19:36

0 Answers0