SQL -Pivoting a column from a table which have Unique records

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

Answer

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

NOTE The 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 emp_id and name, and add a column group grouped by col

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.