How can i transpose an .csv into a sql table with an SSIS Job?

Question

i want to transpose a SQL table into the following format. Can someone tell me how this is possible? I'am new in this topic, so i dont know so much about that. I already tried with the Pivot function of SSIS but i wont work.

Can someone please tell me an SQL Statement or an SSIS Job to help me with this Problem?

Answer 1

Not sure if you want to start in red and go to green or the opposite.

Either way, You are in need of PIVOT and UNPIVOT functions.

Let's create some test tables to mimic your data

create table #_base
(
    Dt_ref  datetime 
,   Val     float
)

insert into #_base
values
 ('2018-12-16 01:00:00.000', 36.96)
,('2018-12-16 02:00:00.000', 38.81)
,('2018-12-16 03:00:00.000', 38.1)
,('2018-12-16 04:00:00.000', 38.58)
,('2018-12-16 05:00:00.000', 38.23)
,('2018-12-16 06:00:00.000', 38.42)
,('2018-12-17 01:00:00.000', 96.96)
,('2018-12-17 02:00:00.000', 98.81)
,('2018-12-17 03:00:00.000', 98.1)
,('2018-12-17 04:00:00.000', 98.58)
,('2018-12-17 05:00:00.000', 98.23)
,('2018-12-17 06:00:00.000', 98.42)

select * from #_base

Let's create your Green dataset

select  cast(Dt_ref as date) as Day
    ,   datepart (hour,Dt_ref) as Hour
    ,   Val 
into #_base_green
from #_base

select * from #_base_green

If you want to go from Green to Red just PIVOT

select  Day
    ,   [1] as Hour1
    ,   [2] as Hour2
    ,   [3] as Hour3
    ,   [4] as Hour4
    ,   [5] as Hour5
    ,   [6] as Hour6
into #_result_red
from #_base_green p
pivot (
    sum(val)
    for Hour in 
    (   [1] 
    ,   [2] 
    ,   [3] 
    ,   [4] 
    ,   [5] 
    ,   [6] ) 
) as pvt

select * from #_result_red

From Red to Green, UNPIVOT

select * 
into #_base_red
from #_result_red

select * from #_base_red

select  
        dateadd(hh, cast(substring(hours,5,6) as int), cast(day as datetime))
    ,   value
into #_result_green
from
(
    select Day,      Hour1
                    ,Hour2
                    ,Hour3
                    ,Hour4
                    ,Hour5
                    ,Hour6
    from #_base_red
) p
unpivot (value for Hours in (Hour1
                    ,Hour2
                    ,Hour3
                    ,Hour4
                    ,Hour5
                    ,Hour6) 
) as unpvt

select * from #_result_green

Answer 2

you can use unpivot transform component

you need to double click on Unpivot to select columns which is going to be transformed to rows