I am trying to create a SQL query that shows me messages with text1 and text3. I do not want to show anything else. I can find text1 and text1 id 2, but because there are other messages from other sources I cannot use id. I would also rather have everything in same column instead of making 2 columns.

select 
    a.id,
    a.message as cur
    b.message as next
from 
    mytable as a
join 
    mytable as b on a.id = b.id - 2
where 
    a.message like '%text1%'

My table:

Output should be like:

or this is okay too, but I would prefer first one:

CodePudding user response：

This can be done via LEAD analytic function as follows:

Query with a two-column result:

With CTE As (
Select [message], Lead([message], 2) Over (Order by id) As message_1
From Tbl
Where [source]='a' 
)
Select [message], message_1
From CTE
Where [message] Like '%text1%'

Result:

Query with a single column result:

With CTE As (
Select [message], Lead([message], 2) Over (Order by id) As message_1
From Tbl
Where source='a' 
)
Select V.[message]
From CTE Cross Apply (VALUES([message]), (message_1)) As V([message])
Where CTE.[message] Like '%text1%'

Result:

CodePudding user response：

It's difficult to understand the question, but based on the additional explanations in the comments (... I want to search for instances of text1 and also find and include related message text 2 rows from source a ...), two possible approaches to get the expected results are ROW_NUMBER() and LEAD(). Note, that you need an additional APPLY operator with a VALUES table value constructor to unpivot the columns:

Table:

SELECT *
INTO MyTable
FROM (VALUES
  (01, 'a', 'text1'),
  (02, 'a', 'text2'),
  (03, 'a', 'text3'),
  (04, 'a', 'text1'),
  (05, 'b', 'herp'),
  (06, 'c', 'derp'),
  (07, 'a', 'text4'),
  (08, 'a', 'text5'),
  (09, 'b', 'derp'),
  (10, 'a', 'text1'),
  (11, 'a', 'text6'),
  (12, 'a', 'text7')
) v (id, source, message)

Statement with ROW_NUMBER() and a self-join:

; WITH cte AS (
   SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS rn
   FROM MyTable
   WHERE source = 'a'
)    
SELECT c.message
FROM cte a
JOIN cte b ON a.rn = b.rn - 2
CROSS APPLY (VALUES (1, a.message), (2, b.message)) c (rn, message)
WHERE a.message like '%text1%'
ORDER BY a.rn, c.rn

Statement with LEAD():

; WITH cte AS (
   SELECT *, LEAD(message, 2) OVER (ORDER BY id) AS message2
   FROM MyTable
   WHERE source = 'a'  
)    
SELECT c.message
FROM cte a
CROSS APPLY (VALUES (1, a.message), (2, a.message2)) c (rn, message)
WHERE a.message LIKE '%text1%'
ORDER BY a.id, c.rn

Result:

message
-------
text1
text3
text1
text5
text1
text7