Each ID can have many repeated rows as showing the first table to the left, the requirement is to place 'child' records of the same ID on the same row and repeat the column headers as showing below in the table on the right.

I am trying to do this in SQL Server, here is my attempt at it:

if Object_id('tempdb..#temp1') is not null
Begin
 drop table #temp1
End
create table #temp1 (
    ID integer, FirstName varchar(50), LastName varchar(50)
)
insert into #temp1 values (25,'Abby','Mathews');
insert into #temp1 values (25,'Jennifer','Edwards');
insert into #temp1 values (26,'Peter','Williams');
insert into #temp1 values (27,'John','Jacobs');
insert into #temp1 values (27,'Mark','Scott');

Select * From #temp1;

With Qrt_CTE (ID, FirstName, LastName)
AS   
(
  SELECT ID, FirstName, LastName 
  FROM #temp1 AS BaseQry
)
SELECT ID, ColumnName, ColumnValue INTO #temp2
FROM Qrt_CTE
UNPIVOT
(
    ColumnValue FOR ColumnName IN (FirstName, LastName)
) AS UnPivotExample

Select * From #temp2

How do I get these results done please?

Thank you so much in advance, appreciate any help.

CodePudding user response：

You can pivot a number of columns with a conditional aggregation

select 
   id
   ,max(case rn when 1 then FirstName end) FirstName1
   ,max(case rn when 1 then LastName end) LastName1
   ,max(case rn when 2 then FirstName end) FirstName2
   ,max(case rn when 2 then LastName end) LastName2
  -- ..
from (
  select *, row_number() over(partition by id order by FirstName ) rn
  from #temp1) t
group by id
;

CodePudding user response：

If what you actually want is a list of all users with an id -- (a common use case) then you should do that this way

SELECT ID, STRING_AGG(FirstName   ' '   LastName, ', ') as Names
FROM #temp1
GROUP BY ID

CodePudding user response：

I'm not giving you the full source-code, this is a large-ish work, but I will provide the steps that you will need to get through

Find out the number of columns

You will need a variable, I will call it cnt which will hold the result of select max(count(1)) from your table, grouped by ID.

Before you loop until cnt with an index

• Initialize selectClause as ID, t1.FirstName as FirstName, t1.LastName as LastName
• Initialize fromClause as from yourtable t1
• Initialize your index to 2 (we already handled the first t1)

For each steps:

Append the selection:

Append , t<i>.FirstName as FirstName, t<i>.LastName as LastName to selectClause, where <i> is your current index value (replace it with the actual index name). You can use CONCAT to concatenate variables and text chunks and you can convert integers into numbers in several ways, see this for an example: