0

I have been working on how to transpose or pivot this table but after working for so many hours I am still stuck on this, can you please help me?

My table looks like this:

CREATE TABLE Table1
(
     `Time` datetime, 
     `IN` int, 
     `OUT` int
);

INSERT INTO Table1 (`Time`, `IN`, `OUT`)
VALUES ('2017-04-05 15:53:00', 40, '35'),
       ('2017-04-05 15:24:00', 40, '35'),
       ('2017-04-05 15:23:00', 40, '35'),
       ('2017-04-05 14:22:00', 42, '40'),
       ('2017-04-05 14:21:00', 42, '40'),
       ('2017-04-05 14:20:00', 42, '40'),
       ('2017-04-05 13:19:00', 33, '30'),
       ('2017-04-05 13:18:00', 33, '30'),
       ('2017-04-05 13:17:00', 33, '30'),
       ('2017-04-05 13:16:00', 33, '30'),
       ('2017-04-05 13:15:00', 33, '30'),
       ('2017-04-05 12:14:00', 29, '25'),
       ('2017-04-05 12:13:00', 29, '25'),
       ('2017-04-05 12:12:00', 29, '25'),
       ('2017-04-05 12:11:00', 29, '25'),
       ('2017-04-05 11:14:00', 35, '33'),
       ('2017-04-05 11:13:00', 35, '33'),
       ('2017-04-05 11:12:00', 35, '33'),
       ('2017-04-05 11:11:00', 35, '33');

I want my output to be something similar to this

enter image description here

The value in 'IN' column is always the same for that particular hour even entered several time, so I just need 1 value to be in my table same as with the value in 'OUT' column

The Accum is the accumulated sum for each hour, just make it optional if it is even possible.

I am doing the first part which is to transpose the 2 columns with this query

SELECT 
    'IN' AS A, [2017-04-05 15:53:00], [2017-04-05 14:22:00],,,,
FROM 
    (SELECT 
         [Time], [in], [out] 
     FROM 
         Table1) AS SourceTable
PIVOT
    (MAX([IN])
        FOR [time] IN ([2017-04-05 15:53:00], [2017-04-05 14:22:00],,,,)) AS PivotTable;
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
MIL D.
  • 33
  • 1
  • 6

1 Answers1

2

You can try this, 

select [Type], [11], [12], [13], [14], [15]
from
(
  Select [Time], [Type], value
  from (
    select t2.*, sum([OUT]) over (order by [TIME]) as ACCUM
    from (Select Distinct DATEPART(HOUR, Time) as [Time], [IN], [OUT] from table1) t2
    ) A
  unpivot
  (
    value for [TYPE] in ([IN],[OUT],[ACCUM])
  ) unpiv
) src
pivot
(
  sum(value)
  for [Time] in ([11], [12], [13], [14], [15])
) piv

Get more details from here to make it dynamic, Simple way to transpose columns and rows in Sql? Accumulate a summarized column

Community
  • 1
  • 1
Jigar
  • 216
  • 1
  • 8
  • Perfect!!Thanks a lot @Jigar – MIL D. Apr 06 '17 at 17:04
  • Hi @jigar sorry but I need additional help here... as I found out that value on the OUT column sometimes changes and so what I want now is to get the latest value based on time. Also I want to have a query to show the Accum value based on a specified time. – MIL D. Apr 07 '17 at 14:06
  • For latest OUT value based on time, you can replace below source table query `Select Distinct DATEPART(HOUR, Time) as [Time], [IN], [OUT] from table1`. All ACCUM values are based on the time (11,12,13,14 and 15) I guess. – Jigar Apr 07 '17 at 18:45