Vertical_Table_Design

Author: David Lund

IdentTypes
----
ID        Desc          DataType
1         First Name    Char
2         Last Name     Char
3         SSN           Char
4         StateId       Int
5         Foo

Person
-----------
INT   INT             Varchar(max)
ID    IdentTypeId     Value
1     1               David
1     2               Lund
1     4               12
2     1               Taylor
2     2               Brazelton
2     3               555112222


select
  a.Value as FirstName,
  b.Value as LastName,
  d.Value as State
from
  Person a
  left outer join Person b on a.ID = b.ID and b.IdentTypeId = 2
  left outer join Person c on a.ID = c.ID and c.IdentTypeId = 4
  left outer join StateTable d on c.Value = d.ID
where
  a.IdentTypeId = 1
  and c.Value = 12