How to efficiently pivot data with coalesce filtering

Viewed 10

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?

0 Answers
Related