How to select different records as columns in a view implemented in SQL Server 2012

Question

Consider the following table schema

Employee(Id int primary key,Name varchar(255))

EquipMent(Id int primary key ,EquipmentName varchar(255))

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

Consider the following data present in these tables as

Employee
(Id,Name)
(1,Jhon)
(2,Max)


EquipMent
(Id,EquipmentName)
(1,Hammer)
(2,Axe)
(3,Screw Driver)


EquipmentMapping
(Id,EmployeeId,EquipmentId)
(1,1,1)
(2,1,2)
(3,2,1)

I want to create a view which will have the following schema as

EmployeeEquipmentMappingView(Id int identity(1,1),EmployeeId int,Equipment1Id int,EquipMent2Id int)

Here the constraints are the employee cannot have more than 2 equipment mapped against him.

I want to select these two equipment mapped against him in Equipment1Id and EquipMent2Id columns.

The output will look like

Select * from EmployeeEquipmentMappingView
(Id ,EmployeeId ,Equipment1Id ,EquipMent2Id )
(1,1,1,2)
(2,2,1,NULL)

Start here: https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx, then go here: https://stackoverflow.com/questions/1343145/tsql-pivot-without-aggregate-function — Eric Brandt, Mar 16 '18 at 11:13

score 0 · Answer 1 · answered Mar 19 '18 at 11:22

try this

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

Live sample

How to select different records as columns in a view implemented in SQL Server 2012

1 Answers1