My existing query is:
SELECT @str = coalesce(@str + ']' + ', ', '') + '[' + monthyear
FROM (SELECT DISTINCT ( monthyear), ExpectedDate
FROM table) s
order by ExpectedDate
I need to bring unique monthyear string in order by its date
ExpectedDate monthyear
2015-11-01 Nov-15
2015-11-01 Nov-15
2015-11-01 Nov-15
2015-11-01 Nov-15
2015-11-01 Nov-15
2015-11-01 Nov-15
2015-11-01 Nov-15
2015-11-01 Nov-15
2015-11-01 Nov-15
2015-11-01 Nov-15
2015-12-01 Dec-15
2015-12-01 Dec-15
2015-12-01 Dec-15
2015-12-01 Dec-15
2015-12-01 Dec-15
2015-12-01 Dec-15
2015-12-01 Dec-15
2015-12-01 Dec-15
2015-12-02 Dec-15
2015-12-30 Dec-15
2016-01-01 Jan-16
2016-01-01 Jan-16
2016-01-01 Jan-16
2016-01-01 Jan-16
2016-01-01 Jan-16
2016-01-01 Jan-16
2016-01-01 Jan-16
2016-01-01 Jan-16
2016-01-01 Jan-16
2016-01-01 Jan-16
2016-01-01 Jan-16
2016-01-01 Jan-16
2016-01-01 Jan-16
2016-01-01 Jan-16
2016-01-04 Jan-16
2016-01-04 Jan-16
2016-01-04 Jan-16
2016-01-04 Jan-16
2016-02-01 Feb-16
2016-02-01 Feb-16
2016-02-01 Feb-16
2016-02-01 Feb-16
2016-02-01 Feb-16
2016-02-01 Feb-16
2016-02-01 Feb-16
2016-02-01 Feb-16
2016-02-01 Feb-16
2016-03-01 Mar-16
2016-03-01 Mar-16
2016-03-01 Mar-16
2016-03-01 Mar-16
2016-03-01 Mar-16
2016-03-01 Mar-16
2016-03-01 Mar-16
2016-04-01 Apr-16
2016-04-01 Apr-16
2016-04-01 Apr-16
2016-04-01 Apr-16
2016-04-01 Apr-16
2016-06-01 Jun-16
2016-06-01 Jun-16
2016-07-01 Jul-16
2016-10-01 Oct-16
2016-10-01 Oct-16
2016-12-01 Dec-16
2016-12-30 Dec-16
Use XML PATH
DECLARE @t table(ExpectedDate date, monthdate char(8))
INSERT @t values
('2015-11-01', 'Nov-2015'),
('2015-11-01', 'Nov-2015'),
('2015-12-01', 'Dec-2015'),
('2015-12-01', 'Dec-2015'),
('2016-01-01', 'Jan-2016')
SELECT
STUFF((
SELECT ',[' + STUFF(monthdate,4,1, '') + ']'
FROM @t t1
GROUP BY ExpectedDate, monthdate
ORDER BY ExpectedDate
for xml path(''), type
).value('.', 'varchar(max)'), 1, 1, '') [values]
Result:
[Nov2015],[Dec2015],[Jan2016]
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加