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!

 

[dnoeth]

Are there always 2 out of 4 items?

select
id,
coalesce(item1, item2, item3, item4) as item1,
coalesce(item4, item3, item2, item1) as item2
from tab

Dieter

 

[mrdenny]

A better option would be a more normalized table. Your current table design is considdered very poor table design.
What you want is a simple list.
ID, Item
1, 222
1, 333
2, 111
2, 333
3, 111
3, 444
...

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)