dynamic pivot, Sql Server

dynamic pivot

--Sql Server 2014 Express Edition
--Batches are separated by 'go'
create table Employee(Id int primary key,Name varchar(255));

create table EquipMent(Id int primary key ,EquipmentName varchar(255));

create table EquipmentMapping(Id int primary key,EmployeeId varchar(255),EquipmentId int);

INSERT INTO
Employee
(Id,Name)
values
(1,'Jhon'),
(2,'Max');

insert into
EquipMent
(Id,EquipmentName)
values
(1,'Hammer'),
(2,'Axe'),
(3,'Screw Driver');

insert into
EquipmentMapping
(Id,EmployeeId,EquipmentId)
values
(1,1,1),
(2,1,2),
(3,2,1),
(4,2,3);

select EM.Id,EM.EmployeeId,EM.EquipmentId 
from EquipmentMapping EM

----------------

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX),
        @PivotColumnNames AS NVARCHAR(MAX),
        @PivotSelectColumnNames AS NVARCHAR(MAX)
--Get distinct values of the PIVOT Column
SELECT @PivotColumnNames= ISNULL(@PivotColumnNames + ',','')
+ QUOTENAME(EquipmentId)
FROM (SELECT DISTINCT EquipmentId FROM EquipmentMapping) AS EquipmentId
--Get distinct values of the PIVOT Column with isnull
SELECT @PivotSelectColumnNames 
    = ISNULL(@PivotSelectColumnNames + ',','')
    +  QUOTENAME(EquipmentId) +' AS '
    + QUOTENAME('Equipment' + convert(varchar(10), EquipmentId) + 'id')
FROM (SELECT DISTINCT EquipmentId FROM EquipmentMapping) AS EquipmentId
--Prepare the PIVOT query using the dynamic
SET @DynamicPivotQuery =
N'SELECT EmployeeId, ' + @PivotSelectColumnNames + '
From (
     select  EmployeeId,
             EquipmentId
     from EquipmentMapping
     ) x
PIVOT(MAX(EquipmentId)
FOR EquipmentId IN (' + @PivotColumnNames + ')) AS PVTTable'
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery


run \| edit \| history \| help	0