SQL Server - list column names where fields are blank

In the following example column_name needs to be the name of the actual column where the field is blank

select 
  id,
  name,
  address,
  shoe_size,
  column_name 
from 
  table 
where 
  name = '' OR shoe_size = '' OR address = ''

What is the correct way of ac开发者_开发问答hieving this? (SQL Server)

eg:

select 
  id,
  name,
  address,
  shoe_size,
  (CASE name
     WHEN '' THEN 'name'
     ELSE 
       CASE shoe_size
         WHEN '' then 'shoe_size'
         else
           CASE address
             WHEN '' then 'address'
             ELSE
               'n/a'
           END
       END
  END) as column_name
from 
  table 
where 
  name = '' OR shoe_size = '' OR address = ''

Doing that for single columns is easy:

select 
  id,
  name,
  address,
  shoe_size,
  case 
     when name = '' then 'name'
     when show_size = '' then 'shoe_size' 
     when address = '' then 'address'
     else 'multiple fields are empty'
  end 
from 
  table 
where 
  name = '' OR shoe_size = '' OR address = ''

It gets ugly when you expect more than one column to be empty, because you need to consider all possible combinations:

  case 
     when name = '' then 'name'
     when shoe_size = '' then 'shoe_size' 
     when address = '' then 'address'
     when name = '' and shoe_size = '' then 'name, shoe_size'
     when name = '' and address = '' then 'name, address'
     when name = '' and shoe_size = '' and address = '' then 'name, address, shoe_size'
     ... (you get the picture) ...
  end 

here is a simple way that can be easily expanded to cover many columns:

--create a sample table to work with
DECLARE @YourTable table (id int,name varchar(10)
                         ,address varchar(10), shoe_size varchar(10))
--populate that table
INSERT @YourTable VALUES (1,'aa','bb','cc')
INSERT @YourTable VALUES (2,'','bb','cc')
INSERT @YourTable VALUES (3,'aa','','cc')
INSERT @YourTable VALUES (4,'aa','bb','')
INSERT @YourTable VALUES (5,'','','cc')
INSERT @YourTable VALUES (6,'aa','','')
INSERT @YourTable VALUES (7,'','bb','')
INSERT @YourTable VALUES (8,'','','')

SELECT 
    id
        ,name
        ,address
        ,shoe_size
        ,STUFF(
            CASE WHEN name        ='' THEN ', name'       ELSE '' END
            +CASE WHEN address    ='' THEN ', address'    ELSE '' END
            +CASE WHEN shoe_size  ='' THEN ', shoe_size'  ELSE '' END
            ,1,2, ''
         ) AS column_name 
    FROM @YourTable
    WHERE Name='' OR shoe_size='' OR address=''

OUTPUT:

id          name       address    shoe_size  column_name
----------- ---------- ---------- ---------- -------------------------
2                      bb         cc         name
3           aa                    cc         address
4           aa         bb                    shoe_size
5                                 cc         name, address
6           aa                               address, shoe_size
7                      bb                    name, shoe_size
8                                            name, address, shoe_size

(7 row(s) affected)