CODEWITHSUNDEEP
sqlsql-serversql-server-2008
I A Khan
I A Khan

Reputation: 8859

How to use Order by in Pivot Table using SQL Server

I am using SQL Server 2008, and make below query for Pivot Table, 

SELECT [pay date], 
       [adm. fee], 
       [colleege dev.] 
FROM   (SELECT 
       CONVERT(VARCHAR(10), CONVERT(DATE, dbo._feepayment._issuedate, 106), 105) AS [Pay Date], 
       dbo._feehead._feeheadname AS aaa, 
       Isnull(dbo._feepaymentdet._amount, 0) AS _AMOUNT 
        FROM   dbo._feepaymentdet 
               LEFT OUTER JOIN dbo._feehead 
                            ON dbo._feepaymentdet._feeheadid = 
                               dbo._feehead._feeheadid 
               RIGHT OUTER JOIN dbo._feepayment 
                             ON dbo._feepaymentdet._feepaymentid = 
                                dbo._feepayment._feepaymentid 
        WHERE  ( dbo._feehead._isdeleted = N'1' ) 
               AND ( dbo._feepayment._isdeleted = N'1' ) 
               AND ( dbo._feepaymentdet._isdeleted = N'1' ) 
               AND dbo._feepayment._issuedate >= 
                   CONVERT(DATETIME, '01.09.2014 00:00:00', 105) 
               AND dbo._feepayment._issuedate <= 
                   CONVERT(DATETIME, '11.09.2014 23:59:59', 105)) AS s 
       PIVOT ( Sum(s._amount) 
             FOR s.aaa IN ( [ADM. FEE], 
                            [COLLEEGE DEV.] ) ) pvt

Above query return following records,

Pay Date        ADM. FEE     COLLEEGE DEV.
09-09-2014      25.00        100.00
05-09-2014      75.00        300.00
01-09-2014      25.00        100.00
03-09-2014      75.00        310.00
02-09-2014      50.00        200.00

but i want ORDER BY on Pay Date above record , if I add 

WHERE (dbo._FEEHEAD._ISDELETED = N'1') 
 AND (dbo._FEEPAYMENT._ISDELETED = N'1') 
 AND (dbo._FEEPAYMENTDET._ISDELETED = N'1')  
 AND dbo._FEEPAYMENT._ISSUEDATE >= CONVERT(DATETIME, '01.09.2014 00:00:00', 105) 
 AND dbo._FEEPAYMENT._ISSUEDATE <= CONVERT(DATETIME, '11.09.2014 23:59:59', 105)
 ORDER BY dbo._FEEPAYMENT._ISSUEDATE

SQL giving me Error :

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.

How I add ORDER BY in above query .

Upvotes: 1

Views: 13667

Answers (1)

Dgan
Dgan

Reputation: 10295

If i m not a wrong Just Use TOP 100 Percent

Its Due To Derived Tables Not Allowed Order BY Clause for inner Queries

SELECT TOP 100 Percent [Pay Date], [ADM. FEE], [COLLEEGE DEV.] FROM 
 (SELECT Convert(varchar(10),CONVERT(date,dbo._FEEPAYMENT._ISSUEDATE,106),105) AS [Pay Date], 
 dbo._FEEHEAD._FEEHEADNAME AS aaa, ISNULL(dbo._FEEPAYMENTDET._AMOUNT ,0) AS _AMOUNT 
 FROM dbo._FEEPAYMENTDET LEFT OUTER JOIN dbo._FEEHEAD ON dbo._FEEPAYMENTDET._FEEHEADID 
 = dbo._FEEHEAD._FEEHEADID RIGHT OUTER JOIN dbo._FEEPAYMENT ON 
 dbo._FEEPAYMENTDET._FEEPAYMENTID = dbo._FEEPAYMENT._FEEPAYMENTID 
 WHERE (dbo._FEEHEAD._ISDELETED = N'1') 
 AND (dbo._FEEPAYMENT._ISDELETED = N'1') 
 AND (dbo._FEEPAYMENTDET._ISDELETED = N'1')  
 AND dbo._FEEPAYMENT._ISSUEDATE >= CONVERT(DATETIME, '01.09.2014 00:00:00', 105) 
 AND dbo._FEEPAYMENT._ISSUEDATE <= CONVERT(DATETIME, '11.09.2014 23:59:59', 105)) 
 as s PIVOT ( Sum(s._AMOUNT) FOR s.aaa IN ( [ADM. FEE], [COLLEEGE DEV.] ) ) pvt
Order BY pvt.[Pay Date]

Or

Without TOP 100 Percent

SELECT [Pay Date], [ADM. FEE], [COLLEEGE DEV.] FROM 
 (SELECT Convert(varchar(10),CONVERT(date,dbo._FEEPAYMENT._ISSUEDATE,106),105) AS [Pay Date], 
 dbo._FEEHEAD._FEEHEADNAME AS aaa, ISNULL(dbo._FEEPAYMENTDET._AMOUNT ,0) AS _AMOUNT 
 FROM dbo._FEEPAYMENTDET LEFT OUTER JOIN dbo._FEEHEAD ON dbo._FEEPAYMENTDET._FEEHEADID 
 = dbo._FEEHEAD._FEEHEADID RIGHT OUTER JOIN dbo._FEEPAYMENT ON 
 dbo._FEEPAYMENTDET._FEEPAYMENTID = dbo._FEEPAYMENT._FEEPAYMENTID 
 WHERE (dbo._FEEHEAD._ISDELETED = N'1') 
 AND (dbo._FEEPAYMENT._ISDELETED = N'1') 
 AND (dbo._FEEPAYMENTDET._ISDELETED = N'1')  
 AND dbo._FEEPAYMENT._ISSUEDATE >= CONVERT(DATETIME, '01.09.2014 00:00:00', 105) 
 AND dbo._FEEPAYMENT._ISSUEDATE <= CONVERT(DATETIME, '11.09.2014 23:59:59', 105)) 
 as s PIVOT ( Sum(s._AMOUNT) FOR s.aaa IN ( [ADM. FEE], [COLLEEGE DEV.] ) ) pvt
Order BY pvt.[Pay Date]

Upvotes: 5

Related Questions