Solved

Vertical to horizontal data

Posted on 2011-02-22
9
629 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
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.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

758 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

24 Experts available now in Live!

Get 1:1 Help Now