Complex Pivot - Based on Row value

Transact-SQL
,
1

Hi All,

I got a transnational table which has data for each and every minute. Have to Grouping this table and do a Pivot based on a Row value. Let me list the Example and Output below. Key value is "CH5".

TableA: (Input)
[Time] [Channel]
19.30 CH1
19.31 CH2
19.32 CH5
19.32 CH4
19.33 CH1
19.34 CH5
19.35 CH5
19.36 CH5
19.37 CH3
19.38 CH2
19.43 CH5
19.45 CH6

Output: (Pivot table based on CH5 - NEXT to CH5 must be in the rows with Order. Here Source Channel will be always CH5.

OUTPUT:
[Time] [SourceChannel] [DestCH1] [DestCH2] [DestCH3] [DestCH4]
19.32 CH5 CH4 CH1 NULL NULL
19.34 CH5 NULL NULL NULL NULL
19.35 CH5 NULL NULL NULL NULL
19.36 CH5 CH3 CH2 NULL NULL
19.43 CH5 CH6 NULL NULL NULL

Above output has based on CH5. We dont need to consider previous rows of this Channel. Following Channel will be pivoted till the source channel repeats.

2

Hi

Based on my understanding
this is what i have

Is this what you are looking for
Please let me know
Thanks

Create Data

drop table harish_tableA
go
create table harish_tableA
(
time varchar(100) null,
channel varchar(100) null
)
go

insert into harish_tableA select '19.30','CH1'
insert into harish_tableA select '19.31','CH2'
insert into harish_tableA select '19.32','CH5'
insert into harish_tableA select '19.32','CH4'
insert into harish_tableA select '19.33','CH1'
insert into harish_tableA select '19.34','CH5'
insert into harish_tableA select '19.35','CH5'
insert into harish_tableA select '19.36','CH5'
insert into harish_tableA select '19.37','CH3'
insert into harish_tableA select '19.38','CH2'
insert into harish_tableA select '19.43','CH5'
insert into harish_tableA select '19.45','CH6'
go

select * from harish_tableA
go

My Query

select time,ch1,ch2,ch3,ch4,ch5
from
(
select time,channel,'ch'+cast(ROW_NUMBER() OVER(partition by time order by time) as varchar(10)) as ColSeq
from harish_tableA
) Temp
PIVOT
(
MAX(channel)
FOR ColSeq in (ch1,ch2,ch3,ch4,ch5)
) PIV

3

Hi harish.. Thanks for your reply..

Shared query not meets up with the Output right.. Your Query will just results with MAX of time period to get the channel result. But my Output refers to a PArticular row value (CH5) - And its upcoming Channels to be pivoted in column values.