Click here to Skip to main content
14,424,458 members
Rate this:
Please Sign up or sign in to vote.
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.

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
Updated 11-May-16 23:55pm
v2
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!
Rate this:
Please Sign up or sign in to vote.

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[^]
   
Comments
Maciej Los 13-May-16 17:26pm
   
5ed!
Rate this:
Please Sign up or sign in to vote.

Solution 1

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

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

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


and OP will be
dr
2014_feb
2015_jan
2015_feb
2015_june
2016_may

Thanks
   

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

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100