asked
Merge row values into a CSV (a.k.a GROUP_CONCAT for SQL Server) without Violating DRY Principle
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. But the most important issue is the query should not violate **DRY** Priciple The query should be very fast because the table are going to have millions of records. Any help is appreciated! This is the actual query that i created using the answers it takes about 20 seconds to get few million records but if i just wan to get few records like 20-30 it takes 2-3 secinds. I want to reduce the time by less than a half second I need to inject where conditions into it. Please specify what i am missing select joined.* , eset.PageNo from (select * from ( select a.EntityTypeID , a.EntityID , a.AttributeValue , ea.AttributeName from (SELECT M.EntityTypeID , M.EntityID, M.AttributeID, SUBSTRING(CAST(foo.bar AS varchar(8000)), 2, 7999) AS AttributeValue FROM ( SELECT DISTINCT EntityID, AttributeID , EntityTYPEID FROM [testview1] where ENtityID between 5060 and 56598 and EntitytypeID = 9 ) M CROSS APPLY ( SELECT ',' + OptionText FROM [testview1] M2 WHERE M.EntityID = M2.EntityID AND M.AttributeID= M2.AttributeID and M.EntityTypeID = M2.ENtityTYpeID and ENtityID between 5060 and 56598 and EntitytypeID = 9 FOR XML PATH ('') ) foo(bar)) a inner join EntityAttributes ea on
ea.ID = a.AttributeID ) options union all (select EntityAttributes.EntityTypeID, EntityAttributeValues.EntityID as 'EntityID' , EntityAttributeValues.AttributeValue ,EntityAttributes.attributeName from EntityAttributeValues inner join EntityAttributes on
EntityAttributes.ID = EntityAttributeValues.AttributeID where EntityAttributeValues.EntityID between 5060 and 56598 and EntityAttributes.EntityTypeID = 9**93 ) ) joined inner join Entity_Paged_View_With_Set eset on
eset.ID = joined.EntityID
How often will the code to create have to run, is it a one off?
Try this, it is *ONE* way of doing it declare @YourTable table (EntityID int, AttributeID int, OptionText varchar(50)) insert into @YourTable values(5016,20,'Paintings') insert into @YourTable values(5044,18,'Female') insert into @YourTable values(5060,48,'M') insert into @YourTable values(5060,48,'F') insert into @YourTable values(5060,49,'Apple') insert into @YourTable values(5060,49,'Banana') insert into @YourTable values(5060,49,'Cat') select distinct EntityID, AttributeID, stuff ( ( SELECT ',' + OptionText FROM @YourTable TABLE1 WHERE TABLE1.EntityID = TABLE2.EntityID AND TABLE1.AttributeID = TABLE2.AttributeID FOR XML PATH ( '' ) ) , 1 , 1 , '' ) FROM @YourTable TABLE2 gives EntityID AttributeID ----------- ----------- ------------------------- 5016 20 Paintings 5044 18 Female 5060 48 M,F 5060 49 Apple,Banana,Cat
How slow is slow, Jeff Moden talks about using this method in this post , and measures a 17 sec duration for a million row table. If you need to keep doing this over and over in a view, then you need to rethink your design. How about materializing this 'view' in a table? How about changing the base table design to store the data as a CSV? How about handling the string manipulation in the application layer/presentation layer?
I post the same question on stackoverflow too. and get the same reply. This query is too slow and does not solve my purpose. Isnt there is some other way. I want fully optimized query
This is the best way I know of. If it's slow, look at the query plan and see how you can optimise it.
@Kamal Deep Singh Saini You can reduce the number of reads from the table a little bit by first grouping the records by 2 columns and then calling cross apply rather than calling it once for each row and then using distinct which does the grouping by 3 columns. This will not help a lot, but will be a bit cheaper to do because the grouping is easier and the number of calls to the inner query is smaller:
select
t.EntityID, t.AttributeID,
stuff(x.Options, 1 , 1 , '') Options
from
(
select
EntityID, AttributeID
from @YourTable
group by EntityID, AttributeID
) t cross apply
(
select ',' + OptionText
from @YourTable
where
EntityID = t.EntityID
and AttributeID = t.AttributeID
for xml path('')
) x(Options); If this does not help much then you can try using the dynamic pivot first and then the normal concat to derive your delimited lists (one for each combination). This will make the query much more complex, but considerably cheaper and probably faster. Please let me know if you want it and I can post a sample.
@Oleg you can please post a sample related to dynamic pivot. What finally i need to do is to get the data in the required format as specified by the question and then pivot it. Actually i am trying to create a Model that resembles EAV. I will post the actual query that i created using the sample given above
Kev's solution is the fastes technique I know of using pure SQL. In addition to Kev's design suggestions, have you looked at your indexing scheme to make sure you are using the best indexes (missing index feature may help)? Also, is there any chance of a resource contention? If something else is holding a lock on the table, your query may be waiting to start, or if the server is simply under great strain from other processes at the same time, everything may be slow.
@Kev Riley Thanks about the article what i am missing is i havent use indexing on the underlying tables. Thanks. Actually the actual query is a mix of too much query. Thank you once agin.
