Re: I need a Query

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 06 Dec 2005 11:44:06 -0800
Message-ID: <1133898226.404904@jetspin.drizzle.com>

apps_km wrote:
> Hi ,
> If I have table A , It Contain 50 Columns and 1000 rows.In
> between 50 columns,
> some null columns also there.i want only show the output in not null
> columns.
>
> If you have above solution of query pl send to me.....
>
> Sample...table
>
>
> SNO S1 S2 S3 S4
> ----- --------- --------- --------- ---------
> 100 50 40
> 101 20 30
> 102 30 40
> 103 40 50
> 104 20 30
> 105 34
> 106 65 565
> 107 45 34
> 108 34 56
>
> I want the output
>
>
> SNO S1 S3
> ----- --------- --------- ---- ---------
> 100 50 40
> 101 20 30
> 102 30 40
> 103 40 50
> 104 20 30
> 105 34
> 106 65 565
> 107 45 34
> 108 34 56
>
> Bye...K.Murugesan

A table such as you have indicated, columns S1 .. S49 is ill-conceived. Fix the design problem and the issue goes away. This should be a three column table.

CREATE TABLE t (

SNO     NUMBER(5),
SCOL    NUMBER(2),
SVALUE  NUMBER(5));

INSERT INTO t (sno, scol, svalue) VALUES (100, 1, 50);
INSERT INTO t (sno, scol, svalue) VALUES (101, 1, 20);

INSERT INTO t (sno, scol, svalue) VALUES (102, 1, 30); INSERT INTO t (sno, scol, svalue) VALUES (107, 3, 34);

Then your query becomes:
SELECT *
FROM t
WHERE svalue IS NOT NULL;

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)

Received on Tue Dec 06 2005 - 13:44:06 CST