It'll sound a little stupid, but creating a separate composite non clustered index on the attributes table on product id and each column, resulting in 150+ indexes on the attributes table, will get you the ultimate speed for that query because it will support the select distinct column value by product from the attributes table. Your insert performance will be super painful.
You're looking for a pivot. Either of these queries will work: SqlFiddle
/* case */
select
CustomerId
, FirstName =max(case when [Key]= 'FirstName' then Value end)
, LastName =max(case when [Key]= 'SecondName' then Value end)
, Age =max(case when [Key]= 'Age' then Value end)
, Gender =max(case when [Key]= 'Gender' then Value end)
from Customers
group by CustomerId
/* pivot */
select CustomerId, FirstName, SecondName, Age, Gender
from (Select CustomerId, [Key], Value from Customers) c
pivot ( max(Value)
for [Key] in (FirstName, SecondName, Age, Gender)
) as p
Schema Setup for SqlFiddle:
create table Customers (
id int identity (1,1) not null primary key
, CustomerID int not null
, [Key] varchar(32) not null
, Value varchar(32)
)
insert into Customers (CustomerId, [Key], Value) values
(2,'FirstName','Tim')
, (2,'SecondName','Skold')
, (2,'Age','48')
, (2,'Gender','Male')
, (3,'FirstName','Sql')
, (3,'SecondName','Zim')
, (3,'Age','32')
, (3,'Gender','Male')
Links
Best Answer
Specify each database name explicitly as a string:
There is no other way. After all, you have to explicitly specify the database name in the table references too (
X.dbo.tablecust,Y.dbo.tablecustetc.).