sql server - Accessing data from SQL table containing ID, [Value Type] and Value -

if question has been asked before, please share link question. i've done searching , not able find answer question this. possibly due difficulty i've encountered trying word query displays results i'm looking for.

background:
i'm designing database hold information (like databases do). particular database holds, purpose of question, 3 tables.

the 3 tables store different information; 1 table defines user unique id , user name, second stores types of information can stored user , third stores information user .

users table defined with:

create table dbo.users (     id          uniqueidentifier not null default (newid()),     [user name] nvarchar(1024)   not null ); 

the [user info data type] table defined as:

create table dbo.[user info data type] (     id               uniqueidentifier not null default (newid()),     [data type name] nvarchar(256)    not null ); 

last not least, [user info] table defined as:

create table dbo.[user info] (     id               uniqueidentifier not null default (newid()),     [user id]        uniqueidentifier not null, --fk [users].id     [user info type] uniqueidentifier not null, --fk [user info data dype].id     value            varbinary(max)   null ); 

question:
i've seen done before (at previous job) i'm not advanced in sql wish was. i'd know how can aggregate data in third table against single record in first table using second table define header of data in third table.

i'll clarify results i'm expecting.

let's 1^st table has 1 record, it's name column contains dave.
2^nd table has entry "created" type.
3^rd table has entry has dave's id, "created" id , value (in binary) of created date.

let's arguments sake "dave's" record created august 24, 2015 @ 00:00h. result of query yield:

user name | created dave      | 8d2ac16f443c000 

the column name pulled second table , value third.

i understand complicated of ask appreciated.

thanks in advance!

-dave

this eav model. commented nick, should research on pros , cons. however, if wish pursue model, queries rely on pivots or crosstabs. here example using dynamic crosstab:

first, create sample data. note, removed spaces column names.

create table #users (     id          uniqueidentifier not null default (newid()),     username    nvarchar(1024)   not null ); create table #userinfodatatype (     id              uniqueidentifier not null default (newid()),     datatypename    nvarchar(256)    not null ); create table #userinfo (     id              uniqueidentifier not null default (newid()),     userid          uniqueidentifier not null, --fk [#users].id     userinfotype    uniqueidentifier not null, --fk [user info data dype].id     value           varbinary(max)   null );  insert #users(username) values('dave'); insert #userinfodatatype(datatypename) values('created'), ('firstname'), ('lastname') insert #userinfo(userid, userinfotype, value)     select         u.id,         uidt.id,         value =             case                 when uidt.datatypename = 'created' convert(varbinary(max), convert(varchar(10),getdate(), 101))                 when uidt.datatypename = 'firstname' convert(varbinary(max), 'dave')                 when uidt.datatypename = 'lastname' convert(varbinary(max), 'doe')             end     #users u     cross join #userinfodatatype uidt 

now, dynamic crosstab solution:

declare @sql1 nvarchar(2000) = '',         @sql2 nvarchar(2000) = '',         @sql3 nvarchar(2000) = ''  select @sql1 =  'select       u.username' + char(10)  select @sql2 = @sql2 + '   , max(case when i.userinfotype = ''' + convert(varchar(max), id) + ''' convert(varchar(max), i.value) end) ' + quotename(datatypename) + char(10) #userinfodatatype  select @sql3 = 'from #userinfo inner join #users u on u.id = i.userid inner join #userinfodatatype t on t.id = i.userinfotype group i.userid, u.username'  print(@sql1 + @sql2+ @sql3) exec(@sql1 + @sql2+ @sql3) 

result:

username created    firstname lastname  -------- ---------- --------- --------  dave     08/25/2015 dave      doe    

see here in sede.