CODEWITHSUNDEEP
sql-server-2008names
mac
mac

Reputation: 65

selecting column names where data is not null or blank

SQL Server 2008

I have a table MyTable with columns A, B, C, D

When I select a row I want a list of only those columns with non-null/blanks. The result set would be

A
C
D

if B was null in my row.

Actually, there may be a column E someday. But I can get all possible column names from another table and need to check if MyTable has any of them and if so which ones have data for the row I selected

Thus:

select * from MyTable where ID = 6

select ColumnName from AllColumnNames

There's gotta be a way to do this in one query?

Upvotes: 2

Views: 2811

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

This will convert your table to XML in the CTE and then it uses XQuery to find the node names that does not have empty values. This will work if your column names does not break the rules for XML node names.

;with C(TableXML) as
(
  select *
  from MyTable
  where ID = 6
  for xml path('T'), elements xsinil, type
)
select T.X.value('local-name(.)', 'sysname') as ColumnName
from C
  cross apply C.TableXML.nodes('/T/*') as T(X)
where T.X.value('.', 'nvarchar(max)') <> ''

Try here: https://data.stackexchange.com/stackoverflow/query/59187

Add this the the where clause if you want to exclude the ID column as well.

T.X.value('local-name(.)', 'sysname') <> 'ID'

Upvotes: 3

Related Questions