Select column names with values from SQL Server database
Image from Unsplash by Sen

Select column names with values from SQL Server database

Fetching column names with its value in T-SQL using built in JSON methods

Fetching the data in it's original form from SQL relational database is the most common way of getting the data stored. For example, to get all column from Products table in Northwind database is pretty straight forward.

SELECT *
FROM [Northwind].[dbo].[Products]
    

This will output the data is well known structure, same structure it is stored in the database.

Select

The complexity starts when you want to flip the data and the column names. For specific reason you may want to have your data output in transformed way so that column names are populated vertically instead of horizontally as the default output.

One of the way to achieve this is to use SQL Server build in JSON functions to translate your column into property names

SELECT *
FROM [Northwind].[dbo].[Products]
FOR JSON AUTO
    

This will output a VARCHAR(MAX) value which is JSON serialized table structure.

Json

JSON string output could be suitable in case you are sending the data to external REST service from the stored procedure if that is a part of your ETL job. In our case with just a string we cannot do much as it is not really query-able.

To change this we need to invoke OPENJSON, another SQL Server build in JSON method to structure our array of objects in from JSON string

SELECT *
FROM OPENJSON((SELECT * FROM [Northwind].[dbo].[Products] FOR JSON AUTO))
    

The output of this query will be the following

Openjson Array

Now we have our initial JSON string response broken in each row as a JSON string, but this is still not usable to us and we can hardly perform any JOIN or other operation to involve this in querying the data.

Actual problem with this is the JSON string, specifically array wrapper of the JSON data. To solve this we can simply use SUBSTRING on top of our JSON sting, but luckily for us, OPENJSON alredy provides an argument to skip the array wrapper characters from the start and and of JSON string

SELECT *
FROM OPENJSON((SELECT * FROM [Northwind].[dbo].[Products] FOR JSON AUTO , WITHOUT_ARRAY_WRAPPER))
    

This will output the proper format, putting the column names into KEY column and values into VALUE column of the output

Openjson Table Single

The structure of the output is the proper one and we can actually use this for querying the data. However, you may notice that we actually have only one row for the whole table. In the structure like this we need additional identifier to know to which entity each row transformed structure exists.

For this we need to use CROSS APPLY and involve PK (primary key) of the table we are performing transformation

SELECT p.ProductID, t.[Name], t.[Value]
FROM [Northwind].[dbo].[Products] p
CROSS APPLY (SELECT [Key] AS [Name], [Value], [Type]
    FROM OPENJSON((SELECT * FROM [Northwind].[dbo].[Products] WHERE ProductId=p.ProductId FOR JSON AUTO , WITHOUT_ARRAY_WRAPPER))) t
    

Openjson Table Multiple

This way we know to which entity each of the transformed row belongs to and we can use it for the output or for further involvement in querying the data.

References

Disclaimer

Purpose of the code contained in snippets or available for download in this article is solely for learning and demo purposes. Author will not be held responsible for any failure or damages caused due to any other usage.


About the author

DEJAN STOJANOVIC

Dejan is a passionate Software Architect/Developer. He is highly experienced in .NET programming platform including ASP.NET MVC and WebApi. He likes working on new technologies and exciting challenging projects

CONNECT WITH DEJAN  Loginlinkedin Logintwitter Logingoogleplus Logingoogleplus

.NET

read more

JavaScript

read more

Umbraco CMS

read more

PowerShell

read more

Comments for this article