How to join tables and show only two lines?-CodePudding

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.

CodePudding user response：

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.

CodePudding user response：

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.