Solved

Vertical to horizontal data

Posted on 2011-02-22
9
642 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
  • 5
  • 4
9 Comments
 
LVL 69

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 69

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
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 34950998
do you have a sample of the data yo have and the result you want?
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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 69

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 69

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
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.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

895 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now