如何使用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屋!