Table where row act as column

- give some sample data
- show output desired
- lastly we talk about performance (after a solution is at hand)

First of all: You've tagged this post MongoDB? Is this correct?

Just a guess: You're working with an EAV-model (enitity-attribute-value). These models have their use-case. But as you already said: How do you index these or improve speed? Well, that is on point of this model: You use these model where entities have possibly a large amount of attributes, which are sparsely filled. Here the performance is simply achieved by only covering the entity.

So for your question: index the entity key column(s).

Then when you're using SQL Server, you can additionally try using filtered indices (using the WHERE clause when creating the index).

But: This kind of problem raises a question.. What is your concrete use-case? Cause it smells like reporting, Thus using an appropriate DW may make sense. Here you would "denormalize" your model to fit into a star-schema.

MongoDB topic removed.

How big is the table? How many rows it has?

Because of the design of the table I'm not sure an index will help.
Which version of SQL Server do you have? If MSSQL 2014 then you can put all table in memory for very fast access (like 100x faster).

rbhargaw, is your issue solved?
If so, please close this question by accepting the comment or comments that helped you out.
Cheers