July 22, 2006 at 10:36 am
Dear friends,
How can i get null value fields from a table
I have a table with fields id,address1,address2,city,country
If in the 1st record address1 and address2 field is null and in the second record city field is null and the 3rd record with country field blank then the query should return
ID fieldname
1 address1
1 address1
2 city
3 country
Thanks
July 22, 2006 at 11:56 pm
SELECT ID, 'Address1'
FROM Table
WHERE Address1 IS NULL
UNION
SELECT ID, 'Address2'
FROM Table
WHERE Address2 IS NULL
UNION
SELECT ID, 'City'
FROM Table
WHERE city IS NULL
UNION
SELECT ID, 'Country'
FROM Table
WHERE country IS NULL
ORDER BY ID
_____________
Code for TallyGenerator
July 23, 2006 at 10:29 am
Thanks for your reply..
My table contains about 65 fields so is there any other way to solve my problem.
Thanks
July 23, 2006 at 3:54 pm
July 23, 2006 at 4:22 pm
set
nocount on
declare
@col nvarchar(200), @n int
select
@n = 1
While
@n is not null
begin
Select
@col = column_name
from
information_schema.columns
where
table_name = 'DimProduct'
and ordinal_position = @n
IF
@n > 1 Print 'UNION ALL'
'select productkey, '''+@col+''' as [Column_name] '
'From DimProduct '
'where '+@col+' is null'
Select
@n = min(Ordinal_position)
From
information_schema.columns
where
table_name = 'DimProduct'
and ordinal_position > @n
END
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply