group by value in the prefix of the values of that group [modified]

[indian143]

Hi All, I want a query to get all the column values as comma seprated values, but it should be group by in such a way that it should get the group by value in the prefix of the values of that group. Means the group column value should appear only once and other description column values for that group should precede it. And all the rows should come as one string. Can anybody help me in writing this query. Sorry I forgot to tell you that I am using SQL Server.

Thanks & Regards, Md. Abdul Aleem NIIT technologies

modified on Monday, June 28, 2010 5:16 PM

[i j russell]

Can you show us a sample of the data and the output that you want. It would also be nice if you showed us how you have tried to tackle the task.

[indian143]

Like you have two columns Category and Item. Now I want all the items and categories should come in one single row but with different categories items should be separated by their categories. For example You have Cat1 Item1 Cate2 Item 2 Cate3 Item3 Cate1 Item 2 Cate1 Item3 Cate2 Item1 Cate2 Item 3 Cate3 Item1 Cate3 Item2 Then my output should look as follows cate1: Item1, Item2, ITem3/Cate2: Item1, Item2, Item3/Cate3:Item1, Item2, Item3. Can you please help me in achieving it, in performance oriented way. They need that too. Sorry I forgot to tell you that I am using SQL Server.

Thanks & Regards, Md. Abdul Aleem NIIT technologies

[indian143]

Hi Russel, I have give the example in the previous reply, can you please give me any solution to do it. Its urgent requirement. For example You have Cat1 Item1 Cate2 Item 2 Cate3 Item3 Cate1 Item 2 Cate1 Item3 Cate2 Item1 Cate2 Item 3 Cate3 Item1 Cate3 Item2 Then my output should look as follows cate1: Item1, Item2, ITem3/Cate2: Item1, Item2, Item3/Cate3:Item1, Item2, Item3. Can you please help me in achieving it, in performance oriented way. They need that too.

Thanks & Regards, Md. Abdul Aleem NIIT technologies

[i j russell]

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;

[i j russell]

:mad: Please do not demand answers otherwise you will find that no one will help you.

[indian143]

I am sorry pls. I did mistake pls. forgive me. In hurry I did a blunder. Please sorry.

Thanks & Regards, Md. Abdul Aleem NIIT technologies

[indian143]

This query is not in SQL Server 2008. Should I change anything for running this in SQL Server.

Thanks & Regards, Md. Abdul Aleem NIIT technologies

[i j russell]

I wrote this in Sql Server 2008.

[indian143]

Sorry for troubling you but it is giving error "Incorrect syntax near the keyword 'FOR'". And I dont know much about using FOR in T-SQL. If possible can you pls. help me in that. Why is it coming. I tried by moving ")", but doesnt work.

Thanks & Regards, Md. Abdul Aleem NIIT technologies

[i j russell]

I have checked the code in Sql Server Management Studio 2008 and 2005 by creating a new query, copying the code into it and running it; It works fine. How are you trying to run the code?