如何使用sql在动态数据透视变量中设置顺序。 [英] How to set order by in dynamic pivot variable using sql.

问题描述

大家好，

如何使用sql在动态数据透视变量中设置顺序。

i已经尝试了很多其他方法，但我无法获得，所以我放弃了这里有一封邮件，

请在下面找到我的代码，

我的预期输出是：

Hi all,
how to set order by in dynamic pivot variable using sql.
i have tried many other ways but i could't get, so that i drop a mail here,
please find my code below,
my Expected output is:

2015_jan
2015_feb
----
----
etc

但根据我的代码显示如下，

but as per my code it showing like as below,

2015_feb
2015_jan
----
----
etc

所以，请让我知道我在哪里犯了错误。

So please let me know where i made a mistake.

SELECT 	       
         
     ISNULL(MS.Quantity,0) AS Quantity, 
     CONVERT(VARCHAR,YEAR(GETDATE()))+'_'+LOWER(DATENAME(MM,GETDATE()))  AS SMonth INTO #Temp3 
FROM MCHSTORE MS	
ORDER BY YEAR(GETDATE()),Month(GETDATE())
DECLARE @Scolumns NVARCHAR(MAX)
DECLARE @Ssql NVARCHAR(MAX);
DECLARE @SDcolumns NVARCHAR(MAX)
SELECT   @Scolumns = ISNULL(@Scolumns + ',','')+ 'ISNULL(' + QUOTENAME(SMonth) + ', 0) AS '+ QUOTENAME(SMonth)

  FROM (
		SELECT distinct SMonth  FROM #Temp3
       ) AS x;

 SELECT   @SDcolumns = ISNULL(@SDcolumns + ',','')+ QUOTENAME(SMonth) 
   FROM (
	       SELECT distinct SMonth  FROM #Temp3
        ) AS x;
SET @Ssql ='

SELECT  	
		 ' + @Scolumns + '  INTO #tempstore

FROM
(
             SELECT 
					ISNULL(ISNULL(MS.SQuantity,0),0) AS Quantity,
					CONVERT(VARCHAR,YEAR(GETDATE()))+''_''+DATENAME(MM,GETDATE()) AS SMonthnames  
              FROM MCHSTORE MS (NOLOCK)
) AS j
PIVOT
(
  SUM(Quantity) FOR SMonthnames IN ('+ @SDcolumns + ')
) AS p;
SELECT * FROM #tempstore';

PRINT @Ssql;
EXEC sp_executesql @Ssql;

DROP TABLE #Temp3

我的尝试：



如何在动态枢轴变量中设置顺序使用sql。

What I have tried:

how to set order by in dynamic pivot variable using sql.

推荐答案

为什么你没有按照你之前的帖子提出的建议有什么特别的原因？

如何按SQL排序年份和月份订单 [< a href =http://www.codeproject.com/Questions/1099125/How-to-sort-year-and-month-order-by-in-SQLtarget =_ blanktitle =New Window> ^ ]

Any particular reason why you didn't follow the advice on your earlier post?
How to sort year and month order by in SQL[^]

您好，

经过一番搜索和测试后，我为您制作了一个解决方案。试试这个。

首先我创建一个临时表

Hello,
After a little search and testing i made one solution for you .Try this one
First I create one temporary table

create table #tmp 
(
	dr varchar(50)
)

现在插入记录

Now insert records

insert into #tmp values ('2015_feb')
insert into #tmp values ('2015_june')
insert into #tmp values ('2015_jan')
insert into #tmp values ('2016_May')
insert into #tmp values ('2014_feb')

根据要求，订单将是明年和月份



这就是为什么第一个子串将值分别得到年份和月份部分。

作为年份长度为4因此将其子串到4位以获得年份值和

月份部分因为我们按MonthName获取价值，即5月或6月或7月



所以，只需将月份名称转换为相应的值即可

Jan - > 1

2月 - > 2

3月 - > 2

4月 - > 4

等等



对于这个问题，我在这里只使用这种格式（'2016 - '+ MonthValue + '-01 10：12：20.123'）生成一个新的日期时间，因为我必须在另一个查询中传递一个

日期时间值以获得相应月份的月值

这里的月份值为5月5日或6月6日或1月1日。



要从相应的月份获取月份值，请使用以下查询

According to the requirement , the order will be Year and month wise

That's why First substring the value to get Year part and month part separately .
as the year length is 4 hence substring it upto 4 digit to get the Year value and
for month part as we are getting value by MonthName i.e , May or June or July

so , just convert the Month name to corresponding Value means
Jan-->1
Feb-->2
Mar-->2
Apr-->4
so on

For that issue , here I simply use this format ('2016-'+MonthValue + '-01 10:12:20.123') to generate a new datetime as I have to pass one
datetime value in another query to get the Month value of corresponding month
Here month value like 5 for May or 6 for June or 1 for Jan .

To get the month value from corresponding Month use below query

select datepart(mm, convert(datetime,Getdate()))

这里OP将是5，因为当月是5月



终极查询将是

Here OP will be 5 as the current month is May

Ultimate query will be

select * from #tmp ORDER BY YEAR(substring(dr,1,4)),
DATEPART(MM,(select convert(varchar(20),Convert(datetime,'2016-'+convert(varchar,substring(dr,6,len(dr)-5))+'-01 10:12:20.123'),107))) asc

和OP将是

and OP will be

dr
2014_feb
2015_jan
2015_feb
2015_june
2016_may

谢谢

Thanks

这篇关于如何使用sql在动态数据透视变量中设置顺序。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！