September 27, 2011 at 4:46 am
Suppose I have one table that contains 10 columns and all can accept null value. Suppose there are 100 records exist. How we can get records like column value that succeeds by null column?
September 27, 2011 at 5:02 am
An example and some test data would have been helpful here. But I think what you are looking for is a combination of a cross apply and a union all, something like this:
-- v -- v -- This part you should have provided as a DDL and sample data script -- v -- v --
declare @MyTable table (
col1 int null,
col2 int null,
col3 int null
);
insert @MyTable(col1, col2, col3)
select 1, null, null
union all select null, 2, null
union all select null, 3, 4
-- ^ -- ^ -- This part you should have provided as a DDL and sample data script -- ^ -- ^ --
-- v -- v -- My idea of what you meant -- v -- v --
select x.value
from @MyTable tbl
cross apply (
select tbl.col1 as value where tbl.col1 is not null
union all select tbl.col2 as value where tbl.col2 is not null
union all select tbl.col3 as value where tbl.col3 is not null
) x
If this is not what you are looking for, please read 'Posting Data Etiquette' in my footer text and complete your question with more details.
September 27, 2011 at 5:41 am
Thanks for your reply and I got the solution.
September 27, 2011 at 7:30 am
Can you give the solution here so that we may all learn from it?
September 27, 2011 at 11:11 pm
Thanks once again for reply… Rozema, I got an idea from your example.
Once again I am putting my question for better understanding. See I have a table which doesn’t have any key and all can contain NULL value. So, if tables have values in the columns like:
Col1----Col2----Col3----Col4-----Col5-----Col6------Col7-----Col8------Col9----Col10
3------- 1--------1--------2-------NULL-----NULL------NULL----NULL-----NULL----NULL
22------23-------456-----35------332-------NULL-----NULL----NULL-----NULL----NULL
1-------1--------22------2345----34--------34-------32-------NULL-----NULL----NULL
2456-----NULL----NULL----NULL----NULL------NULL----NULL-----NULL-----NULL----NULL
1--------1--------345------24------2345-----1--------34-------1--------1--------NULL
1--------1--------1--------1--------1--------1--------345------1--------655------NULL
1--------1--------1--------1--------1--------1--------1--------1--------1--------1
Then I need to a solution like:
Column Name -----Column Value
Col4-------------- 2
Col5--------------332
Col7--------------32
Col1--------------2456
Col9--------------1
Col9--------------655
September 28, 2011 at 1:40 am
See, that is why we ask you to provide us with a: a script creating the table and some sample data plus b: the expected output, so we know what you need to work with and we can test our examples against that same data. Now that you've given the expected output, here's an example giving the name plus the value for every non-null column.
select x.name as [Column Name], x.value as [column Value]
from @MyTable tbl
cross apply (
select 'col1' as name, tbl.col1 as value where tbl.col1 is not null
union all select 'col2', tbl.col2 as value where tbl.col2 is not null
union all select 'col3', tbl.col3 as value where tbl.col3 is not null
) x
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply