• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 262
  • Last Modified:

tsql: How can I get this result set?

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

  • 2
2 Solutions
What you're looking for is a pivot statement.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
select max(case when node = 'N1' then value end) node1
, max(case when node = 'N2' then value end) node2
from yourtable
group by DateTime                  
Totally read that wrong the first time.  Thought you were adding the values.  

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now