How to ORDER BY in SQL PIVOT
Try this:
with Mth (st, nd) as (
select DATEADD (M, datediff (m, 0,'2012-09-01'), 0),
DATEADD (M, DATEDIFF (m, 0, '2012-09-01') + 1, 0)
union all
select DATEADD (m, 1, st),
DATEADD (m, 1, nd)
from Mth
where nd <= DATEADD (m, datediff (m, 0, getdate()), 0)
), Pivoted
AS
(
select *
from
(
select MONTH(Mth.st) Month,
U.USER,
COUNT(S.QRY_ID) Searches
FROM Mth
LEFT JOIN SEARCHES S
on Mth.st <= S.CREATED
and Mth.nd > S.CREATED
LEFT JOIN MEMBERS U
on U.AID = S.AID
GROUP BY YEAR(Mth.st), MONTH(Mth.st), U.HOLDER_LOGIN
) src
pivot
(
sum(searches)
for month in ([12],[11],[10])
) piv
)
SELECT *
FROM Pivoted
ORDER BY Dec
SQL Server Pivot and Sort
You are not pivoting correctly and the column names should be generated in a single string. Try this:
CREATE TABLE ShopOrder (ItemCode VARCHAR(100),[Description] VARCHAR(100),ReleaseDate DATE, RequiredQty INT)
GO
INSERT INTO ShopOrder
VALUES
('A','SLEEVE NUT','08/01/2017',19200)
,('A','SLEEVE NUT','08/02/2017',18000)
,('A','SLEEVE NUT','09/01/2017',17000)
,('B','STARTER','08/03/2017',10000)
,('B','STARTER','08/04/2017',18000)
,('B','STARTER','09/15/2017',16000)
DECLARE @SQLQuery AS NVARCHAR(MAX)
DECLARE @PivotColumns AS NVARCHAR(MAX)
SET @PivotColumns = STUFF(( SELECT DISTINCT ',[' + CONVERT(char(6), cast(ReleaseDate as date), 112 ) + ']'
FROM ShopOrder
ORDER BY ',[' + CONVERT(char(6), cast(ReleaseDate as date), 112 ) + ']'
FOR XML PATH('')),1,1,'')
SET @SQLQuery =
N'
SELECT ItemCode,'+ @PivotColumns + '
FROM (SELECT ItemCOde,CONVERT(char(6), cast(ReleaseDate as date),112) ReleaseDate, RequiredQty
FROM ShopOrder) AS T
PIVOT( SUM(RequiredQty)
FOR ReleaseDate IN ('+@PivotColumns+')) AS P
'
SELECT @SQLQuery
--Execute dynamic query
EXEC sp_executesql @SQLQuery
How to use Order by in Pivot Table using SQL Server
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]
Sort months in ascending order in a Pivot Table
You could add the month number to the pivot portion of the query, and then order using it:
SELECT [Month], [2001], [2002]
FROM
(
SELECT DATENAME(M, PurchaseDate) AS [Month],
MONTH(PurchaseDate) AS mn,
YEAR(PurchaseDate) AS [Year],
SoldAmount
FROM Purchases
) AS DataSource
PIVOT
(
SUM(SoldAmount)
FOR [Year] IN ([2001],[2002])
) AS Pivoting
ORDER BY mn;
Issue adding order by to PIVOT group by SQL
As the error says, you can't order a subquery unless there is a reason for the order (TOP, FOR XML etc). The reason for this is that just because you have ordered the subquery there is no reason that this order would be maintained in your outer query. SQL Server is essentially telling you that your ORDER BY is pointless, therefore not valid.
The solution is to simply add a column with month number to your subuquery s, then you can order by it. You would also need to explicitly state your select list to ensure that this new column does not appear in it:
DECLARE @cols AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(year(TransactionDateTime))
FROM Quotations
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'');
SET @query =
'SELECT [month], ' + @Cols + '
FROM (
SELECT
left(datename(month,TransactionDateTime),3) as [month],
datepart(month,TransactionDateTime) as [monthNum],
year(TransactionDateTime) as [year],
isnull(count(*),0) as Total
FROM quotations
group by left(datename(month,TransactionDateTime),3), datepart(month,TransactionDateTime), year(TransactionDateTime)
) as s
PIVOT
(
SUM(Total)
FOR [year] IN (' + @cols + ')
) AS QuotationResults
ORDER BY QuotationResults.MonthNum;';
EXECUTE(@query);
ADDENDUM
The ISNULL() does not trap the null values because at the point of using ISNULL() they don't exist. COUNT(*) will never return null, so your ISNULL() is actually redundant.
In a very simple example if you have:
TransactionDateTime
----------------------
2015-01-01
2015-02-01
2015-02-01
2014-03-01
To skip ahead one step, after your pivot you will end up with:
Month 2014 2015
------------------------
Jan NULL 1
Feb NULL 2
Mar 1 NULL
So you end up with NULL values, now to go back a step, if you look at the results after your aggregation you have:
Month MonthNum Year Total
-----------------------------------
Jan 1 2015 1
Feb 2 2015 2
Mar 3 2014 1
So there are no rows for Jan or Feb in 2014, therefore SUM(NULL) will yield NULL. I would suggest leaving all the aggregation to the pivot function. So your non dynamic query would look something like:
SELECT pvt.[Month], pvt.[2014], pvt.[2015]
FROM ( SELECT [Month] = LEFT(DATENAME(MONTH, TransactionDateTime), 3),
[MonthNum] = DATEPART(MONTH, TransactionDateTime),
[Year] = DATEPART(YEAR, TransactionDateTime),
Value = 1
FROM Quotations
) AS t
PIVOT
(
COUNT(Value)
FOR [year] IN ([2014], [2015])
) AS pvt
ORDER BY pvt.MonthNum;
And put into dynamic SQL:
DECLARE @cols AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(DATEPART(YEAR, TransactionDateTime))
FROM Quotations
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'');
SET @query =
'SELECT pvt.[Month], ' + @cols + '
FROM ( SELECT [Month] = LEFT(DATENAME(MONTH, TransactionDateTime), 3),
[MonthNum] = DATEPART(MONTH, TransactionDateTime),
[Year] = DATEPART(YEAR, TransactionDateTime),
Value = 1
FROM Quotations
) AS t
PIVOT
(
COUNT(Value)
FOR [year] IN (' + @cols + ')
) AS pvt
ORDER BY pvt.MonthNum;
(
SUM(Total)
FOR [year] IN (' + @cols + ')
) AS QuotationResults
ORDER BY QuotationResults.MonthNum;';
EXECUTE sp_executesql @query;
Why is the order by not working in my query with a Pivot Clause?
Just use conditional aggregation:
SELECT client,
SUM(CASE WHEN CLUSTER_LOG = 'AS' THEN CS_VOL END) as vol_as,
SUM(CASE WHEN CLUSTER_LOG = 'AEA' THEN CS_VOL END) as vol_aea,
SUM(CASE WHEN CLUSTER_LOG = 'HT' THEN CS_VOL END) as vol_ht,
SUM(CASE WHEN CLUSTER_LOG = 'PT' THEN CS_VOL END) as vol_pt,
SUM(CASE WHEN CLUSTER_LOG = 'AES' THEN CS_VOL END) as vol_aes
FROM FID
GROUP BY client
ORDER BY SUM(CS_VOL);
Sorting a PIVOT table in SQL
If you want the final columns to be ordered then you should be able to add the ORDER BY SortOrder in your query that gets the @regionList so you will change the code to the following:
DECLARE @RegionList varchar(2000),@SQL varchar(max)
SELECT @RegionList = STUFF((SELECT ',' + QuoteName(Region)
FROM #Temp
GROUP BY Region, SortOrder
ORDER BY SortOrder --- add the SortOrder here
FOR XML PATH('')),1,1,'')
Your current code is ordering the data by the Region so it is alphabetical. You need to implement the SortOrder in this list.
Related Topics
Select Rows That Are a Multiple of X
Can SQL Clr Triggers Do This? or Is There a Better Way
Different Value Counts on Same Column
How to Create Foreign Keys Across Databases
Why Are Dot-Separated Prefixes Ignored in the Column List for Insert Statements
Inserting Default Value as Current Date + 30 Days in MySQL
Notify My Wcf Service When My Database Is Updated
SQL How to Search a Many to Many Relationship
Return Value from MySQL Stored Procedure
When Should You Consider Indexing Your SQL Tables
SQL Select Group by and String Concat
Why When Matched' Cannot Appear More Than Once in a 'Update' Clause of a Merge Statement