Sql Query Result transpose

Question

Hi I have a table called MessageValue which has fields like

MessageId | Key | Value

following are the values

1054176 |File Type|Source
1054176 |Number of Records|43
1054177 |Number of Records|22
1054177 |File Type|Target

I need a result set like

MessageId | Filetype | NoOfRows

WITH VALUES LIKE

1054176 | Source | 43
1054177 | Target | 22

Kindly help me with this .

Thanks, Kannan

Answer 1

0

Yikes, that's one scary design. You really shouldn't store two completely different (although related) sets of data in the same table. But, it shouldn't be that rough to put them together

SELECT mv1.MessageId,
mv1.Key,
mv2.Value
FROM dbo.MessageValue AS mv1
JOIN dbo.MessageValue as mv2
ON mv1.MessageId = Mv2.MessageId
AND mv2.Key = 'Number of Records'

I think that, or something very similar will get you what you need.

answered Jun 22 '12 at 10:45 AM

Grant Fritchey ♦♦
62.6k ● 12 ● 20 ● 66

One more yikes for the column names ;) And this might be what you needed. I only extended Grant Fritchey's solution a bit

SELECT [mv1].[MessageId]
,       [mv1].[value] [Key]
,       [mv2].[value] [value]
FROM MessageValue AS mv1
JOIN MessageValue as mv2
ON mv1.MessageId = Mv2.MessageId
AND mv2.[Key] = 'Number of Records'
AND [mv1].[KEY] &lt;&gt; 'Number of Records'

Jun 22 '12 at 12:10 PM Usman Butt

Oops! Yeah, that's much better.

Jun 22 '12 at 12:15 PM Grant Fritchey ♦♦

Thanks Guys..

Jun 22 '12 at 12:29 PM aRookieBIdev

(comments are locked)

Sql Query Result transpose

Follow this question

Related Questions