June 24, 2006 at 1:14 am
Hello all,
Can any one help me for the below issue.
I have created a table and when select all the columns the result is as follow.
marks1 marks2 marks3 marks4
---------------------------------------------------
1 NULL NULL NULL
NULL 1 NULL NULL
NULL NULL 1 NULL
NULL NULL NULL 1
---------------------------------------------------
1 1 1 1
June 24, 2006 at 2:02 am
Check this out this may help you.
CREATE TABLE t1 (a int, b int, c int,d int )
INSERT INTO t1 VALUES(1,NULL,NULL,NULL)
INSERT INTO t1 VALUES(NULL,1,NULL,NULL)
INSERT INTO t1 VALUES(NULL,NULL,1,NULL)
INSERT INTO t1 VALUES(NULL,NULL,NULL,1)
select * from t1
select MAX(COALESCE(a,'')) as c1
,MAX(COALESCE(b,''))as c2
,MAX(COALESCE(c,''))as c3
,MAX(COALESCE(d,''))as c4
from t1
------------
Prakash Sawant
http://psawant.blogspot.com
June 24, 2006 at 2:17 am
Thanks for the solution....
but this one is the lengthy process right .If i have more than that no of col's?
Can u give me a query for dynamic pupose....
any way thanks for the update.....
Regards,
Barath.
June 24, 2006 at 2:37 am
This is the best way to do it.
if you want your query should be dynamic then you have to write a sp useing cursor in it which will be real pain then this query.
------------
Prakash Sawant
http://psawant.blogspot.com
June 24, 2006 at 2:47 am
Is it possible through the syscolumns table?
June 24, 2006 at 2:55 am
It may be possible you can try that...
------------
Prakash Sawant
http://psawant.blogspot.com
June 24, 2006 at 2:57 am
Ok I will try and let u know the result.....
any other solution?
Thanks,
Barath.
June 24, 2006 at 7:58 am
See Stored Proc below
--------------------------------------------------
CREATE
TABLE t1 (a int, b int, c int,d int )
INSERT INTO t1 VALUES(1,NULL,NULL,NULL)
INSERT
INTO t1 VALUES(NULL,1,NULL,NULL)
INSERT
INTO t1 VALUES(NULL,NULL,1,NULL)
INSERT
INTO t1 VALUES(NULL,NULL,NULL,1)
select
* from t1
select
MAX(a) as c1
,MAX(b)as c2
,max(c)as c3
,max(d)as c4
from t1
--------------------------------------------------------------------
-------------------------------------------------------------------
alter
proc MaxCol
(
@table_name
varchar(20) --Pass Table Name i.e 't1'
)
as
declare
@sql nvarchar(4000), @CurrentCol tinyint, @TotCols tinyint
declare
@tab table(id tinyint identity, ColumnName varchar(50))
insert
select
c.name
from
syscolumns c with (nolock)
join
sysobjects o with (nolock)
on o.id = c.id
where
o.name = @table_name
select
@sql = 'select ' ,@CurrentCol =1 , @TotCols = scope_identity()
while
@CurrentCol <=@TotCols
begin
select
@sql = @sql + 'max('+ColumnName+')'+ColumnName+','
from
where
id =@CurrentCol
set
@CurrentCol = @CurrentCol +1
end
set
@sql = substring(@sql,1,len(@sql)-1)+' from '+@table_name
@sql
exec
sp_executesql @sql
go
June 24, 2006 at 8:07 am
I cant imagine that performance will ever be an issue with this kind of sp. Infact you shouldnt really build queries like this into prodution code at all. Anyway here is a a revised version using an update rather than a cursor and should therefore run a little faster...
alter
proc MaxCol --'t1'
(
@table_name
varchar(20) --Pass Table Name i.e 't1'
)
as
declare
@sql nvarchar(4000), @CurrentCol tinyint, @TotCols tinyint
declare
@tab table(id tinyint identity, ColumnName varchar(50))
insert
select
c.name
from
syscolumns c with (nolock)
join
sysobjects o with (nolock)
on o.id = c.id
where
o.name = @table_name
select
@sql = 'select ' ,@CurrentCol =1 , @TotCols = scope_identity()
update
set
@sql = @sql + 'max('+ColumnName+')'+ColumnName+','
from
set
@sql = substring(@sql,1,len(@sql)-1)+' from '+@table_name
@sql
exec
sp_executesql @sql
go
June 26, 2006 at 2:02 am
Hello Barath,
your question suggests that the data model is incorrect, or we are missing something. What is the reason for the desired output? What if one of the rows has values in more than one column, what if there are several values in one column?
Example:
marks1 marks2 marks3 marks4
---------------------------------------------------
1 NULL NULL NULL
NULL 1 2 NULL
NULL NULL 1 NULL
NULL NULL NULL 1
And as to the "dynamic" fetching of column names, I agree with Jules - you shouldn't do that in production. You should always know what columns there are and what do you want to do with them.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply