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

Question

I have a database with multiple tables that share several common fields (ID (Guid), Title, ParentID(Guid)), but any of them can have table specific fields.

Is it possible to create a view that UNIONs on these tables and outputs a forth column that is a JSON representation of key value pairs representing the column name and value of ALL other fields other than the 3 common ones? The value of the field would then be used by a web application. It doesn't have to be JSON, it could be XML, comma separated, but basically should represent the fieldname / value pairing of one or more fields that are not common between the unioned tables.

To Clarify. Take the following two table schemas

Table1
ID    Title    ParentID   ABooleanField    AnIntegerField
1     A Parent NULL       True             50
2     A Child  1          False            100

Table2 
ID    Title         ParentID    ADateField
3     AnotherParent NULL        10/12/2014

The View would then output this as

ID      Title          ParentID      Uncommon
1       A Parent       NULL          ABooleanField:True,AnIntegerField:50
2       A Child        1             ABooleanField:False,AnIntegerField:100
3       AnotherParent  NULL          ADateField:10/12/2014

The IDs would be GUIDs in reality and the Uncommon field would be NVARCHAR(MAX)

Cheers

Stewart

add schema, data sample, and expected result. Also, what have you tried so far? — Horaciux, Sep 10 '14 at 19:42
Have updated Question would example of tables and desired result — Stewart Alan, Sep 10 '14 at 19:52
possible duplicate of [SQL Server SELECT to JSON function](http://stackoverflow.com/questions/6818441/sql-server-select-to-json-function) — Bulat, Sep 10 '14 at 21:56

Krishnraj Rana · Accepted Answer · 2014-09-11T04:54:03.637

Let say This is your

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 is the

Query

SELECT id
    ,Title
    ,ParentID
    ,(
        SELECT STUFF((
                    SELECT ',"ABooleanField": ' + ABooleanField + ',"AnIntegerField":' + cast(AnIntegerField AS VARCHAR(20))
                    FOR XML path('')
                        ,type
                    ).value('.', 'varchar(max)'), 1, 1, '')
        ) AS 'KeyValPair'
FROM table1
UNION ALL
SELECT id
    ,Title
    ,ParentID
    ,(
        SELECT STUFF((
                    SELECT ',"ADateField": ' + ADateField
                    FOR XML path('')
                        ,type
                    ).value('.', 'varchar(max)'), 1, 1, '')
        ) AS 'KeyValPair'
FROM table2

and you can also check here in SQL Fiddle for that.

Output.

ID      Title          ParentID      KeyValPair
1       A Parent       Null          ABooleanField: true, AnIntegerField:50
2       A Child        1             ABooleanField: false, AnIntegerField:100
3       AnotherParent  Null          ADateField: 10/12/2014

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

1 Answers1

schema

Query

Output.