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

Question

0.00/5 (No votes)

See more:

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.

SQL

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

What I have tried:

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

Posted 10-May-16 20:51pm

stellus

Updated 11-May-16 22:55pm

Maciej Los

v2

Add a Solution

Comments

Maciej Los 11-May-16 3:04am

Deja vu? Seems, i'd seen it before...

CHill60 12-May-16 4:54am

Oh this feeling of deja vu
is certainly not just from you
Of this kind of post, there is almost a host
And repeats in there - just a few!

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2016-05-11T22:55:00

Solution 2

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

Posted 11-May-16 22:55pm

CHill60

Comments

Maciej Los 13-May-16 17:26pm

5ed!

Animesh Datta · Answer 2 · 2016-05-11T22:39:00

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

SQL

create table #tmp 
(
	dr varchar(50)
)

Now insert records

SQL

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')

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

SQL

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

Here OP will be 5 as the current month is May

Ultimate query will be

SQL

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

and OP will be

C#

dr
2014_feb
2015_jan
2015_feb
2015_june
2016_may

Thanks