Adding Child Records to New Columns Instead of New Rows

Question

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.

This is an action that is better to do in the report presentation software -- not in SQL. To do it in SQL you would have to use dynamic SQL unless you know the maximum number of repeats in the ID column. — Hogan, Jan 19 '23 at 18:06
Thanks for the answer, I am using SSRS, is it possible to have done on the reporting side? If yes, I need a simple example that can help me, would you be able to share an example, please? — Franko, Jan 19 '23 at 18:09
Yes -- SSRS is a reporting tool -- it is possible to do in SSRS — Hogan, Jan 19 '23 at 18:11
The SQL language **does not work this way**, as this would be in direct opposition to the relational set theory underpinning SQL databases. — Joel Coehoorn, Jan 19 '23 at 18:12
@JoelCoehoorn -- I'd go so far to say use cases don't work this way -- I can't think of an example where you want a report with an unbounded number of columns. — Hogan, Jan 19 '23 at 18:15
Hogan, can you give me a simple example or a pointer on how to do it in SSRS please? — Franko, Jan 19 '23 at 18:18
[stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Stu, Jan 19 '23 at 18:21
How to do dynamic columns in SSRS was answered here: https://stackoverflow.com/questions/36130747/how-to-create-dynamic-columns-in-ssrs-report — Hogan, Jan 19 '23 at 18:27
As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. — Dale K, Jan 19 '23 at 18:56
@Hogan Thank you so much for the help, I really appreciate it, was able to create a matrix report that would group on each column name to produce the needed report, however the second row starts at the column after the first row's columns end, is there a better way to make all rows start at the first column please? — Franko, Jan 19 '23 at 19:07
I've no idea @Franko -- I've never written an SSRS report -- did you try google? or the product documentation? — Hogan, Jan 19 '23 at 19:10
The order of the new column pairs is by `FirstName` from left to right according to your sample data. Is that a requirement? Is there any order requirement? — HABO, Jan 19 '23 at 20:06

score 1 · Answer 1 · answered Jan 19 '23 at 18:10

1

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
;

answered Jan 19 '23 at 18:10

Serg

22,285
5
21
48

This only works if at max there are two names -- he says there can be any number. – Hogan Jan 19 '23 at 18:11
@Hogan, in any case, this only works if the number of rows with the same ID is less than half the limit on the number of columns. – Serg Jan 19 '23 at 18:18
Ok, but the OP still said he wants it to be unlimited -- you got to make it dynamic to solve the problem. – Hogan Jan 19 '23 at 18:20
You do know that 2016 was 7 years ago? You might want get a version that is less than 5 years old. – Hogan Jan 19 '23 at 18:29

score 1 · Answer 2 · answered Jan 19 '23 at 18:19

1

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

answered Jan 19 '23 at 18:19

Hogan

69,564
10
76
117

'STRING_AGG' is not a recognized built-in function name. – Franko Jan 19 '23 at 18:21
What version of SQL server are you using -- it is documented right here https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver16 – Hogan Jan 19 '23 at 18:23
Maybe you are using DB2 or Oracle? Then use LIST_AGG – Hogan Jan 19 '23 at 18:26
2016. however, if it can be done on SSRS, could you provide an simple example please? – Franko Jan 19 '23 at 18:26
@Franko -- see comment above -- this is a basic SSRS feature called Matrix reports – Hogan Jan 19 '23 at 18:28

score 0 · Answer 3 · answered Jan 19 '23 at 19:27

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.FirstName as FirstName, t.LastName as LastName to selectClause, where  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: https://www.w3schools.com/sql/func_sqlserver_convert.asp

Append the table

Append left join yourtable t on t<i-1>.ID = t.ID and CONCAT(t<i-1>.FirstName, t<i-1>.LastName) < CONCAT(t.FirstName, t.LastName) to fromClause

Increment the index

Don't forget to do it :)

Once the loop completed

Concatenate selectClause and fromClause and don't forget to append group by ID to your concatenation. Now your query is ready.

Run the dynamic query

You can do that with EXEC or sp_executesql.

Dangers

beware SQL injection, your text data could be dangerous, see https://www.w3schools.com/sql/sql_injection.asp
this is arduous and labor intensive

Why is there no SQL code? Why are linking to w3schools -- one of the worse sites on the internet? — Hogan, Jan 19 '23 at 19:36
@Hogan I think that based on my answer anyone can figure out a solution. And it's better to avoid writing the code for being too long than writing code which has little to do with the problem, such as your code, which does not have the multiple `FirstName`/`LastName` columns the op asked for. — Lajos Arpad, Jan 19 '23 at 20:29

score 0 · Answer 4 · answered Jan 19 '23 at 19:38

Thank you all for responding to my question. I was able to resolve it by creating an SSRS matrix report and grouping on the rows (ID) and the grouping on the columns (all other columns needed to be part of the group). Thanks to @Hogan for suggesting the SSRS reporting idea.