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'),
('03','Cust3','Dog','Y'),
('04','Cust4','Fish','Y'),
('05','Cust5','Mouse','Y'),
('06','Cust6','Dog','Y')
6 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 ( SELECT (CASE WHEN [IsPet]=''Y'' THEN ''Yes'' END) AS [IsPet],
                    [CustID], [CustName],[PetType]
               FROM [Customer] ) AS c
     PIVOT 
     (
       MAX([IsPet]) FOR [PetType] IN (' + @cols + N')
     ) AS p'

EXEC sp_executesql @query;


CustID
CustName
Cat
Dog
Fish
Mouse


1
Cust1
null
Yes
null
null

2
Cust2
Yes
null
null
null

3
Cust3
null
Yes
null
null

4
Cust4
null
null
Yes
null

5
Cust5
null
null
null
Yes

6
Cust6
null
Yes
null
null

CustID	CustName	Cat	Dog	Fish	Mouse
1	Cust1	null	Yes	null	null
2	Cust2	Yes	null	null	null
3	Cust3	null	Yes	null	null
4	Cust4	null	null	Yes	null
5	Cust5	null	null	null	Yes
6	Cust6	null	Yes	null	null