How to select columns that have some values only?

  • I have event table that containing multiple events, and many of them are empty. I'd like to select only the columns that have values in them.

    Here is an example:

    IF OBJECT_ID('tempdb..#events') IS NOT NULL

    DROP TABLE #events

    create table #events (eventId int,

    Category varchar(250),

    events1 varchar(250),

    events2 varchar(250),

    events3 varchar(250),

    events4 varchar(250),

    events5 varchar(250))

    insert into #events(eventId, Category, events1, events2, events3, events4, events5)

    values (1, 'meeting','event1', 'event2', null,null, 'event5'),

    (2, 'travel',null, 'event2', null,'event4', null),

    (3, 'meeting', 'event1','event2', null,null, 'event5'),

    (4, 'travel','event1', null, null,null, 'event5')

    In this case, I'd like to run a query like this one(skip Column Event3):

    Select eventId,Category,events1,events2,events4,events5 From #events

    Thanks all in advance.

  • Why would you need something like that? The only option is to use dynamic SQL.

    It seems as well that you have a design problem. If you can change the design of your tables, please do so to prevent having all those columns.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Luis Cazares (6/1/2015)


    Why would you need something like that? The only option is to use dynamic SQL.

    It seems as well that you have a design problem. If you can change the design of your tables, please do so to prevent having all those columns.

    Luis, I couldn't agree more.

    Jay, how about something like this?

    if OBJECT_ID('dbo.Catgories', 'u') is not null drop table dbo.Categories;

    create table dbo.Categories (

    ID integer not null identity(1, 1),

    constraint Categories_PK primary key (ID),

    Description varchar(255),

    EntryDate datetime not null default getdate());

    if OBJECT_ID('dbo.Events', 'u') is not null drop table dbo.Events;

    create table dbo.Events (

    ID integer not null identity(1, 1),

    constraint Events_PK primary key (ID),

    CategoryID integer not null,

    constraint Events_Categories_FK

    foreign key (CategoryID)

    references dbo.Categories (ID),

    Description Varchar(250) not null,

    EntryDate datetime not null default getdate());

    Then, to query the rows, the query becomes very simple and only returns those rows with data.

    SELECT c.Description Category, e.Description, e.EntryDate

    FROM dbo.Categories c

    INNER JOIN dbo.Events e ON e.CategoryID = c.ID

    ORDER BY e.EntryDate;

    You'll be able to filter events by date range, events by category or anything else without having to jump through a bunch of hoops to filter out the NULLs. There won't be any NULLs present.

  • SQL doesn't provide for conditionally returning columns. However, you can have a computed column that contains values from multiple columns appended together. For example, you can return your events as a comma separated list, and the ISNULL() function can be used to substitute a NULL value with an empty string.

    select eventid, category

    , isnull(events1+', ','')

    + isnull(events2+', ','')

    + isnull(events3+', ','')

    + isnull(events4+', ','')

    + isnull(events5+', ','')

    as events

    from #events;

    eventidcategory events

    1meeting event1, event2, event5,

    2travel event2, event4,

    3meeting event1, event2, event5,

    4travel event1, event5,

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • Thanks, everyone.

    As suggested, I'm not going to try it.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply