No this didn;t worked.....it gives me some strange output..not strange but I just returns 1 result..one of the column values...nothing concatenated ....when the table has more than 300.
Please some other suggestions..
I want concatenated list ( comma separated) of one of the columns....but duplicates removed and just their one copy should exist.
i do not want to use subquery that is retrieve all than remove duplicate.
The above one...strangely does not give error thought but also does not give desired result. so may be some twist should work..any idea?
As a reader of the daily WTF, you should know that everything is better with XML. It is kind of like ketchup for the programming world ;-).
On a slightly more serious note, GROUP_CONCAT is possible in SQL 2005, and may even be available somewhere. SQL 2005 introduces CLR functions (functions written in .NET languages), including aggregate functions. You may have to write it yourself, but it is possible.
I don't like that method particularly, but it does outperform the recursive variable assignment and can be used inline (e.g. comma delimited exams marks per student) without the need of being tuurned in to a UDF.
And of course I've heard of group_concat, I think you'll find I praised MySQL extrememly highly for this function a mere month or two ago
As yet there is no such thing as an ordered aggregate function in SQL Server. So with a concatenation aggregate you can't control the order in which the data gets strung together. That's the big advantage of GROUP_CONCAT in MySQL and the FOR XML solution in SQL Server.