sql server - Outputting column name / values as key value pairs in SQL view field -

i have database multiple tables share several common fields (id (guid), title, parentid(guid)), of them can have table specific fields.

is possible create view unions on these tables , outputs forth column json representation of key value pairs representing column name , value of other fields other 3 common ones? value of field used web application. doesn't have json, xml, comma separated, should represent fieldname / value pairing of 1 or more fields not common between unioned tables.

to clarify. take following 2 table schemas

table1 id    title    parentid   abooleanfield    anintegerfield 1     parent null       true             50 2     child  1          false            100  table2  id    title         parentid    adatefield 3     anotherparent null        10/12/2014 

the view output

id      title          parentid      uncommon 1       parent       null          abooleanfield:true,anintegerfield:50 2       child        1             abooleanfield:false,anintegerfield:100 3       anotherparent  null          adatefield:10/12/2014 

the ids guids in reality , uncommon field nvarchar(max)

cheers

stewart

let

schema

create table table1 (id int, title nvarchar(100), parentid int, abooleanfield varchar(10), anintegerfield int) insert table1 values (1, 'a parent', null, 'true', 50), (2, 'a child', 1, 'false', 100)  create table table2 (id int, title nvarchar(100), parentid int, adatefield varchar(100)) insert table2 values (1, 'anotherparent  ', null, '10/12/2014') 

and here

query

select id     ,title     ,parentid     ,(         select stuff((                     select ',"abooleanfield": ' + abooleanfield + ',"anintegerfield":' + cast(anintegerfield varchar(20))                     xml path('')                         ,type                     ).value('.', 'varchar(max)'), 1, 1, '')         ) 'keyvalpair' table1 union select id     ,title     ,parentid     ,(         select stuff((                     select ',"adatefield": ' + adatefield                     xml path('')                         ,type                     ).value('.', 'varchar(max)'), 1, 1, '')         ) 'keyvalpair' table2 

and can check here in sql fiddle that.

output.

id      title          parentid      keyvalpair 1       parent       null          abooleanfield: true, anintegerfield:50 2       child        1             abooleanfield: false, anintegerfield:100 3       anotherparent  null          adatefield: 10/12/2014