We have a dataset we want to pivot. There are basically 60 columns for the resulting pivot column set but the catch is the data has a type. If PlanID 1 doesn't have a value then we want to use PlanID 2.
DECLARE @testTable TABLE
(
ID INT PRIMARY KEY IDENTITY(1,1),
[ColID] INT NOT NULL,
[PropertyName] NVARCHAR(50) NOT NULL,
[Value] NVARCHAR(MAX) NOT NULL,
[PlanID] INT NOT NULL
)
DECLARE @parentTable TABLE
(
[ColID] INT PRIMARY KEY
)
INSERT INTO @parentTable ([ColID])
select 200
union all
select 300
union all
select 400
INSERT INTO @testTable ([ColID], [PropertyName], [Value], [PlanID])
select 200, 'Prop1', 343, 1
union all
select 200, 'Prop1', 444, 2
union all
select 200, 'Prop2', 555, 2
union all
select 300, 'Prop2', 111, 2
select parent.[ColID],
COALESCE(VT_A.[Prop1],VT_F.[Prop1]) AS "Prop1",
COALESCE(VT_A.[Prop2],VT_F.[Prop2]) AS "Prop2"
from
(
select [ColID] from @parentTable
) parent
left join
(
select ColID,[Prop1],[Prop2]
from
(
select ColID, PropertyName, [Value]
FROM @testTable
WHERE PlanID = 1
) as sourcetable
pivot
(
min([Value]) for PropertyName in ([Prop1],[Prop2])
) as pivottable
) VT_A
on VT_A.ColID = parent.ColID
left join
(
select ColID,[Prop1],[Prop2]
from
(
select ColID, PropertyName, [Value]
FROM @testTable
WHERE PlanID = 2
) as sourcetable
pivot
(
min([Value]) for PropertyName in ([Prop1],[Prop2])
) as pivottable
) VT_F
on VT_F.ColID = parent.ColID
So we're code-generating this as a view and with 60 columns but the data table has probably 300,000 rows. The view performance is poor. It would be queried filtered by range of [ColID]'s but nevertheless even filtered to 30,000 records it is performing poorly.
Is there a better way to structure such a query?