I am trying to pivot a table to Row which have duplicate records on all columns but, a unique ID Named ([log_ID]) at the last columns . I wont log_ID field pivoted and given dynamic name as log_ID_1 ,log_ID_2 …etc FYI – THE log_ID’s is not static they change at every execution’s they could be less or more .
My table retrieve records as this . enter image description here
I wont it be displayed as this enter image description here
You have not stated what database you are using so I’m assuming it’s a fairly recent version of SQL Server..
Add a column to your dataset that generates a number for each
log_id like this…
I’ve recreated your sample data with
DECLARE @t TABLE(Emp_ID int, [Name] varchar(20), log_ID int) INSERT INTO @t VALUES (6354, 'John Doe', 752), (6354, 'John Doe', 653), (6354, 'John Doe', 635), (6354, 'John Doe', 124), (6354, 'John Doe', 874), (6354, 'John Doe', 254) SELECT [Emp_ID], [Name], [log_ID], CONCAT('log_ID_', ROW_NUMBER() OVER(PARTITION BY Emp_ID ORDER BY Emp_ID) ) as col FROM @t
This gives the following results
ROW_NUMBER() function needs an
ORDER BY. I use
Emp_ID even though they are all the same so that the results should be returned in the order they sit in the table. You can change the order by clause to order the log_ID’s in whatever order you like.
Now in your report, add a matrix control, add a row group by
name, and add a column group grouped by
If the table has more than one emp_id then these will appear on separate rows in the report.
That’s all there is to it.