Solved

tsql: How can I get this result set?

Posted on 2011-09-15
Medium Priority
254 Views
TSQL:
From a table such as this

DateTime                  Value                  Node
01/01/2011 10:00:00            10                  N1
01/01/2011 10:00:00            100                  N2
01/01/2011 10:01:00            20                  N1
01/01/2011 10:01:00            200                  N2
01/01/2011 10:02:00            30                  N1
01/01/2011 10:02:00            300                  N2
01/01/2011 10:03:00            40                  N1
01/01/2011 10:03:00            400                  N2
01/01/2011 10:04:00            50                  N1
01/01/2011 10:04:00            500                  N2
01/01/2011 10:05:00            60                  N1
01/01/2011 10:05:00            600                  N2

How can a query create the following result. The where clause should be related to date and time and node

I can get the value from node 1 easily but I also need node2 in the same result set.

Node1                  Node2
10                  100
20                  200
30                  300
40                  400
50                  500
60                  600

Thanks
0
Question by:stevejknight
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2

LVL 15

Expert Comment

ID: 36543462
What you're looking for is a pivot statement.

http://msdn.microsoft.com/en-us/library/ms177410.aspx
0

LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 920 total points
ID: 36543467
select max(case when node = 'N1' then value end) node1
, max(case when node = 'N2' then value end) node2
from yourtable
group by DateTime
0

LVL 15

Expert Comment

ID: 36543496
Totally read that wrong the first time.  Thought you were adding the values.

0

LVL 50

Assisted Solution

ID: 36543796
like this

``````select a.[value] as node1,b.[value] as node2
from (select x.*
,row_number() over (partition by [datetime] order by [value]) as rn
from yourtable as x
where node='n1') as a
full outer join
(select x.*
,row_number() over (partition by [datetime] order by [value]) as rn
from yourtable as x
where node='n2') as b
on a.[datetime]=b.[datetime]
and a.rn=b.rn
order by coalesce(a.[datetime],b.[datetime]),coalesce(a.rn,b.rn)
``````
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Suggested Courses
Course of the Month10 days, 10 hours left to enroll