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

# 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
• 2
2 Solutions

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

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

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

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

0

Commented:
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
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.

## Featured Post

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