merging multiple selects to multiple columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

merging multiple selects to multiple columns

Author

Topic

mwidjaja
Starting Member

8 Posts

Posted - 2010-05-10 : 21:30:46

Hi,
I'm trying to join multiple selects together in 1 query with multiple columns. I have been successful on some tables using temp table, but not on this Events table.
Current output:
Message1, Message2 etc, they are all on separate queries.

I simply wanted to put them together like:
Message1 | Message2 | Message3 | Message4

The data type is ntext.
Below is the code but it throws "do not match table definition" error.

SET NOCOUNT ON
CREATE TABLE ##NODES

(Last7Days ntext,
Last30Days ntext,
Last60Days ntext,
ThisYear ntext)

INSERT ##NODES

--last7days

SELECT Events.Message

FROM
(Events INNER JOIN EventTypes Events_EventTypes ON (Events.EventType = Events_EventTypes.EventType))
WHERE
( EventTime BETWEEN 40285.625 AND 40285.6666666667 )
AND
(
(Events.Message LIKE '%SLA%') AND
(Events.Message LIKE '%7 Days%') AND
(
(Events.Message LIKE 'Terminal%') OR
(Events.Message LIKE 'File Sharing%'))
)
--last30days

(SELECT Events.Message

FROM
(Events INNER JOIN EventTypes Events_EventTypes ON (Events.EventType = Events_EventTypes.EventType))
WHERE
( EventTime BETWEEN 40285.4449884259 AND 40285.4583333333 )
AND
(
(Events.Message LIKE '%SLA%') AND
(Events.Message LIKE '%7 Days%') AND
(
(Events.Message LIKE 'Terminal%') OR
(Events.Message LIKE 'File Sharing%'))
))

--last60days

(SELECT Events.Message

FROM
(Events INNER JOIN EventTypes Events_EventTypes ON (Events.EventType = Events_EventTypes.EventType))
WHERE
( EventTime BETWEEN 40285.5909722222 AND 40285.6458333333 )
)

--thisyear

(SELECT Events.Message

FROM
(Events INNER JOIN EventTypes Events_EventTypes ON (Events.EventType = Events_EventTypes.EventType))
WHERE
( EventTime BETWEEN 40285.5909722222 AND 40285.6597222222 )
AND
(
(Events.Message LIKE '%SLA%') AND
(Events.Message LIKE '%7 Days%') AND
(
(Events.Message LIKE 'Terminal%') OR
(Events.Message LIKE 'File Sharing%'))
))

select * from ##nodes
drop table ##nodes

Any suggestion is greatly appreciated.

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-05-11 : 01:08:14

First of all you have a lot of typos, and secondly why you need those four columns for. If it is to specifiy the message period then keep one column for the message and another one for the period, and use CASE to calculate the period (not multiple selects) using something like this:


SELECT E.Message,
       CASE WHEN EventTime BETWEEN 40285.625 AND 40285.6666666667
                 THEN 'Last 7 Days'
            WHEN EventTime BETWEEN 40285.4449884259 AND 40285.4583333333
                 THEN 'Last 30 Days'
            WHEN EventTime BETWEEN 40285.5909722222 AND 40285.6458333333
                 THEN 'Last 60 Days'
            WHEN EventTime BETWEEN 40285.5909722222 AND 40285.6597222222
                 THEN 'This Year'
            ELSE NULL
       END AS period
  FROM Events AS E
       INNER JOIN
       EventTypes AS ET
       ON E.EventType = ET.EventType
 WHERE EventTime BETWEEN /*Min value of all periods*/ AND /*Max value of all periods*/
   AND E.Message LIKE '%SLA%'
   AND E.Message LIKE '%7 Days%'
   AND (E.Message LIKE 'Terminal%'
        OR E.Message LIKE 'File Sharing%')

And if you need the temp table just to save multiple results (not to enhance performance or something else) then you may not use it at all and return the query result immediately.

mwidjaja
Starting Member

8 Posts

Posted - 2010-05-11 : 04:04:49

i dont need one column for displaying the eventtime because the message itself has been filtered for each eventtime, so by simply display those filtered messages in different columns, it would be the main objective. like i put it on the first thread, i want something like:

Last 7 Days | last 30 Days | Last 60 Days | This Year
-------------------------------------------------------
aaaaaaaaaaa | bbbbbbbbbbbb | cccccccccccc | ddddddddddd

thanks.

mwidjaja
Starting Member

8 Posts

Posted - 2010-05-11 : 11:10:36

care to share some thoughts on this?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources