CODEWITHSUNDEEP
sql-servert-sqlazure
Sagar Waghmode
Sagar Waghmode

Reputation: 777

How can I add column names in each row from select query in SQL Server?

My table structure is something like this:

Domain    Label1    Label2   ...   LabelN  
foo.com    NULL       X      ...    NULL  
bar.com     Y         Z      ...    NULL  
.  
.  
xyz.com    NULL      NULL    ...      M

I want the output to be something like this (in text format):

foo.com,Label2:X    
bar.com,Label1:Y|Label2:Z  
.  
.  
xyz.com,Label3:M

Basically, I want to remove NULL entries as well as I want column labels to be present in the final output. I have tried to use XML path for it but it concatenates all rows together and doesn't remove NULL.

Has someone tried to solve this?

Upvotes: 0

Views: 1240

Answers (1)

Rob Reagan
Rob Reagan

Reputation: 7686

I don't see how you'd be able to do this by joining with the sys table in a single query and get the results you want. It could likely be done in a multi-step stored procedure, but it would be extremely ugly and not the cleanest solution. And I don't think that you really need to because your label columns are part of the fixed schema.

I think you need to perform a simple select from this table, then iterate through the results in code to build your single line of text per row. You can just concat in the label names at that time.

Upvotes: 1

Related Questions