Turning multiple data records into a single record

Question

I have a table containing item data that it its simplest form consists of an item number and a category. An item number can be in multiple categories but not all items are in every category:

Item                   category

1111                   A
1111                   B
1111                   C
2222                   A
3333                   B
3333                   C

I have to put this data into a feed for a 3rd party in the form of an single item number and its associated categories. Feed layout cannot be changed.

So for the above the feed would have to contain the following

1111,A,B,C
2222,A
3333,B,C

Does anyone know how to does this. I have spiralled into a group by, roll up, pivoting mess and could use some assistance.

thanks

so you are trying to do, you sql query for this right – Suganthan Madhavan Pillai Nov 23 '13 at 10:02 — Suganthan Madhavan Pillai, Nov 23 '13 at 10:02
yes I need a query that will combine the items. – user3024570 Nov 23 '13 at 11:04 — user3024570, Nov 23 '13 at 11:04

score 0 · Accepted Answer · edited May 23 '17 at 10:25

0

You have to use GROUP_CONCAT function. It allowes to concatenate values from multiple (grouped) rows when using GROUP BY statement. Read more here.

Ok, as you are using MS server, i think that you will have take the following approach Simulating group_concat MySQL function in Microsoft SQL Server 2005?

edited May 23 '17 at 10:25

Community

1
1

answered Nov 23 '13 at 10:18

Antoniossss

31,590
6
57
99

Sorry, I should have said that I am using SQl Server 2008. I don't think that has a group concat clause. – user3024570 Nov 23 '13 at 10:37
Did you checked that? – Antoniossss Nov 23 '13 at 10:43
Now thats just crappy mysql product! – Antoniossss Nov 23 '13 at 10:59
Thanks. Used the xml method from that link. – user3024570 Nov 23 '13 at 17:00

score 0 · Answer 2 · answered Nov 23 '13 at 11:19

0

This is how you can take that list.

SELECT item,category FROM item_category WHERE item="1111"

But I doubt with your db design and your requirement.

Listing like this [1111,A,B,C], [2222,B]... you have to manipulate in your business logic

answered Nov 23 '13 at 11:19

Suganthan Madhavan Pillai

5,495
9
51
84

score 0 · Answer 3 · answered Nov 23 '13 at 11:48

I made function and with that i achieved result which you want. Hope below solution work for you.

CREATE FUNCTION GetCategory
(@Item As integer) Returns nvarchar(max) As BEGIN
Declare @RetVal as nvarchar(max) = ''
Declare @Category as nvarchar(40)  

DECLARE db_GetString CURSOR FOR
SELECT MyTable.Category
FROM MyTable
WHERE MyTable.Item = @Item

OPEN db_GetString      
FETCH NEXT FROM db_GetString INTO @Category
WHILE @@FETCH_STATUS = 0      
BEGIN      
    SET @RetVal = @RetVal + ',' + @Category
    FETCH NEXT FROM db_GetString INTO @Category
END       

CLOSE db_GetString      
DEALLOCATE db_GetString     

Return Substring(@RetVal,2, Len(@RetVal)) END
 GO

Select Item, dbo.GetCategory(Item) As Category FROm MyTable GROUP by MyTable.Item

Thanks. looks pretty useful but i would be concerned over the performance using cursors as I am going to be forking over a lot of data. — user3024570, Nov 23 '13 at 17:01

Turning multiple data records into a single record

3 Answers3