Select Multiple Rows Into Multiple Columns

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.

DECLARE @MsgID INT
SET @MsgID = 204

SELECT m1.message_id 
			,m1.language_id
			,m1.severity
			,m1.text
			,m2.text
			,m3.text
			,m4.text
			,m5.text
			,m6.text
			,m7.text
			,m8.text
			,m9.text
			,m10.text
			,m11.text
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!

Advertisements

One thought on “Select Multiple Rows Into Multiple Columns

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s