Solved

tsql: How can I get this result set?

Posted on 2011-09-15
4
229 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 230 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 20 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

829 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