Solved

Vertical to horizontal data

Posted on 2011-02-22
9
677 Views
Last Modified: 2012-05-11
I have an Table SQL that looks like this:
MeterNo
Channel  (5 channels)
Reading
TimeStamp

How can I query them to show as follows:

MeterNo
Channel1
Channel2
Channel3
Channel4
Channel5
Reading
TimeStamp

Thanks.
0
Comment
Question by:JaimeJegonia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 34950446
select MeterNo
, (select 'X' from YourTable where Channel = 1 where MeterNo = A.MeterNo) AS Channel1
, (select 'X' from YourTable where Channel = 2 where MeterNo = A.MeterNo) AS Channel2
, (select 'X' from YourTable where Channel = 3 where MeterNo = A.MeterNo) AS Channel3
, (select 'X' from YourTable where Channel = 4 where MeterNo = A.MeterNo) AS Channel4
, (select 'X' from YourTable where Channel = 5 where MeterNo = A.MeterNo) AS Channel5
, Reading, TimeStamp
FROM YourTable AS A
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 34950457
or:
select MeterNo
, case Channel when 1 then 'X' else '' END AS Channel1
, case Channel when 2 then 'X' else '' END AS Channel2
, case Channel when 3 then 'X' else '' END AS Channel3
, case Channel when 4 then 'X' else '' END AS Channel4
, case Channel when 5 then 'X' else '' END AS Channel5
, Reading, TimeStamp
FROM YourTable AS A
0
 

Author Comment

by:JaimeJegonia
ID: 34950966
emoreau:

Great you are really a genius guy!
Instead of 'X' I replaced it with Reading and it looks perfect.

Now I have a little problem, I want to get the reading difference or consumption every channel, can you help me?

Thanks.

 
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 70

Expert Comment

by:Éric Moreau
ID: 34950998
do you have a sample of the data yo have and the result you want?
0
 

Author Comment

by:JaimeJegonia
ID: 34951168
Here's the sample data:
Mtr#    Ch1    Ch2     Ch3      Ch4    Ch5      TimeStamp

A      98835      0      0      3622      0      2010-01-11 21:15:00.000
A      88837      0      0      3610      0      2010-01-11 21:30:00.000
A      88500      0      0      3510      0      2010-01-11 21:45:00.000
A      88400      0      0      3500      0      2010-01-11 22:15:00.000
A      88326      0      0      3420      0      2010-01-11 22:30:00.000
A      88300      0      0      3410      0      2010-01-11 22:45:00.000
A      88201      0      0      3401      0      2010-01-11 23:00:00.000
A      88001      0      0      3302      0      2010-01-11 23:15:00.000
A      87900      0      0      3300      0      2010-01-11 23:30:00.000
A      87600      0      0      3200      0      2010-01-11 23:45:00.000
A      88824      0      0      3120      0      2010-01-12 00:00:00.000
A      86010      0      0      3005      0      2010-01-12 00:15:00.000
A      85000      0      0      3000      0      2010-01-12 00:30:00.000
A      85452      0      0      2901      0      2010-01-12 00:45:00.000
A      85000      0      0      2850      0      2010-01-12 01:00:00.000
A      84562      0      0      2801      0      2010-01-12 01:15:00.000
A      83625      0      0      2750      0      2010-01-12 01:30:00.000
A      83602      0      0      2702      0      2010-01-12 01:45:00.000
A      82654      0      0      2610      0      2010-01-12 02:00:00.000
A      82100      0      0      2600      0      2010-01-12 02:15:00.000
A      81254      0      0      2560      0      2010-01-12 02:30:00.000
A      81025      0      0      2501      0      2010-01-12 02:45:00.000
A      81002      0      0      2450      0      2010-01-12 03:00:00.000
A      80251      0      0      2341      0      2010-01-12 03:15:00.000
B      832        0      0      6333      0      2010-01-12 03:30:00.000
B      802              0      0      6000      0      2010-01-12 03:45:00.000
B      800              0      0      5801      0      2010-01-12 04:00:00.000
B      799              0      0      5810      0      2010-01-12 04:15:00.000
B      362              0      0      3620      0      2010-01-12 04:30:00.000
B      260              0      0      3210      0      2010-01-12 04:45:00.000
B      120              0      0      3000      0      2010-01-12 05:00:00.000
B      100              0      0      2540      0      2010-01-12 05:15:00.000

If you notice the volume is decreasing, the difference is the consumption per time interval per Channel

Thanks.

0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 34951204
and what is the result you want?
0
 

Author Comment

by:JaimeJegonia
ID: 34951416

