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.
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 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
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
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
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
- Northwind sample database for SQL Server
- SUBSTRING (Transact-SQL)
- OPENJSON (Transact-SQL)
- Use FOR JSON output in SQL Server and in client apps (SQL Server)
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.
Comments for this article