Vertical to horizontal data

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.
JaimeJegoniaAsked:
Who is Participating?
 
Éric MoreauConnect With a Mentor Senior .Net ConsultantCommented:
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
 
Éric MoreauSenior .Net ConsultantCommented:
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
 
Éric MoreauSenior .Net ConsultantCommented:
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
JaimeJegoniaAuthor Commented:
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
 
Éric MoreauSenior .Net ConsultantCommented:
do you have a sample of the data yo have and the result you want?
0
 
JaimeJegoniaAuthor Commented:
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
 
Éric MoreauSenior .Net ConsultantCommented:
and what is the result you want?
0
 
JaimeJegoniaAuthor Commented:

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
 
JaimeJegoniaAuthor Commented:
Perfect!  Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.