Mtr# Ch1       Ch2   Ch3   Ch4     Ch5     TimeStamp                                                          Consumption
A      98835      0      0      3622      0      2010-01-11 21:15:00.000                                      
A      88837      0      0      3610      0      2010-01-11 21:30:00.000                                      12
A      88500      0      0      3510      0      2010-01-11 21:45:00.000                                      100
A      88400      0      0      3500      0      2010-01-11 22:15:00.000                                      10
A      88326      0      0      3420      0      2010-01-11 22:30:00.000                                      80
A      88300      0      0      3410      0      2010-01-11 22:45:00.000                                      10
A      88201      0      0      3401      0      2010-01-11 23:00:00.000                                     9
A      88001      0      0      3302      0      2010-01-11 23:15:00.000                                      99
A      87900      0      0      3300      0      2010-01-11 23:30:00.000                                    2
A      87600      0      0      3200      0      2010-01-11 23:45:00.000                                    100
A      88824      0      0      3120      0      2010-01-12 00:00:00.000                                    80
A      86010      0      0      3005      0      2010-01-12 00:15:00.000                                    115
A      85000      0      0      3000      0      2010-01-12 00:30:00.000                                    5
A      85452      0      0      2901      0      2010-01-12 00:45:00.000                                    99
A      85000      0      0      2850      0      2010-01-12 01:00:00.000                                    51
A      84562      0      0      2801      0      2010-01-12 01:15:00.000                                    49
A      83625      0      0      2750      0      2010-01-12 01:30:00.000                                    51
A      83602      0      0      2702      0      2010-01-12 01:45:00.000                                    48
A      82654      0      0      2610      0      2010-01-12 02:00:00.000                                    92
A      82100      0      0      2600      0      2010-01-12 02:15:00.000                                    10
A      81254      0      0      2560      0      2010-01-12 02:30:00.000                                    40
A      81025      0      0      2501      0      2010-01-12 02:45:00.000                                    59
A      81002      0      0      2450      0      2010-01-12 03:00:00.000                                    51
A      80251      0      0      2341      0      2010-01-12 03:15:00.000                                    109
B      832              0      0      6333      0      2010-01-12 03:30:00.000                                    
B      802              0      0      6000      0      2010-01-12 03:45:00.000                                    333
B      800              0      0      5801      0      2010-01-12 04:00:00.000                                    199
B      799              0      0      5800      0      2010-01-12 04:15:00.000                                    1
B      362              0      0      3620      0      2010-01-12 04:30:00.000                                    180
B      260              0      0      3210      0      2010-01-12 04:45:00.000                                    410
B      120              0      0      3000      0      2010-01-12 05:00:00.000                                    210
B      100              0      0      2540      0      2010-01-12 05:15:00.000                                    460

Thanks.
0
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 500 total points
ID: 34951626
something like this:


create table #TestTable (
	MeterNo		Char(1), 
	Channel		int,
	Reading		int,
	TimeStamp	datetime
)

insert into #TestTable VALUES  ('A', 1, 98835, '2010-01-11 21:15:00.000')
insert into #TestTable VALUES  ('A', 4, 3622, '2010-01-11 21:15:00.000')
insert into #TestTable VALUES  ('A', 1, 88837, '2010-01-11 21:30:00.000')
insert into #TestTable VALUES  ('A', 4, 3610, '2010-01-11 21:30:00.000')
insert into #TestTable VALUES  ('A', 1, 88500, '2010-01-11 21:45:00.000')
insert into #TestTable VALUES  ('A', 4, 3510, '2010-01-11 21:45:00.000')
insert into #TestTable VALUES  ('A', 1, 88400, '2010-01-11 22:15:00.000')
insert into #TestTable VALUES  ('A', 4, 3500, '2010-01-11 22:15:00.000')

insert into #TestTable VALUES  ('B', 1, 832, '2010-01-12 03:30:00.000')
insert into #TestTable VALUES  ('B', 4, 6333, '2010-01-12 03:30:00.000')
insert into #TestTable VALUES  ('B', 1, 802, '2010-01-12 03:45:00.000')
insert into #TestTable VALUES  ('B', 4, 6000, '2010-01-12 03:45:00.000')
insert into #TestTable VALUES  ('B', 1, 800, '2010-01-12 04:00:00.000')
insert into #TestTable VALUES  ('B', 4, 5801, '2010-01-12 04:00:00.000')


select row_number() over (order by MeterNo, TimeStamp) as RowID, *
into #TestTable2
from (
	select distinct MeterNo
	, (select reading from #TestTable where Channel = 1 and MeterNo = A.MeterNo and TimeStamp = A.TimeStamp ) AS Channel1
	, (select reading from #TestTable where Channel = 2 and MeterNo = A.MeterNo and TimeStamp = A.TimeStamp ) AS Channel2
	, (select reading from #TestTable where Channel = 3 and MeterNo = A.MeterNo and TimeStamp = A.TimeStamp ) AS Channel3
	, (select reading from #TestTable where Channel = 4 and MeterNo = A.MeterNo and TimeStamp = A.TimeStamp ) AS Channel4
	, (select reading from #TestTable where Channel = 5 and MeterNo = A.MeterNo and TimeStamp = A.TimeStamp ) AS Channel5
	, TimeStamp
	FROM #TestTable AS A 
) AS B

SELECT T1.*
, T2.Channel1 - T1.Channel1 as DiffChannel1
, T2.Channel2 - T1.Channel2 as DiffChannel2
, T2.Channel3 - T1.Channel3 as DiffChannel3
, T2.Channel4 - T1.Channel4 as DiffChannel4
, T2.Channel5 - T1.Channel5 as DiffChannel5
FROM #TestTable2 AS T1
left join #TestTable2 as T2
on T2.RowID = T1.RowID -1
and T2.MeterNo = T1.MeterNo

drop table #TestTable
drop table #TestTable2

Open in new window

0
 

Author Closing Comment

by:JaimeJegonia
ID: 34956466
Perfect!  Thanks.
0

Featured Post

Veeam gives away 10 full conference passes

Veeam is a VMworld 2017 US & Europe Platinum Sponsor. Enter the raffle to get the full conference pass. Pass includes the admission to all general and breakout sessions, VMware Hands-On Labs, Solutions Exchange, exclusive giveaways and the great VMworld Customer Appreciation Part

Question has a verified solution.

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

In this article I will describe the Detach & Attach 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.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

622 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