Solved

Vertical to horizontal data

Posted on 2011-02-22
9
651 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 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

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.

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…
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…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
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…

803 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