
USE pubs
GO

-- needs tables

CREATE SCHEMA products AUTHORIZATION dbo
GO

drop table products.productname
drop table products.properties
GO

create table products.productname
(
id int primary key,
name varchar(max)
)
go

create table products.properties
(
	id int,
	name varchar(50),
	value varchar(max),
	CONSTRAINT PK_properties PRIMARY KEY (id, name),
	FOREIGN KEY (id) REFERENCES	products.productname (id)
)
go

INSERT products.productname VALUES(1, 'shoe')
INSERT products.productname VALUES(2, 'socks')
GO

INSERT products.properties VALUES(1, 'color', 'brown')
INSERT products.properties VALUES(1, 'size', '11')
INSERT products.properties VALUES(2, 'color', 'blue')
INSERT products.properties VALUES(2, 'size', '9-12')
INSERT products.properties VALUES(2, 'fabric', 'cotton')

-- need this procedure
CREATE FUNCTION products.get_property_names()
RETURNS VARCHAR(1000)
AS
BEGIN
-- hardcode for now
RETURN '[color], [size], [fabric]'
END
GO

-- test it
SELECT products.get_property_names()
GO

-- caller must have access to products.properties table
CREATE PROCEDURE products.get_property_values
AS
DECLARE @stmt VARCHAR(8000)
DECLARE @col_list VARCHAR(1000)
SET @col_list = products.get_property_names()
-- NOTE: that this is subject to SQL injection 
-- "col_list" must be validated property 
SET @stmt='SELECT * FROM products.properties 
  PIVOT (MAX(value) 
  FOR name IN (' +@col_list+ ')) AS P'
PRINT @stmt
EXECUTE(@stmt)
go

products.get_property_values

