Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 261
  • Last Modified:

tsql: How can I get this result set?

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
stevejknight
Asked:
stevejknight
  • 2
2 Solutions
 
tim_csCommented:
What you're looking for is a pivot statement.

http://msdn.microsoft.com/en-us/library/ms177410.aspx
0
 
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                  
0
 
tim_csCommented:
Totally read that wrong the first time.  Thought you were adding the values.  

0
 
LowfatspreadCommented:
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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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