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 '12 at 10:19 AM in Default

aRookieBIdev gravatar image

aRookieBIdev
2.3k 42 54 58

(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 '12 at 10:45 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
90.7k 19 21 74

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 '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)
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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x562
x38

asked: Jun 22 '12 at 10:19 AM

Seen: 1217 times

Last Updated: Jun 22 '12 at 12:29 PM