Boolean filed on one column - PIVOT table

Question

I am having an issue trying to create a pivot table. I am joining some tables and need to pivot the car model column. For example my tables are:

Person

Id	FirstName	LastName
1	Mary	Ford
2	John	Murphy
3	Cathal	Gibsey

Cars

Id	Description
1	Toyota
2	Ford
3	BMW
4	Hyundaii
5	Volvo

Person-Car

Id	Car
1	1
1	2
1	5
2	3
3	4
3	5

My preferred output would be the description column pivoted as columns:

Id	FirstName	LastName	Toyota	Ford	BMW	Hyundaii	Volvo
1	Mary	Ford	TRUE	TRUE			TRUE
2	John	Murphy			TRUE
3	Cathal	Gibsey				TRUE	TRUE

My Query is:

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX);
DECLARE @Columncars AS NVARCHAR(MAX);

 
SELECT @Columncars= ISNULL(@Columncars + ',','') 
       + QUOTENAME([CDescription])
FROM (
select distinct Description [Columncars]
from Cars
) As Carsssss

 
select  @Columncars

IF OBJECT_ID('tempdb.dbo.##CandidateCarsTable ', 'U') IS NOT NULL
DROP TABLE  ##CandidateCarsTable ; 
SET @DynamicPivotQuery = 
  N'Select distinct * into ##CandidateCarsTable 
    FROM (
     select  p.firstname, p.lastname, count(c.Id) as''carCount'',
    case when c.Description is null then ''N/A'' + N'':car '' else c.Description + N'':car ''  end as personCar
    from person p
    inner join Person-Car pc on pc.Id = p.Id
    Inner join cars c on c.Id = pc.Car
    PIVOT(MAX(carCount)
          FOR [personCar] IN (' + @ColumnCars + ')) AS PVTTable1'

    
      EXEC sp_executesql @DynamicPivotQuery
      select * from  ##CandidateCarsTable

I cannot seem to get it correct. Any help would be appreciated. Thanks

EDIT, My tables look perfectly find on preview and then change once saved.

Preferred output table screenshot

@serg Ive changed it a bit now and everything comes out as NULL - updated the query — user19902020, Jun 23 '21 at 20:01
It will not run at all @Columnlanguages is not declared. Also tag the question with your DBMS. Looks like MS Sql Server code — Serg, Jun 23 '21 at 20:05
@Serg I had the right column just not in this edit, I have now changed it. Changed to sql server manager — user19902020, Jun 23 '21 at 20:13
Does this answer your question? [SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Charlieface, Jun 24 '21 at 08:41

Serg · Answer 1 · 2021-06-23T20:38:16.103

This is a static sql. I quess it produces the output you want for example data. The query enumerates all possible person/car pairs and checks if the pair really exists. Change your dynamic sql building code accordingly

Select distinct * 
FROM (
   select firstname, lastname, [BMW],[Ford],[Hyundaii],[Toyota],[Volvo]
   from (
      select p.firstname, p.lastname, c.description, case when pc.car is not Null Then N'TRUE' end flag
      from person p 
      cross join cars c
      left join PersonCar pc on pc.Id = p.Id and c.Id = pc.Car 
   ) t
   PIVOT(MAX(flag) FOR [description] IN ([BMW],[Ford],[Hyundaii],[Toyota],[Volvo])) AS PVTTable1
   ) t;

Looks like the simpler version will do as unique person.id is included in your last edit.

   select id, firstname, lastname, [BMW],[Ford],[Hyundaii],[Toyota],[Volvo]
   from (
      select p.id, p.firstname, p.lastname, c.description, case when pc.car is not Null Then N'TRUE' end flag
      from person p 
      left join PersonCar pc on pc.Id = p.Id 
      left join cars c on c.Id = pc.Car 
   ) t
   PIVOT(MAX(flag) FOR [description] IN ([BMW],[Ford],[Hyundaii],[Toyota],[Volvo])) AS PVTTable1

db<>fiddle

Boolean filed on one column - PIVOT table

1 Answers1