September 28, 2016 at 10:46 am
Hello,
I am trying to create a PIVOT table to list employee by location. I want to show my list with the Location and have two additional columns that will list Employee IDs. Below is my code and I cannot seem to get my PIVOT table correct. Can someone please offer some advice? Thank you in advance!
-- HERE ARE MY DESIRED RESULTS
Loc Emp1 Emp2
1 123 222
2 222
3 331 332
4 444 441
5 555
6 552
-- HERE IS MY CODE
CREATE TABLE #LookUp (Loc int, EmpID int)
INSERT INTO #LookUp VALUES (1,123)
INSERT INTO #LookUp VALUES (1,222)
INSERT INTO #LookUp VALUES (2,222)
INSERT INTO #LookUp VALUES (3,331)
INSERT INTO #LookUp VALUES (3,332)
INSERT INTO #LookUp VALUES (4,444)
INSERT INTO #LookUp VALUES (4,441)
INSERT INTO #LookUp VALUES (5,555)
INSERT INTO #LookUp VALUES (6,552)
-- SELECT * FROM #LookUp
SELECT Loc, '' as Emp1, '' as Emp2
FROM
(
SELECT EmpID, Loc FROM #LookUp
) src
pivot
(
MAX(EmpID)
FOR Loc IN (Emp1, Emp2)
) pvt
September 28, 2016 at 11:17 am
Quick suggestion
😎
USE TEEST;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'tempdb..#LookUp') IS NOT NULL DROP TABLE #LookUp;
CREATE TABLE #LookUp (Loc int, EmpID int)
INSERT INTO #LookUp
VALUES (1,123)
,(1,222)
,(2,222)
,(3,331)
,(3,332)
,(4,444)
,(4,441)
,(5,555)
,(6,552);
;WITH BASE_DATA AS
(
SELECT
LU.Loc
,ROW_NUMBER() OVER
(
PARTITION BY LU.Loc
ORDER BY @@VERSION
) AS RID
,LU.EmpID
FROM #LookUp LU
)
SELECT
BD.Loc
,MAX(CASE WHEN BD.RID = 1 THEN BD.EmpID END ) AS EMP1
,MAX(CASE WHEN BD.RID = 2 THEN BD.EmpID END ) AS EMP2
FROM BASE_DATA BD
GROUP BY BD.Loc
;
Output
Loc EMP1 EMP2
----------- ----------- -----------
1 123 222
2 222 NULL
3 331 332
4 444 441
5 555 NULL
6 552 NULL
September 28, 2016 at 11:46 am
works great and thank you! Row number is what I was missing!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply