How to join tables and show only two lines?

Question

Person Table

Car Table

Select

How to get the above selection that shows the result in just two lines?

So far I've used INNER JOIN and GROUP BY, but I didn't get what I need.

Answer 1

I think I have a solution for you. To achieve your answer you need to use Pivot and dynamic column. Here is my solution given below.

DECLARE @DRCol varchar(500);
DECLARE @DRColWITHMax varchar(500);
DECLARE @Prefix varchar(20)='Car.Color';

DECLARE @temp TABLE(p_id int,columnName VARCHAR(10),rn int);
--PERSON Table
CREATE TABLE #tmpPerson(id int,pname VARCHAR(10));
INSERT INTO #tmpPerson VALUES
(1,'John'),
(2,'Sarah')
--Car Table
create table  #tmp(id int,p_id int,color VARCHAR(10));
INSERT INTO #tmp VALUES
 (1,1,'blue'),
(2,1,'Red'),
(3,1,'black'),
(4,2,'white');

--Using This table for dynamic Column Generation
INSERT INTO @temp
SELECT p_id,@Prefix + CAST(ROW_NUMBER() OVER(PARTITION BY p_id ORDER BY color) AS VARCHAR(MAX)) AS columnName,
        ROW_NUMBER()over(partition by p_id order by color)rn
FROM #tmp AS m;

--Dynamic Column Generation For Pivot
select top 1 
@DRCol=stuff((select ','+'['+@Prefix+cast(rn as varchar)+']' 
from @temp c1 where c.p_id=c1.p_id for xml path('')),1,1,'')
from @temp c
where c.p_id=(select top 1 p_id from @temp c1 order by rn desc);

--Dynamic Column Generation For Group By
SELECT top 1 
@DRColWITHMax=stuff((select ','+'MAX(['+@Prefix+cast(rn as varchar)+']) as ['+@Prefix+cast(rn as varchar)+']' 
FROM @temp c1 where c.p_id=c1.p_id for xml path('')),1,1,'')
FROM @temp c
WHERE c.p_id=(SELECT top 1 p_id from @temp c1 order by rn desc);

DECLARE @Sql varchar(2000)='';

 set @Sql='   
 WITH CTE AS
 (
    SELECT p.*
    FROM
    (
        SELECT *,pName=(Select TOP 1 pname FROM #tmpPerson as tp Where tp.id=m.p_id)
               ,'''+@Prefix+'''+ cast(ROW_NUMBER() OVER(PARTITION BY p_id ORDER BY id) as varchar(max))  columnName
        FROM #tmp AS m
    ) AS t PIVOT(MAX(color) FOR columnName IN('+@DRCol+')) AS p
 )
 Select p_id,pName, '+@DRColWITHMax+'
 FROM CTE GROUP BY p_id,pName
';
exec(@sql);
drop table #tmp;
drop table #tmpPerson;

Output:

Note: I made this query SQL Server 2016 and took help from this Answer.

Answer 2

You can use conditional aggregation:

select p.id, p.name,
       max(case when seqnum = 1 then c.color end) as color_1,
       max(case when seqnum = 2 then c.color end) as color_2,
       max(case when seqnum = 3 then c.color end) as color_3
from (select c.*,
             row_number() over (partition by p_id order by id) as seqnum
      from car c
     ) c join
     person p
     on c.p_id = p.id
group by p.id, p.name;

Note that the above does not include the person id repeated through the row. That seems quite redundant.