Merge row values into a CSV (a.k.a GROUP_CONCAT for SQL Server)

Question

I have a table like:

EntityID AttributeID OptionText 5016 20 Paintings 5044 18 Female 5060 48 M 5060 48 F 5060 49 Apple 5060 49 Banana 5060 49 Cat

I want to create a view that will show:

5016 20 Paintings 5044 18 Female 5060 48 M,F 5060 49 Apple, Banana, Cat

Means The attributes values on every entity should be displayed separated by a comma.

The number of options can be varied.

Any help is appreciated!

gbn · Accepted Answer · 2011-10-13 14:51:14Z

12

For each pair of EntityID, AttributeID use the XML path trick to generate the CSV

 SELECT M.EntityID, M.AttributeID, SUBSTRING(CAST(foo.bar AS varchar(8000)), 2, 7999) AS Options FROM ( SELECT DISTINCT EntityID, AttributeID FROM MyTable ) M CROSS APPLY ( SELECT ',' + OptionText FROM MyTable M2 WHERE M.EntityID = M2.EntityID AND M.AttributeID= M2.AttributeID FOR XML PATH ('') ) foo(bar)

answered Oct 13, 2011 at 14:51

gbn

434k84 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Seph Over a year ago

Would there be any noticeable performance difference between SUBSTRING and CAST versus STUFF(( ... ), 1, 2, '')?

gbn Over a year ago

@Seph: marginal, don't know which way. The bulk of resources and time will be the JOINs and the XML processing. Not removing the first comma.

Moons Over a year ago

Thanks for solving the query but i am still facing performance issues. The table is going to have more than 1 million records. In this case even to fetch just few records the time taken is more than 2 seconds.

gbn Over a year ago

@Kamal Deep Singh: you have an index problem then. Ask a new question with table schema and indexes.

Moons Over a year ago

@gbn Actually the issue is the table i have shown you is actually a view. I have not apply indexing of its own. The index will be default if provided by the sql server. I am actually applying a model similar to EAV model in this case i need such a condition.

|

Community · Accepted Answer · 2021-01-18 12:30:34Z

Try the code below (I've included all test SQL so you don't have to practice on live data). You can view a working example here: https://data.stackexchange.com/stackoverflow/q/115141/

--Set up test table CREATE TABLE #Table1 (EntityID INT, AttributeID INT, OptionText VARCHAR(50)) INSERT INTO #Table1 SELECT 5030, 48, 'M' INSERT INTO #Table1 SELECT 5030, 48, 'F' --Begin actual working SQL SELECT T1.EntityID, T1.AttributeID, STUFF(( SELECT ', ' + T2.OptionText FROM #Table1 T2 WHERE T2.AttributeID = T1.AttributeID AND T2.EntityID = T1.EntityID FOR XML PATH('') ), 1, 2, '') [Attributes] FROM #Table1 T1 GROUP BY T1.EntityID, T1.AttributeID DROP TABLE #Table1

The query is very helpful but i am still facing performance issues. The table is going to have more than 1 million records. In this case even to fetch just few records the time taken is more than 2 seconds. Is there is any alternative for this.

feronovak · Accepted Answer · 2011-10-28 17:15:58Z

2

I have done some tests with 3 different methods:

http://blog.feronovak.com/2011/10/multiple-values-in-one-column-aka.html

Hope it helps.

answered Oct 28, 2011 at 17:15

feronovak

2,6976 gold badges36 silver badges55 bronze badges

1 Comment

Bratch Over a year ago

Since this is a frequent task for us, we ended up making it a function similar to your Method #1. The xml path trick works too, but the function makes the SQL easier to read.

Collectives™ on Stack Overflow

Merge row values into a CSV (a.k.a GROUP_CONCAT for SQL Server)

3 Answers 3

7 Comments

1 Comment

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

1 Comment

1 Comment

Linked

Related