Create indexed view

My table structure is below :

MyTable (ID Int, AccID1 Int, AccID2 Int, AccID3 int)

 ID      AccID1      AccID2      AccID3
----    --------    --------    --------
  1        12          2          NULL
  2        4           12           1
  3       NULL        NULL          5
  4        7          NULL          1   

I want to create indexed view with below output :

 ID    Level     Value
----   -----    -------
 1       1         12
 1       2         2 
 2       1         4
 2       2         12
 2       3         1
 3       3         5
 4       1         7
 4       3         1

EDIT :

My table is very huge and I want to have above output. I can Get my query such as below :

Select  ID,
        Case StrLevel
            When  'AccID1' Then 1
            When  'AccID2' Then 2
            Else 3
        End AS [Level],
        AccID as Value
From    (
        Select A.ID, A.AccID1, A.AccID2, A.AccID3
        From MyTable A
        )as p
UNPIVOT (AccID FOR [StrLevel] IN (AccID1, AccID2, AccID3)) AS unpvt

or

Select *
from (
        select MyTable.ID,
                num.n as [Level],
                Case Num.n
                    When 1 Then MyTable.AccID1
                    When 2 Then MyTable.AccID2
                    Else MyTable.AccID3
                End AS AccID
        from myTable
        cross join (select 1
                    union select 2
                    union select 3)Num(n)
    )Z
Where Z.AccID IS NOT NULL

or

    Select  A.ID,
            2 AS [Level],
            A.AccID1 AS AccID
     From MyTable A
     Where A.AccID1 IS NOT NULL

    Union

    Select  A.ID,
            2 AS [Level],
            A.AccID2
     From MyTable A
     Where A.AccID2 IS NOT NULL

    Union

    Select  A.ID,
            3 AS [Level],
            A.AccID3
     From MyTable A
     Where A.AccID3 IS NOT NULL  

But Above query is slow and I want to have indexed view to have better performance. and in indexed view I can't use UNION or UNPIVOT or CROSS JOIN in indexed view.

Answers


What if you created a Numbers table to essentially do the work of your illegal CROSS JOIN?

Create Table Numbers (number INT NOT NULL PRIMARY KEY) 
Go

Insert Numbers 
Select top 30000 row_number() over (order by (select 1)) as rn
from sys.all_objects s1 cross join sys.all_objects s2
go

Create view v_unpivot with schemabinding
as  
Select MyTable.ID,
        n.number as [Level],
        Case n.number
            When 1 Then MyTable.AccID1
            When 2 Then MyTable.AccID2
            Else MyTable.AccID3
        End AS AccID
From dbo.Mytable
Join dbo.Numbers n on n.number BETWEEN 1 AND 3 
go

Create unique clustered index pk_v_unpivot on v_unpivot (ID, [Level]) 
go

Select 
  ID,
  [Level],
  AccID
From v_unpivot with (noexpand)
Where AccID IS NOT NULL 
Order by ID, [Level]

The WHERE AccID IS NOT NULL must be part of the query because derived tables are not allowed in indexed views.


Need Your Help

Doctrine Load Module Exception

php orm doctrine

I'm new to doctrine. I created an bootstrap file like the following one:

MVC3 URL parameters - avoiding malicious attacks/security flaws

asp.net-mvc-3 security url parameters

When navigating to a new webpage, is there a "Best Practice" for passing Ids around.