June 1, 2015 at 10:56 am
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.
June 1, 2015 at 11:24 am
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.
June 1, 2015 at 11:47 am
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.
June 1, 2015 at 11:50 am
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
June 1, 2015 at 12:25 pm
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