Need help in creating a query that dismisses empty

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Need help in creating a query that dismisses empty

Author

Topic

royc
Yak Posting Veteran

53 Posts

Posted - 2007-03-06 : 08:16:32

Hello,

Suppose I have a table with this format and data in it:

A B C D
X X
X X
X X X
X X

I would like to create a query that will get the results from the table but without columns which are empty.
In the example above, the query will result only in fields A,C and D since B doesn't contain any data.
Also, I will be running this query on tables with different columns structure so the columns names cannot appear in the query.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-06 : 08:23:09

"In the example above, the query will result only in fields A,C and D since B doesn't contain any data."
But the sample data you posted shows that in fact column D doesn't contain data (or am I seeing things?)

"Also, I will be running this query on tables with different columns structure so the columns names cannot appear in the query."
Normally, this requirement means your DB design is screwed up and it is the time for you to think whether you want to continue with it or change the design.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

royc
Yak Posting Veteran

53 Posts

Posted - 2007-03-06 : 08:28:50

Hi,

It got shifted, so it's D, you got the idea.
I am quering SAP BW tables, go tell them to change their design... :)
"The IMPOSSIBLE is often UNTRIED"
So, is it possible?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-06 : 08:36:59

It is possible but that does not mean you should go for everything that is possible. In fact it will defeat the basic purpose of having it in the back-end.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

royc
Yak Posting Veteran

53 Posts

Posted - 2007-03-06 : 08:39:31

I'll take my chances, can you please share the query ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-06 : 08:44:16

So this is your sample data ?


declare @table table
(
	A	char(1),
	B	char(1),
	C	char(1),
	D	char(1)
)
insert into @table
select	'X', '', 'X', '' 	union all
select	'X', '', 'X', '' 	union all
select	'X', '', 'X', 'X' 	union all
select	'X', '', 'X', ''

How do you want the result to be ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-06 : 09:07:29

All records in a resultset have the same number of columns.

What you are trying to achieve leads you to the dark side of dynamic sql.

Peter Larsson
Helsingborg, Sweden

royc
Yak Posting Veteran

53 Posts

Posted - 2007-03-06 : 09:11:30

That dark...?
Can't we try at least?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-06 : 09:25:24

quote:
Originally posted by royc

That dark...?
Can't we try at least?

Yes. But how can we try if you did not at least tell us what do you expect as a result ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-06 : 09:26:17

He wants only columns A, C and D in the resultset, as there are no data at all for the column B in all records.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-06 : 09:28:24

Basically

SELECT A, C, D
FROM Table1

because all records has no value for column B, column b should be omitted.

BUT OH MAN!!! This is really going to confuse your front-end.

Peter Larsson
Helsingborg, Sweden

royc
Yak Posting Veteran

53 Posts

Posted - 2007-03-06 : 09:33:35

Hi Peso,

But as I said, I am going to use it on few tables which I don't know their columns in advance, trherefore the column names shouldn't be at the query.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-06 : 09:35:41

Beware. The force of the dark side is strong.


-- Create table for testing
create table #table
(
	A	char(1),
	B	char(1),
	C	char(1),
	D	char(1)
)
insert into #table
select	'X', '', 'X', '' 	union all
select	'X', '', 'X', '' 	union all
select	'X', '', 'X', 'X' 	union all
select	'X', '', 'X', ''

-- Here come the dark side
declare @sql nvarchar(4000)

select	@sql = 'select ' 
if exists (select * from #table where A <> '') select @sql = @sql + 'A,'
if exists (select * from #table where B <> '') select @sql = @sql + 'B,'
if exists (select * from #table where C <> '') select @sql = @sql + 'C,'
if exists (select * from #table where D <> '') select @sql = @sql + 'D,'
select	@sql = left(@sql, len(@sql) - 1) + char(13)
select	@sql = @sql + 'from #table'

exec(@sql)

drop table #table

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-06 : 09:37:03

Simple and direct way:

declare @table table
(
	A	char(1),
	B	char(1),
	C	char(1),
	D	char(1)
)
insert into @table
select	'X', '', 'X', '' 	union all
select	'X', '', 'X', '' 	union all
select	'X', '', 'X', 'X' 	union all
select	'X', '', 'X', ''

declare @sql varchar(5000)

if exists(select * from @table where isnull(a, '') <> '')
	set @sql = coalesce(@sql + ',', '') + 'a'

if exists(select * from @table where isnull(b, '') <> '')
	set @sql = coalesce(@sql + ',', '') + 'b'

if exists(select * from @table where isnull(c, '') <> '')
	set @sql = coalesce(@sql + ',', '') + 'c'

if exists(select * from @table where isnull(d, '') <> '')
	set @sql = coalesce(@sql + ',', '') + 'd'

if @sql is not null
	set @sql = 'Select ' + @sql + ' from @table'

print @sql

Mind it, the no. of IF EXISTS statements will increase linearly depending on no. of columns.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-06 : 09:39:26

Harsh, you can't use dynamic sql on the table variable. It will be out of scope.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-06 : 09:40:56

quote:
Originally posted by royc

I am going to use it on few tables which I don't know their columns in advance, trherefore the column names shouldn't be at the query.

Horrible database/table design...

Peter Larsson
Helsingborg, Sweden

royc
Yak Posting Veteran

53 Posts

Posted - 2007-03-06 : 09:41:26

But again, you are assuming fixed number of column.
I don't know the number of columns at each table....

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-06 : 09:42:55

quote:
Originally posted by khtan

Harsh, you can't use dynamic sql on the table variable. It will be out of scope.

KH

I know...that's why I just printed the sql statement, not executed it!

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-06 : 09:44:27

quote:
Originally posted by Peso

quote:
Originally posted by royc

I am going to use it on few tables which I don't know their columns in advance, trherefore the column names shouldn't be at the query.
Horrible database/table design...

Peter Larsson
Helsingborg, Sweden