Skiping empty columns

[SQLBeginer]

I have the following table:

ID ITEM1 ITEM2 ITEM3 ITEM4
1 222 333
2 111 333
3 111 444

I need to create another table to be something like this:

ID ITEM1 ITEM2
1 222 333
2 111 333
3 111 444

How can I do this?
Please help!

 

[SkipVought]

SB,

Your basic table structure seems NOT to be normalized.

A Normalized structure would be
[tt]
ID ITEM
1 222
1 333
2 111
2 333
3 111
3 444
[/tt]
or
[tt]
ID ITEM VALUE
1 ITEM2 222
1 ITEM3 333
2 ITEM1 111
2 ITEM3 333
3 ITEM1 111
3 ITEM4 444
[/tt]

then the report you requested would be a simple crosstab query.


Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[PHV]

Create a saved UNION of 6 queries:
SELECT ID, Item1, Item2 FROM yourTable WHERE Item1 IS Not Null And Item2 Is Not Null
UNION SELECT ID, Item1, Item3 FROM yourTable WHERE Item1 IS Not Null And Item3 Is Not Null
SELECT ID, Item1, Item4 FROM yourTable WHERE Item1 IS Not Null And Item4 Is Not Null
SELECT ID, Item2, Item3 FROM yourTable WHERE Item2 IS Not Null And Item3 Is Not Null
SELECT ID, Item2, Item4 FROM yourTable WHERE Item2 IS Not Null And Item4 Is Not Null
SELECT ID, Item3, Item4 FROM yourTable WHERE Item3 IS Not Null And Item4 Is Not Null

Now you may build a maketable query based on the above saved query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244