Need help on pivot

Question

Hi,
Please find the details below.
CREATE TABLE [dbo].[temp1](
[Prid] varchar NOT NULL,
[Sponsor] varchar NULL,
[ProjectManager] varchar NULL,
[Country_Name] varchar NOT NULL,
[cntid] [int] NOT NULL,
[RolloutStartDate] [date] NULL,
[RolloutEndDate] [date] NULL,
[CountryImpact] [bit] NULL,
[StartDateatRisk] [bit] NULL,
[EndDateatRisk] [bit] NULL
) ON [PRIMARY]
GO

insert into temp1 values('KT1','KT','Siva Hanuma','France',2,'2020-09-08','2022-03-04',0,0,0)
go
insert into temp1 values('KT1','KT','Siva Hanuma','US',1,'2020-09-08','2022-03-04',0,0,0)
go
insert into temp1 values('KT1','KT','Siva Hanuma','Germany',3,'2020-09-08','2022-03-04',0,0,0)

My required output is

PRID Sponsor FranceRolloutStartDate FranceRolloutEndDate FranceCountryImpact FranceStartDateatRisk USRolloutStartDate USRolloutEndDate USCountryImpact USStartDateatRisk GermanyRolloutStartDate GermanyRolloutEndDate GermanyCountryImpact GermanyStartDateatRisk
KT1 Siva Hanuma 2 2020-09-08 2022-03-04 0 0 0 2020-08-04 2021-03-04 0 0 0 2020-08-31 2023-03-04 0 0 0

Waiting for valuable replies

Answer 1

Hi @Murari Ram ,

The column and data in your expected output do not correspond, so all the data is returned in my code, you can replace the * in the select statement with the column you need to return.
Please refer to：

CREATE TABLE [dbo].[temp1](  
[Prid] [varchar](20) NOT NULL,  
[Sponsor] [varchar](80) NULL,  
[ProjectManager] [varchar](100) NULL,  
[Country_Name] [varchar](200) NOT NULL,  
[cntid] [int] NOT NULL,  
[RolloutStartDate] [date] NULL,  
[RolloutEndDate] [date] NULL,  
[CountryImpact] [bit] NULL,  
[StartDateatRisk] [bit] NULL,  
[EndDateatRisk] [bit] NULL  
) ON [PRIMARY]  
GO  
  
  
insert into temp1 values('KT1','KT','Siva Hanuma','France',2,'2020-09-08','2022-03-04',0,0,0)  
go  
insert into temp1 values('KT1','KT','Siva Hanuma','US',1,'2020-09-08','2022-03-04',0,0,0)  
go  
insert into temp1 values('KT1','KT','Siva Hanuma','Germany',3,'2020-09-08','2022-03-04',0,0,0)  
  
  
with cte1  
as(select PRID, Sponsor, ProjectManager,cntid Francecntid ,RolloutStartDate FranceRolloutStartDate, RolloutEndDate FranceRolloutEndDate,  
          CountryImpact FranceCountryImpact,StartDateatRisk FranceStartDateatRisk,EndDateatRisk FranceEndDateatRisk   
   from temp1 where Country_Name='France')  
,cte2 as(select PRID,cntid UScntid ,RolloutStartDate USRolloutStartDate, RolloutEndDate USRolloutEndDate,  
          CountryImpact USCountryImpact,StartDateatRisk USStartDateatRisk,EndDateatRisk USEndDateatRisk  from temp1 where Country_Name='US')   
,cte3 as(select PRID,cntid Germanycntid ,RolloutStartDate GermanyRolloutStartDate, RolloutEndDate GermanyRolloutEndDate,  
          CountryImpact GermanyCountryImpact,StartDateatRisk GermanyStartDateatRisk,EndDateatRisk GermanyEndDateatRisk  from temp1 where Country_Name='Germany')   
  
select * from cte1 c1  
join cte2 c2  
on c1.Prid=c2.Prid  
join cte3 c3  
on c2.Prid=c3.Prid  
  
drop table temp1   

Best Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

CREATE TABLE [dbo].[temp1](
[Prid] [varchar](20) NOT NULL,
[Sponsor] [varchar](80) NULL,
[ProjectManager] [varchar](100) NULL,
[Country_Name] [varchar](200) NOT NULL,
[cntid] [int] NOT NULL,
[RolloutStartDate] [date] NULL,
[RolloutEndDate] [date] NULL,
[CountryImpact] [bit] NULL,
[StartDateatRisk] [bit] NULL,
[EndDateatRisk] [bit] NULL
) ON [PRIMARY]
GO


insert into temp1 values('KT1','KT','Siva Hanuma','France',2,'2020-09-08','2022-03-04',0,0,0)
go
insert into temp1 values('KT1','KT','Siva Hanuma','US',1,'2020-09-08','2022-03-04',0,0,0)
go
insert into temp1 values('KT1','KT','Siva Hanuma','Germany',3,'2020-09-08','2022-03-04',0,0,0)

select Prid,Sponsor, [ProjectManager]
--,max(case when Country_Name='France' then cntid else null end) Francecntid
,max(case when Country_Name='France' then RolloutStartDate else null end) FranceRolloutStartDate
,max(case when Country_Name='France' then RolloutEndDate else null end) FranceRolloutEndDate
,max(case when Country_Name='France' then Cast(CountryImpact as int) else null end) FranceCountryImpact
,max(case when Country_Name='France' then Cast(StartDateatRisk as int) else null end) FranceStartDateatRisk
,max(case when Country_Name='France' then Cast(EndDateatRisk as int)  else null end) FranceEndDateatRisk 


 ,max(case when Country_Name='US' then RolloutStartDate else null end) USRolloutStartDate
,max(case when Country_Name='US' then RolloutEndDate else null end) USRolloutEndDate
,max(case when Country_Name='US' then Cast(CountryImpact as int) else null end) USCountryImpact
,max(case when Country_Name='US' then Cast(StartDateatRisk as int) else null end) USStartDateatRisk
,max(case when Country_Name='US' then Cast(EndDateatRisk as int)  else null end) USEndDateatRisk 



 ,max(case when Country_Name='Germany' then RolloutStartDate else null end) GermanyRolloutStartDate
,max(case when Country_Name='Germany' then RolloutEndDate else null end) FGermanyRolloutEndDate
,max(case when Country_Name='Germany' then Cast(CountryImpact as int) else null end) GermanyCountryImpact
,max(case when Country_Name='Germany' then Cast(StartDateatRisk as int) else null end) GermanyStartDateatRisk
,max(case when Country_Name='Germany' then Cast(EndDateatRisk as int)  else null end) GermanyEndDateatRisk 

  from temp1
  Group by Prid,Sponsor, [ProjectManager] 

  drop table if exists   temp1