sql - Showing data as a column name -

i have 3 tables. called them table a,table b,table c. , have desired view want get.

table aid         regnum  bid         value 2ce7d0a7    2000000 d5981dfc    offcro 9d3c13aa    2000000 c58566c5    ych -  9ddb90c4    2000000 812e9e75    y 

table b connected table table b's foreign key in table a

table b  bid             label                               columnname              order d5981dfc        offered/change role                 statuschangecode        0 c58566c5        offered/role change comments        statuschangecomments    1 812e9e75        assessed                            statuschangeassessed    2 

table c has foreign key in table well. reg num. reg num primary key in table c

table c   name        surname     regnum etibar      hasanov     2000000 

as see there column's names datas in table b

desiredview name        surname     regnum      statuschangecode    statuschangecomments    statuschangeassessed etibar      hasanov     2000000     offcro              ych -              y 

you can achieve using pivot table. try this,

create table tablea(aid varchar(50), regnum int, bid varchar(20), value varchar(50)) insert tablea values ('2ce7d0a7', 2000000, 'd5981dfc', 'offcro'), ('9d3c13aa', 2000000, 'c58566c5', 'ych - from' ), ('9ddb90c4', 2000000, '812e9e75', 'y')   create table tableb(bid varchar(20), label varchar(50), columnname  varchar(50), [order] int)  insert tableb values ('d5981dfc', 'offered/change role', 'statuschangecode', 0), ('c58566c5', 'offered/role change comments', 'statuschangecomments', 1), ('812e9e75', 'assessed', 'statuschangeassessed',  2)  create table tablec (name varchar(20), surname  varchar(20), regnum int) insert tablec values ('etibar', 'hasanov', 2000000)  select * (     select        name, surname, c.regnum, value, b.columnname       tablec c      join tablea on c.regnum = a.regnum      join tableb b on b.bid = a.bid ) source pivot (   max(value)     [columnname]      in ( [statuschangecode], [statuschangecomments],  [statuschangeassessed] ) )as pvot 

sql fiddle demo

if using sql server 2005, according microsoft technet,

when pivot , unpivot used against databases upgraded sql server 2005 or later, compatibility level of database must set 90 or higher.