12

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!

Improve this question
0

3 Answers 3

Reset to default
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)
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Would there be any noticeable performance difference between SUBSTRING and CAST versus STUFF(( ... ), 1, 2, '')?
@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.
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.
@Kamal Deep Singh: you have an index problem then. Ask a new question with table schema and indexes.
@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.
|
4

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
Improve this answer

1 Comment

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.
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.

Improve this answer

1 Comment

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.