Database

Select *
From Table1
Order by convert(datetime,StupidVarcharDateField)

Chris Meech wrote:

was left scratching my head

CREATE TABLE #data
(
	Category VARCHAR(10),
	Item VARCHAR(10)
);

INSERT INTO #data (Category, Item) VALUES ('Cate1', 'Item1');
INSERT INTO #data (Category, Item) VALUES ('Cate2', 'Item 2');
INSERT INTO #data (Category, Item) VALUES ('Cate3', 'Item3');
INSERT INTO #data (Category, Item) VALUES ('Cate1', 'Item 2');
INSERT INTO #data (Category, Item) VALUES ('Cate1', 'Item3');
INSERT INTO #data (Category, Item) VALUES ('Cate2', 'Item1');
INSERT INTO #data (Category, Item) VALUES ('Cate2', 'Item 3');
INSERT INTO #data (Category, Item) VALUES ('Cate3', 'Item1');
INSERT INTO #data (Category, Item) VALUES ('Cate3', 'Item2');

SELECT Category,
	STUFF((SELECT ',' + Item AS [text()]
			FROM #data AS d2
			WHERE d2.Category = d1.Category
			ORDER BY Item ASC
			FOR XML PATH('')), 1, 1, '') as ItemList
FROM #data AS d1
GROUP BY Category;

DROP TABLE #data;