indexed_view_with_pivot.sql

USE [master]
GO

CREATE DATABASE [testing]
GO

USE [testing]
GO

CREATE TABLE [dbo].[Items](
    [ItemID] [int] IDENTITY(1,1) NOT NULL,
    [ItemName] [varchar](50) NOT NULL,
    CONSTRAINT [PK_Items] PRIMARY KEY CLUSTERED 
    (
        [ItemID] ASC
    )
)
GO

INSERT [dbo].[Items] ([ItemName]) VALUES (N'SomeItemFoo')
GO

CREATE TABLE [dbo].[ItemAttributes](
    [ItemAttributeID] [int] IDENTITY(1,1) NOT NULL,
    [ItemID] [int] NOT NULL,
    [AttributeName] [varchar](50) NOT NULL,
    [AttributeValue] [varchar](50) NOT NULL,
    CONSTRAINT [PK_ItemAttributes] PRIMARY KEY CLUSTERED 
    (
        [ItemAttributeID] ASC
    )
)
GO

INSERT [dbo].[ItemAttributes] ([ItemID], [AttributeName], [AttributeValue]) VALUES (1, N'Color', N'Red')
GO
INSERT [dbo].[ItemAttributes] ([ItemID], [AttributeName], [AttributeValue]) VALUES (1, N'Size', N'Big')
GO


CREATE VIEW [dbo].[PivotedItems]
WITH SCHEMABINDING
AS
    select
        ItemID,
        ItemName,
        Color,
        Size
    FROM 
    (
        select
            i.ItemID,
            i.ItemName,
            ia.AttributeName,
            ia.AttributeValue
        from dbo.Items i
            inner join dbo.ItemAttributes ia
                on i.ItemID = ia.ItemID
    ) AS SourceData
    PIVOT
    (
        max(AttributeValue)
        FOR AttributeName
        IN
        (
            Color,
            Size
        )
    ) AS PivotTable
GO


CREATE UNIQUE CLUSTERED INDEX IDX_PivotedItems_ItemID 
    ON dbo.PivotedItems (ItemID);
GO