Select * Except Null Fields

Question

i'm having a problem with my database. I have the folowing:

create table book
(
    name_b varchaR(50) primary key,
    typee varchaR(50) not null,
    nmbr integer unique not null,
    yeare numeric(4,0) default null,
)

create table story
(
    id integer identity(1,1) primary key,
    name_s varchar(100) not null,
    chars varchar(100) not null,
    code varchaR(200) null,
    artist varchar(100) default null,
    argument varchar(100) default null,
    book_ref_name varchar(50) references book(name_b),
    book_ref_nmbr integer references book(nmbr)
)

insert into story values
('StoryName1','StoryChars1',null,default,default,'BookName1',13),
('StoryName2','StoryChars2',null,default,default,'BookName2',35),
('StoryName3','StoryChars3',null,default,default,'BookName3',125)

insert into book values
('BookName1','Type1',13,default),
('BookName2','Type2',35,default),
('BookName3','Type3',125,default)


UPDATE story
SET code = name_s + '-' + chars + '-' + book_ref_name + '-' + Convert(varchar(50), book_ref_nmbr)
WHERE code IS NULL

how can i make a SELECT statement that selects * from STORY except the null fields. for example if a story has no argumentist/artist i dont want that field to show up.

for example:

the results of the select * from story where name_s = 'StoryName1' would be the folowing table but because those two fields are NULL i would want it to be like this table

for the last table to show up that way i did this select id,name_s,chars,code,book_ref_name,book_ref_nmbr from story where name_s = 'StoryName1'

im trying to do something like this (this code obviously doesnt exist is just an example to try to explain me better)

select ""notnullcollumns"" from story where name_s = 'StoryName1'

Answer 1

You could do something like this:

SELECT id, 
      'code' AS a_type, code AS a_value
  FROM story
UNION
SELECT id, 
      'artist' AS a_type, artist AS a_value
  FROM story
UNION
SELECT id, 
      'argument' AS a_type, argument AS a_value
  FROM story
UNION...

but if that looks good to you then you probably need to change your design.