Select Page

Clearly I am having a sql brain fart but for some reason I cannot think of a better way to select data from a column and have it return in a single row. To better illustrate what I am looking to accomplish I will reference the sys.messages system view.

Here is an example dataset that I will be working with.

select * from sys.messages where (message_id = 204)

This is the route I came up with from the top of my head; however, something tells me I could do this differently but I cannot think of anything else.

SET @MsgID = 204

SELECT m1.message_id 
FROM	sys.messages m1
			,sys.messages m2
			,sys.messages m3
			,sys.messages m4
			,sys.messages m5
			,sys.messages m6
			,sys.messages m7
			,sys.messages m8
			,sys.messages m9
			,sys.messages m10
			,sys.messages m11			
WHERE (m1.message_id = @MsgID and m1.language_id = 1033)
AND (m2.message_id = @MsgID and m2.language_id = 1031)
AND (m3.message_id = @MsgID and m3.language_id = 1036)
AND (m4.message_id = @MsgID and m4.language_id = 1041)
AND (m5.message_id = @MsgID and m5.language_id = 3082)
AND (m6.message_id = @MsgID and m6.language_id = 1040)
AND (m7.message_id = @MsgID and m7.language_id = 1049)
AND (m8.message_id = @MsgID and m8.language_id = 1046)
AND (m9.message_id = @MsgID and m9.language_id = 1028)
AND (m10.message_id = @MsgID and m10.language_id = 1042)
AND (m11.message_id = @MsgID and m11.language_id = 2052)

This is the desired results

If you know of a better method please elaborate, I beg you!

Share This