sql-server - 如何在sql中的pivot列中进行升序

Question

我需要在每个系统中每小时获取报告。我已经使用 pivot 获取了所有数据。但在我的专栏中，不是按升序排列的。

我的代码在这里：

DECLARE @cols  AS NVARCHAR(MAX)='';
DECLARE @query AS NVARCHAR(MAX)='';

SELECT @cols = @cols + QUOTENAME(hour) + ',' FROM (select distinct DATEPART(hh, BillDate) AS hour from Bill_Entry WHERE BillDate >= '6/10/2018 12:00:00' AND BillDate < '7/10/2018 12:00:00' ) as tmp
select @cols = substring(@cols, 0, len(@cols)) --trim "," at end

set @query = 
'SELECT * from 
(
    SELECT Remarks, DATEPART(hh, BillDate) AS hour, sum(cast(GrandTotal as int)) as Total
FROM Bill_Entry  WHERE BillDate >=  ''6/10/2018 12:00:00''  AND BillDate <=  ''7/10/2018 12:00:00''

GROUP BY DATEPART(hh, BillDate),Remarks 

) src
pivot 
(
    max(Total) for hour in (' + @cols + ')
) piv order by Len(Remarks), Remarks'

execute(@query)

我的输出是这样的：

这里我的专栏不是按升序排列的。我需要像这样的列

8 9 10 11 12 13 14 15 16 17 18 ......

标签： sql-server

Answer 1

您应该进行以下更改

DECLARE @cols  AS NVARCHAR(MAX)='';
DECLARE @query AS NVARCHAR(MAX)='';

SELECT @cols = @cols + 
            QUOTENAME(hour) + 
            ',' 
            FROM (
            select distinct DATEPART(hh, BillDate) AS hour 
            from Bill_Entry 
            WHERE BillDate >= '6/10/2018 12:00:00' 
            AND BillDate < '7/10/2018 12:00:00' 
            ) as tmp
            ORDER BY DATEPART(hh, BillDate) ASC
            -- the above order by line will solve your issue
select @cols = substring(@cols, 0, len(@cols)) --trim "," at end

set @query = 
'SELECT * from 
(
    SELECT Remarks, DATEPART(hh, BillDate) AS hour, sum(cast(GrandTotal as int)) as Total
FROM Bill_Entry  WHERE BillDate >=  ''6/10/2018 12:00:00''  AND BillDate <=  ''7/10/2018 12:00:00''

GROUP BY DATEPART(hh, BillDate),Remarks 

) src
pivot 
(
    max(Total) for hour in (' + @cols + ')
) piv order by Len(Remarks), Remarks'

execute(@query)