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

@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:

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