SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE [Customer](CustID INT, CustName VARCHAR(20), PetType VARCHAR(20), IsPet VARCHAR(1))
INSERT [Customer] VALUES 
('01','Cust1','Dog','Y'),
('02','Cust2','Cat','Y')
2 rows affected

DECLARE @cols  AS NVARCHAR(MAX),  @query AS NVARCHAR(MAX)

SET @cols = ( SELECT STRING_AGG(QUOTENAME([PetType]),',') 
                FROM (SELECT DISTINCT [PetType] 
                        FROM [Customer] ) C );

SET @query = 
  N'SELECT *  
      FROM [Customer]
     PIVOT 
     (
         MAX(IsPet) FOR [PetType] IN (' + @cols + N')
     ) AS piv'

EXEC sp_executesql @query;


CustID
CustName
Cat
Dog


1
Cust1
null
Y

2
Cust2
Y
null