CODEWITHSUNDEEP
sqlpivot
VALGS
VALGS

Reputation: 1

How to PIVOT three columns based on one field in SQL Server?

I know PIVOT related questions have been asked in this community before, but I can't seem to find what I need. I would like to pivot multiple columns based on the same field. I was able to accomplish the task by writing three separate queries and then joining them, but I was wondering if there is a better way to do it. Here is the table: enter image description here The result I need: enter image description here

I cannot embed the images yet, so hopefully the attached pictures will be available. I need to pivot Location, StartDate and EndDate based on the LocationStatus, so each employee will have a one line record. I can publish my code, but it seems to be very inefficient since I am doing three pivots and a join. Thank you.

Upvotes: -1

Views: 99

Answers (1)

John K.
John K.

Reputation: 525

As a workaround, you can triplicate the LocationStatus column, and then PIVOT on each of them individually:

SELECT
    EmployeeID,
    MAX([Primary]) AS PrimaryLocation,
    MAX([Secondary]) AS SecondaryLocation,
    MAX([Primary2]) AS PrimaryLocationStart,
    MAX([Primary3]) AS PrimaryLocationEnd,
    MAX([Secondary2]) AS SecondaryLocationStart,
    MAX([Secondary3]) AS SecondaryLocationEnd
FROM
    (
    SELECT 
        *, 
        LocationStatus+'2' AS LocationStatus2, 
        LocationStatus+'3' AS LocationStatus3 
    FROM 
        TableName
    ) X
PIVOT 
    (
    MAX([Location]) 
    FOR LocationStatus IN ([Primary], [Secondary])
    ) AS A
PIVOT 
    (
    MAX([StartDate]) 
    FOR LocationStatus2 IN ([Primary2], [Secondary2])
    ) AS B
PIVOT 
    (
    MAX([EndDate]) 
    FOR LocationStatus3 IN ([Primary3], [Secondary3])
    ) AS C
GROUP BY
    EmployeeID  
;

Upvotes: 1

Related Questions