Select data from 2 tables, UNPIVOT first table and join it with other table

Question

I have 2 tables in SQL, namely Pets and Customers

Pets table:

Pet ID	PetType
01	Dog
02	Cat
03	Mouse
04	Fish

Customer table:

CustID	CustName	PetType	IsPet
01	Cust1	Dog	Y
02	Cust2	Cat	Y
03	Cust3	Dog	Y
04	Cust4	Fish	Y
05	Cust5	Mouse	Y
06	Cust6	Dog	Y

I need to select the records in such a way that the list of Customer are joined with pivoted columns for the pets. For example, the result should be:

CustID	CustName	Dog	Cat	Mouse	Fish
01	Cust1	Yes
02	Cust2		Yes
03	Cust3	Yes
04	Cust4				Yes
05	Cust5			Yes
06	Cust6	Yes

Any advice or help for the SQL query would be greatly appreciated.

If you don't know the columns in the final table (i.e. they come from the first table), then you will need dynamic SQL. — Gordon Linoff, Jun 04 '21 at 11:35
This is a pivot, not an unpivot. A Pivot transforms rows into columns, using aggregation, where as an unpivot transforms columns into rows (and doesn't require aggregation). — Thom A, Jun 04 '21 at 11:45

Barbaros Özhan · Answer 1 · 2021-06-04T12:13:51.550

You can use such a query in order to create all animals list inserted into the table dynamically :

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

SET @cols = ( SELECT STRING_AGG(QUOTENAME([PetType]),',') 
                FROM (SELECT DISTINCT [PetType] 
                        FROM [Customer] ) C );
SELECT @cols;
SET @query = 
  N'SELECT *  
      FROM [Customer]
     PIVOT 
     (
         MAX(IsPet) FOR [PetType] IN (' + @cols + N')
     ) AS p'

EXEC sp_executesql @query;

Demo

If bringing the literal Y as Yes matters, then use a subquery along with a conditional such as

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;

Demo

In any case, [Pets] table is not needed.

Select data from 2 tables, UNPIVOT first table and join it with other table

1 Answers1