You can try this, I'm not sure wif this will work if col1 is null for record 1, and has a value for record 2 but you can give it a try:
SELECT CASE WHEN col1 is Null THEN col1 END, CASE WHEN col2 is Null THEN col2 END, CASE WHEN col3 is Null THEN col3 END, CASE WHEN col4 is Null THEN col4 END, FROM mytable - j -----Original Message----- From: Patti G. L. Hall [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 18, 2002 11:56 AM To: CF-Talk Subject: Re: Returning columns with null values when specific columns are unknown at runtime Ok, I'm willing to give this a try... but I still have a question. My real table has 27 columns. I never know which columns will be null. Does that mean I want a case statement for each column that could possibly be null then? It seems like the select statment here is directly tailored to the example I used below... I will never know that much detail beforehand. I only put the example to give a visual of what I may need to return. If I send in primary key 1 then I want to return columns 3 and 4, but only if they acutally are null. The next time I run the query I'd be sendign in primary key 2 and in that case I'm returning columns 1, 3 and 4. So, I just wanted to check ... Can you explain how this is actually working? Is it relying on my explictly stating which columns I'm returning at any one time? If it is, then it's still not what I'm looking for. And perhaps what I'm looking for isn't possible. Thanks - Patti ----- Original Message ----- From: "Jim Curran" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Wednesday, September 18, 2002 11:26 AM Subject: RE: Returning columns with null values when specific columns are unknown at runtime > SELECT CASE WHEN pk = 1 THEN col3 END AS newcol1, > CASE WHEN pk = 1 THEN col4 END AS newcol2, > CASE WHEN pk = 2 THEN col1 END AS newcol1, > CASE WHEN pk = 2 THEN col2 END AS newcol2 > FROM mytable > > > -----Original Message----- > From: Patti G. L. Hall [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, September 18, 2002 10:54 AM > To: CF-Talk > Subject: SQL: Returning columns with null values when specific columns > are unknown at runtime > > > Is there a way to write a MSSQL 2k query that will return a result set that > contains only columns with null values when you don't know explicitly which > columns those will be? > > So if I have this data > > pk | col 1 | col 2 | col 3 | col 4| > 1 1 2 null null > 2 null 4 null null > > I'd like a query that would return col 3 and col 4 where pk = 1 or col 1, > col 3 and col 4 where pk=2. > > Is this possible? > -Patti > > > ______________________________________________________________________ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
