CODEWITHSUNDEEP
sql-serverpivot
MIL D.
MIL D.

Reputation: 33

How to transpose rows into columns in SQL Server?

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;

Upvotes: 0

Views: 482

Answers (1)

Jigar
Jigar

Reputation: 214

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

Upvotes: 2

Related Questions