SQLTeam.com | Weblogs | Forums

Sql pivot help

need help to get the sql pivot function to get output table sql pivot

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