CODEWITHSUNDEEP
mysqlsql-server
Ashesh Das
Ashesh Das

Reputation: 365

Is there a way to have the months in a sorted way within a cell of a table

I have a table which has the following data :

enter image description here

Is there a way to select the months column in such a way that the months are not displayed alphabetically but sorted as calendar months separated by commas? For example the second row should return APR,MAY,JUN rather than APR,JUN,MAY.

Upvotes: 1

Views: 61

Answers (3)

Slava N.
Slava N.

Reputation: 596

select
    qtr
    ,replace(
       case when charindex('JAN', Months) > 0 then 'JAN,' else '' end
       + case when charindex('FEB', Months) > 0 then 'FEB,' else '' end 
       + case when charindex('MAR', Months) > 0 then 'MAR,' else '' end 
       + case when charindex('APR', Months) > 0 then 'APR,' else '' end 
       + case when charindex('MAY', Months) > 0 then 'MAY,' else '' end 
       + case when charindex('JUN', Months) > 0 then 'JUN,' else '' end 
       + case when charindex('JUL', Months) > 0 then 'JUL,' else '' end 
       + case when charindex('AUG', Months) > 0 then 'AUG,' else '' end
       + case when charindex('SEP', Months) > 0 then 'SEP,' else '' end
       + case when charindex('OCT', Months) > 0 then 'OCT,' else '' end
       + case when charindex('NOV', Months) > 0 then 'NOV,' else '' end
       + case when charindex('DEC', Months) > 0 then 'DEC,' else '' end
       + case when rtrim(ltrim(Months)) != '' then  ',' else '' end
       ,',,'
       ,''
     ) [Months]
from
  your_table
;

Upvotes: 1

Ullas
Ullas

Reputation: 11556

If SQL-Server.

First create either a temp table, table variable or regular table for storing the order for each month.

I just created a regular table.

CREATE TABLE tbl_month
(
    Months VARCHAR(3), [order] INT
);
INSERT INTO tbl_month VALUES
('JAN',1),
('FEB',2),
('MAR',3),
('APR',4),
('MAY',5),
('JUN',6),
('JUL',7),
('AUG',8),
('SEP',9),
('OCT',10),
('NOV',11),
('DEC',12);

Then Split each comma separated value and join it with above table variable for the month order and store the result set to a temp for ease in use.

SELECT t1.*, t2.[order] into #temp_table from 
(
    SELECT A.qtr, Split.a.value('.', 'VARCHAR(100)')  as Months FROM  
    (
        SELECT qtr,  
        CAST ('<M>' + REPLACE(Months, ',', '</M><M>') + '</M>' AS XML) AS Months  
        FROM  my_table_name
    ) AS A CROSS APPLY Months.nodes ('/M') AS Split(a))t1
JOIN tbl_month t2
ON t1.Months = t2.Months
ORDER BY t1.qtr;

The above query will create a temp table as follows.

+---------+--------+-------+
| qtr     | Months | order |
+---------+--------+-------+
| 2015-Q1 | MAR    | 3     |
| 2015-Q1 | JAN    | 1     |
| 2015-Q1 | FEB    | 2     |
| 2015-Q2 | APR    | 4     |
| 2015-Q2 | JUN    | 6     |
| 2015-Q2 | MAY    | 5     |
| 2015-Q3 | SEP    | 9     |
| 2015-Q3 | AUG    | 8     |
| 2015-Q3 | JUL    | 7     |
| 2015-Q4 | OCT    | 10    |
| 2015-Q4 | DEC    | 12    |
+---------+--------+-------+

Then concatenate the Months in the order of month order for each qtr.

SELECT qtr, 
       STUFF
       (
         (
           SELECT ',' + Months 
           FROM #temp_table AS t2
           WHERE t2.qtr = t.qtr 
           ORDER BY [order]
           FOR XML PATH('')
          ),1,1,'') as Months
FROM #temp AS t
GROUP BY qtr
ORDER BY qtr;

Result

+---------+-------------+
| qtr     | Months      |
+---------+-------------+
| 2015-Q1 | JAN,FEB,MAR |
| 2015-Q2 | APR,MAY,JUN |
| 2015-Q3 | JUL,AUG,SEP |
| 2015-Q4 | OCT,DEC     |
+---------+-------------+

Upvotes: 1

cameront
cameront

Reputation: 710

So I am with Andy, the design that you have presented definitely has a code smell, you are breaching the concept of normalization in relational databases. But as a pure coding exercise this is how you do it. 1) Create a udf that splits the string 2) create a query that joins to this udf and order it by the new date that you create.

1) 

CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )
RETURNS
 @returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN

 DECLARE @name NVARCHAR(255)
 DECLARE @pos INT

 WHILE CHARINDEX(',', @stringToSplit) > 0
 BEGIN
  SELECT @pos  = CHARINDEX(',', @stringToSplit)  
  SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

  INSERT INTO @returnList 
  SELECT @name

  SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
 END

 INSERT INTO @returnList
 SELECT @stringToSplit

 RETURN
END

2)

Select theDate = convert(date, left(qtr, 4) + '-' + st.name + '-01') from [dbo].[Qtrs] q 
cross apply dbo.splitstring (q.months) as st
order by thedate

Upvotes: 0

Related Questions