x

Sql Query Result transpose

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

more ▼

asked Jun 22, 2012 at 10:19 AM in Default

avatar image

aRookieBIdev
2.8k 56 65 71

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Jun 22, 2012 at 10:45 AM

avatar image

Grant Fritchey ♦♦
137k 20 43 81

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] <> 'Number of Records'
Jun 22, 2012 at 12:10 PM Usman Butt

Oops! Yeah, that's much better.

Jun 22, 2012 at 12:15 PM Grant Fritchey ♦♦

Thanks Guys..

Jun 22, 2012 at 12:29 PM aRookieBIdev
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x749
x52

asked: Jun 22, 2012 at 10:19 AM

Seen: 1795 times

Last Updated: Jun 22, 2012 at 12:29 PM

Copyright 2016 Redgate Software. Privacy Policy