CREATE TABLE [#cars](
	[id] [int] NOT NULL,
	[part1] [int] NULL,
	[part2] [int] NULL,
	[part3] [int] NULL
) ON [PRIMARY]

GO

INSERT INTO [#cars] (id, [part1], [part2],[part3])
SELECT 1,1,1,1 UNION ALL
SELECT 2,0,0,0 UNION ALL
SELECT 3,0,1,0 UNION ALL
SELECT 4,1,0,0 UNION ALL
SELECT 5,0,0,1

SELECT * FROM #cars

DROP TABLE #cars

SELECT id as carid, '1' as carparts FROM #cars WHERE part1=1
UNION
SELECT id as carid, '2' as carparts FROM #cars WHERE part2=1
UNION
SELECT id as carid, '3' as carparts FROM #cars WHERE part3=1

CREATE TABLE [#cars](
	[id] [int] NOT NULL,
	[part1] [int] NULL,
	[part2] [int] NULL,
	[part3] [int] NULL
) ON [PRIMARY]

INSERT [#cars] (id, [part1], [part2],[part3])
VALUES(1,1,1,1),(2,0,0,0),(3,0,1,0),(4,1,0,0),(5,0,0,1)

SELECT * FROM #cars

SELECT id, part1 as Part, 1 as Seq
FROM #cars
WHERE part1 <>0
UNION ALL
SELECT id, part2 as Part, 2 as Seq
FROM #cars
WHERE part2 <>0
UNION ALL
SELECT id, part3 as Part, 3 as Seq
FROM #cars
WHERE part3 <>0

DROP TABLE #cars

select id, a.PartId 
from #cars
cross apply (VALUES (1*[part1]), (2*[part2]), (3*[part3])) a(PartId)
where a.PartId > 0

select id,1*part1 from #cars where part1 > 0
union
select id,2*part2 from #cars where part2 > 0
union
select id,3*part3 from #cars where part3 > 0

CREATE TABLE #cars(
	[id] [int] NOT NULL primary key( id )
) ON [PRIMARY]

create table #parts(
	Id int not null
	primary key (id )
)

create table #mnPartsCars(
	CarId int not null,
	PartId int not null
	);

insert into #cars
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5

insert into #parts( Id ) values ( 1 ), ( 2 ), ( 3 );

insert into #mnPartsCars( CarId, PartId ) values
  ( 1, 1 ),
  ( 1, 2 ),
  ( 1, 3 ),
  ( 3, 2),
  ( 4, 1),
  ( 5, 3);

select 
  CarId = c.id,
  PartId = p.id
from
  #cars c inner join
  #mnPartsCars mn on
    mn.CarId = c.id inner join
  #parts p on
    p.Id = mn.PartId


drop table #cars
drop table #parts
drop table #mnPartsCars