找到你要的答案

Q:Order By In Pivot Select Statement

Q:按轴心选择语句排序

This is my statement I need Order By date but I can't do this any help please

My code

PROCEDURE [sal_pivottable_day_plan]
AS
    DECLARE @cols AS NVARCHAR(MAX), 
            @query AS NVARCHAR(MAX),
            @DtFrom date, @DtTo date

    SELECT @cols = STUFF((SELECT distinct ',' + QUOTENAME(sal_day_plan_0015.MyDate) FROM sal_day_plan_0015 

    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')
    SET @query = 'SELECT dealers_sril, dealers_name, stop_value, hanging_value, dealers_balanc, paid_val_motkhrat, ' + @cols + ' 
                  FROM ( 
                         SELECT dealers_sril, dealers_name, stop_value, hanging_value, dealers_balanc, paid_val_motkhrat, paid_val_between,MyDate
                        FROM  sal_day_plan_0015 
                        )x
                   PIOVT
                   ( 
                     min(paid_val_between)for MyDate in (' + @cols + ') 
                   ) p'             

    EXEC(@query)            

    RETURN 0

这是我的声明,我需要的日期,但我不能做任何帮助,请

我的代码

PROCEDURE [sal_pivottable_day_plan]
AS
    DECLARE @cols AS NVARCHAR(MAX), 
            @query AS NVARCHAR(MAX),
            @DtFrom date, @DtTo date

    SELECT @cols = STUFF((SELECT distinct ',' + QUOTENAME(sal_day_plan_0015.MyDate) FROM sal_day_plan_0015 

    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')
    SET @query = 'SELECT dealers_sril, dealers_name, stop_value, hanging_value, dealers_balanc, paid_val_motkhrat, ' + @cols + ' 
                  FROM ( 
                         SELECT dealers_sril, dealers_name, stop_value, hanging_value, dealers_balanc, paid_val_motkhrat, paid_val_between,MyDate
                        FROM  sal_day_plan_0015 
                        )x
                   PIOVT
                   ( 
                     min(paid_val_between)for MyDate in (' + @cols + ') 
                   ) p'             

    EXEC(@query)            

    RETURN 0
answer1: 回答1:

You can alias ',' + QUOTENAME(sal_day_plan_0015.MyDate) as c and order by that column:

PROCEDURE [sal_pivottable_day_plan]
AS
DECLARE @cols AS NVARCHAR(MAX), @query  AS NVARCHAR(MAX),@DtFrom date ,@DtTo date

    SELECT @cols = STUFF((SELECT distinct ',' + QUOTENAME(sal_day_plan_0015.MyDate) AS c FROM sal_day_plan_0015 
    ORDER BY c
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')

    SET @query = 'SELECT dealers_sril, dealers_name, stop_value, hanging_value, dealers_balanc, paid_val_motkhrat, ' + @cols + ' 
                  FROM ( 
                         SELECT dealers_sril, dealers_name, stop_value, hanging_value, dealers_balanc, paid_val_motkhrat, paid_val_between,MyDate
                        FROM  sal_day_plan_0015 
                        )x
                   PIVOT
                   ( 
                     min(paid_val_between)for MyDate in (' + @cols + ') 
                   ) p'             
EXEC(@query)            
RETURN 0

PS: you have error in PIVOT keyword.

你可以化名',' + quotename(sal_day_plan_0015。创建)为C和顺序,列:

PROCEDURE [sal_pivottable_day_plan]
AS
DECLARE @cols AS NVARCHAR(MAX), @query  AS NVARCHAR(MAX),@DtFrom date ,@DtTo date

    SELECT @cols = STUFF((SELECT distinct ',' + QUOTENAME(sal_day_plan_0015.MyDate) AS c FROM sal_day_plan_0015 
    ORDER BY c
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')

    SET @query = 'SELECT dealers_sril, dealers_name, stop_value, hanging_value, dealers_balanc, paid_val_motkhrat, ' + @cols + ' 
                  FROM ( 
                         SELECT dealers_sril, dealers_name, stop_value, hanging_value, dealers_balanc, paid_val_motkhrat, paid_val_between,MyDate
                        FROM  sal_day_plan_0015 
                        )x
                   PIVOT
                   ( 
                     min(paid_val_between)for MyDate in (' + @cols + ') 
                   ) p'             
EXEC(@query)            
RETURN 0

你有错误的轴心关键字。

sql-server  tsql  pivot