Check my article for Pivot details with example query
I have made sample for you.
Create Table EmpDetails
(
Emp_Id int,
Emp_Name varchar(40),
Allowance_Names varchar(40),
Allowance_Values varchar(40),
)
Insert into EmpDetails Values(1,'Emp1', 'AllowNames1', 'Values1')
Insert into EmpDetails Values(1,'Emp1', 'AllowNames2', 'Values2')
Insert into EmpDetails Values(1,'Emp1', 'AllowNames3', 'Values3')
Insert into EmpDetails Values(1,'Emp1', 'AllowNames4', 'Values4')
Insert into EmpDetails Values(2,'Emp2', 'AllowNames1', 'Values2_1')
Insert into EmpDetails Values(2,'Emp2', 'AllowNames2', 'Values2_2')
Insert into EmpDetails Values(2,'Emp2', 'AllowNames3', 'Values2_3')
Insert into EmpDetails Values(2,'Emp2', 'AllowNames4', 'Values2_4')
DECLARE @MyColumns AS NVARCHAR(MAX),
@SQLquery AS NVARCHAR(MAX)
select @MyColumns = STUFF((SELECT ',' + QUOTENAME(Allowance_Names)
FROM EmpDetails
GROUP BY Allowance_Names
ORDER BY Allowance_Names
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @SQLquery = N'SELECT Emp_Id,Emp_Name,' + @MyColumns + N' from
(
SELECT
Emp_Id,
Emp_Name,
Allowance_Values as values2
,Allowance_Names
FROM EmpDetails
) x
pivot
(
max(values2)
for Allowance_Names in (' + @MyColumns + N')
) p '
exec sp_executesql @SQLquery;
for more details check my articles.
Basic SQL Queries for Beginners[
^]