need help to get the sql pivot function to get output table 
please provide sample data not a picture but real usable data as
create table #sample(id int, date char(2), time char(2), ch10 char(2) ) ---etc
insert into #sample
select 1, 'd1', 't1', 'a1', 'b1' union
select 2, 'd1', 't2', 'a1', 'b1'
etc
hi
i decided to help him out
please post in t-sql forum ... this is t-sql related
please click arrow to the left for drop create data script
drop table #data
go
create table #data
(
id int ,
date varchar(10),
time varchar(10),
ch10 varchar(10),
ch11 varchar(10),
ch12 varchar(10),
ch13 varchar(10),
ch14 varchar(10),
ch15 varchar(10)
)
go
insert into #data select 1 ,'d1','t1','a1' ,'b1' ,'c1' ,'d1' ,'e1' ,'f1'
insert into #data select 2 ,'d1','t2','a2' ,'b2' ,'c2' ,'d2' ,'e2' ,'f2'
insert into #data select 3 ,'d1','t3','a3' ,'b3' ,'c3' ,'d3' ,'e3' ,'f3'
insert into #data select 4 ,'d1','t4','a4' ,'b4' ,'c4' ,'d4' ,'e4' ,'f4'
insert into #data select 5 ,'d1','t5','a5' ,'b5' ,'c5' ,'d5' ,'e5' ,'f5'
insert into #data select 6 ,'d2','t1','a6' ,'b6' ,'c6' ,'d6' ,'e6' ,'f6'
insert into #data select 7 ,'d2','t2','a7' ,'b7' ,'c7' ,'d7' ,'e7' ,'f7'
insert into #data select 8 ,'d2','t3','a8' ,'b8' ,'c8' ,'d8' ,'e8' ,'f8'
insert into #data select 9 ,'d2','t4','a9' ,'b9' ,'c9' ,'d9' ,'e9' ,'f9'
insert into #data select 10 ,'d2','t5','a10','b10','c10','d10','e10','f10'
go
select * from #data
hi
i have your solution ... its not a case of PIVOT ...
please click arrow to the left for SQL 1
select
a.time as ch10
, a.ch10 as d1
, b.ch10 as d2
from
( select * from #data where date = 'd1' ) a
join
( select * from #data where date = 'd2' ) b
on
a.time = b.time
go
please click arrow to the left for SQL 2
select
a.time as ch11
, a.ch11 as d1
, b.ch11 as d2
from
( select * from #data where date = 'd1' ) a
join
( select * from #data where date = 'd2' ) b
on
a.time = b.time
go
Thx buddy, for the help,
will run the code soon and post the result
regards
john
Tried the code dosent work for me.
putting the code from my test database.
any help on this pls.
create table
create table testdb (
id SERIAL PRIMARY KEY,
date DATE NOT NULL,
time VARCHAR(50) NOT NULL,
ch10 VARCHAR(50),
ch11 VARCHAR(50),
ch12 VARCHAR(50),
ch13 VARCHAR(50),
ch14 VARCHAR(50),
ch15 VARCHAR(50)
);
insert data
insert into testdb (date, time, ch10, ch11, ch12, ch13, ch14, ch15) values ('12/1/2019', '15.50', 'Alisun', 'Bernadene', 'Jonah', 'Justinian', 'Halli', 'Bebe');
insert into testdb (date, time, ch10, ch11, ch12, ch13, ch14, ch15) values ('12/1/2019', '16.10', 'Randi', 'Silvan', 'Lia', 'Nissie', 'Theresina', 'Yvon');
insert into testdb (date, time, ch10, ch11, ch12, ch13, ch14, ch15) values ('12/1/2019', '16.30', 'Bendick', 'Reece', 'Vic', 'Edwin', 'Jan', 'Kelly');
insert into testdb (date, time, ch10, ch11, ch12, ch13, ch14, ch15) values ('12/1/2019', '16.50', 'Catie', 'Christie', 'Marion', 'Sibel', 'Sammy', 'Artur');
insert into testdb (date, time, ch10, ch11, ch12, ch13, ch14, ch15) values ('12/1/2019', '17.10', 'Vivyanne', 'Godfree', 'Nial', 'Ethe', 'Bailey', 'Dal');
insert into testdb (date, time, ch10, ch11, ch12, ch13, ch14, ch15) values ('12/1/2019', '17.30', 'Beryl', 'Cullan', 'Genni', 'Wynny', 'Gwyn', 'Karim');
insert into testdb (date, time, ch10, ch11, ch12, ch13, ch14, ch15) values ('12/1/2019', '17.50', 'Nichole', 'Karil', 'Mahmoud', 'Anni', 'Kissiah', 'Tessie');
insert into testdb (date, time, ch10, ch11, ch12, ch13, ch14, ch15) values ('12/1/2019', '18.10', 'Sax', 'Benny', 'Em', 'Ringo', 'Myrtie', 'Alberik');
insert into testdb (date, time, ch10, ch11, ch12, ch13, ch14, ch15) values ('12/1/2019', '18.30', 'Hersch', 'Jo', 'Marietta', 'Saloma', 'Eddie', 'Jami');
insert into testdb (date, time, ch10, ch11, ch12, ch13, ch14, ch15) values ('12/1/2019', '18.50', 'Jerome', 'Thain', 'Shawnee', 'Edyth', 'Peadar', 'Hewe');
insert into testdb (date, time, ch10, ch11, ch12, ch13, ch14, ch15) values ('12/1/2019', '19.10', 'Noemi', 'Sig', 'Cherri', 'Jimmy', 'Caro', 'Stavro');
insert into testdb (date, time, ch10, ch11, ch12, ch13, ch14, ch15) values ('12/1/2019', '19.30', 'Clayton', 'Miner', 'Ketti', 'Aaron', 'Olvan', 'Gweneth');
insert into testdb (date, time, ch10, ch11, ch12, ch13, ch14, ch15) values ('12/1/2019', '19.50', 'Georg', 'Beatrisa', 'Maury', 'Adaline', 'Thacher', 'Maddi');
insert into testdb (date, time, ch10, ch11, ch12, ch13, ch14, ch15) values ('12/1/2019', '20.10', 'Bondie', 'Ario', 'Hedwig', 'Galvan', 'Normie', 'Jesselyn');
insert into testdb (date, time, ch10, ch11, ch12, ch13, ch14, ch15) values ('12/1/2019', '20.30', 'Poul', 'Leshia', 'Larisa', 'Cyndie', 'Osmund', 'Lindsay');
insert into testdb (date, time, ch10, ch11, ch12, ch13, ch14, ch15) values ('12/2/2019', '15.50', 'Betteann', 'Vida', 'Betti', 'Donalt', 'Ossie', 'Myrah');
insert into testdb (date, time, ch10, ch11, ch12, ch13, ch14, ch15) values ('12/2/2019', '16.10', 'Nata', 'Darill', 'Luigi', 'Sheppard', 'Cliff', 'Sandor');
insert into testdb (date, time, ch10, ch11, ch12, ch13, ch14, ch15) values ('12/2/2019', '16.30', 'Dulcia', 'Kizzie', 'Heidie', 'Had', 'Ivor', 'Romeo');
insert into testdb (date, time, ch10, ch11, ch12, ch13, ch14, ch15) values ('12/2/2019', '16.50', 'Clayton', 'Miner', 'Ketti', 'Adaline', 'Thacher', 'Maddi');
insert into testdb (date, time, ch10, ch11, ch12, ch13, ch14, ch15) values ('12/2/2019', '17.10', 'Pippa', 'Sidoney', 'Peterus', 'Jasmina', 'Darice', 'Joyous');
insert into testdb (date, time, ch10, ch11, ch12, ch13, ch14, ch15) values ('12/2/2019', '17.30', 'Linet', 'Lorrayne', 'Isabelle', 'Luca', 'Selie', 'Derrick');
insert into testdb (date, time, ch10, ch11, ch12, ch13, ch14, ch15) values ('12/2/2019', '17.50', 'Linc', 'Whittaker', 'Dell', 'Koressa', 'Ketty', 'Lonna');
insert into testdb (date, time, ch10, ch11, ch12, ch13, ch14, ch15) values ('12/2/2019', '18.10', 'Cherish', 'Agace', 'Domingo', 'Janot', 'Alyse', 'Tremayne');
insert into testdb (date, time, ch10, ch11, ch12, ch13, ch14, ch15) values ('12/2/2019', '18.30', 'Anatole', 'Yettie', 'Natalee', 'Faina', 'Paloma', 'Ozzy');
insert into testdb (date, time, ch10, ch11, ch12, ch13, ch14, ch15) values ('12/2/2019', '18.50', 'Ethelin', 'Fern', 'Guillemette', 'Orlando', 'Angelika', 'Herby');
insert into testdb (date, time, ch10, ch11, ch12, ch13, ch14, ch15) values ('12/2/2019', '19.10', 'Hedi', 'Gunner', 'Moses', 'Loretta', 'Burr', 'Jamil');
insert into testdb (date, time, ch10, ch11, ch12, ch13, ch14, ch15) values ('12/2/2019', '19.30', 'Fina', 'Rozella', 'Tilda', 'Adan', 'Demetra', 'Sena');
insert into testdb (date, time, ch10, ch11, ch12, ch13, ch14, ch15) values ('12/2/2019', '19.50', 'Cross', 'Isidoro', 'Bron', 'Brian', 'Darice', 'Horacio');
insert into testdb (date, time, ch10, ch11, ch12, ch13, ch14, ch15) values ('12/2/2019', '20.10', 'Lorena', 'Karita', 'Noe', 'Meaghan', 'Blayne', 'Randa');
insert into testdb (date, time, ch10, ch11, ch12, ch13, ch14, ch15) values ('12/2/2019', '20.30', 'Warren', 'Corly', 'Cthrine', 'Arlana', 'Larine', 'Brendon');
output requirement where ch10 is with distinct date in the columns
REQUIREMENT =>
id | time | ch10 | ch10 |
|2019-12-01|2019-12-02 |
----+--------+----------+-----------+
1 | 15.50 | Alisun |Betteann |
2 | 16.10 | Randi |Nata |
3 | 16.30 | Bendick |Dulcia |
4 | 16.50 | Catie |Clayton |
5 | 17.10 | Vivyanne |Pippa |
6 | 17.30 | Beryl |Linet |
7 | 17.50 | Nichole |Linc |
8 | 18.10 | Sax |Cherish |
9 | 18.30 | Hersch |Anatole |
10 | 18.50 | Jerome |Ethelin |
11 | 19.10 | Noemi |Hedi |
12 | 19.30 | Clayton |Fina |
13 | 19.50 | Georg |Cross |
14 | 20.10 | Bondie |Lorena |
15 | 20.30 | Poul |Warren |
Hi
Bed time for me ....
I will have a look in the morning
Please excuse me.. till then
Sorry
hi buddy,
just did a rerun of your code and it worked perfectly.
had to remove the go option on the code.
thx for the help much appreciated
regards