Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

tsql: How can I get this result set?

Posted on 2011-09-15
4
Medium Priority
?
258 Views
Last Modified: 2012-05-12
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
Comment
Question by:stevejknight
  • 2
4 Comments
 
LVL 15

Expert Comment

by:tim_cs
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

by:
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

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

0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 80 total points
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)

Open in new window

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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 …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question