Unpivot Multiple columns from specific Table

Question

I am using following SQL Script to unpivot the data when I execute I am receiving this error : specified in the UNPIVOT operator conflicts with the existing column name in the UNPIVOT argument.

 SELECT 
        HelpDeskName
        , TRY_CONVERT(NUMERIC(10,4),loggedInAgents) AS loggedInAgents
        , TRY_CONVERT(NUMERIC(10,4),AvailableAgents) AS AvailableAgents
        , TRY_CONVERT(NUMERIC(10,4),UnAvailableAgent) AS UnAvailableAgent
        , TRY_CONVERT(NUMERIC(10,4),TotalCalls) AS TotalCalls
        , TRY_CONVERT(NUMERIC(10,4),CallsHandled) AS CallsHandled
        , ReceivedTime
    FROM [Final].[UCCX]  AS U
    UNPIVOT
        (

            HelpDeskName,
            ReceivedTime
            for Category in (LoggedInAgents,AvailableAgents,UnAvailableAgent,TotalCalls,CallsHandled)
        ) Z ;

The output columns from unpivot I would like to see is HelpDeskName,ReceivedTime, Category, CategoryValue. Please advise or help with this Query.

Answer 1

Two Quick Options

Option 1 - Via Cross Apply

The Cross Apply approach offers a bit more freedom/flexibility. For example you can easily rename the Categories or re-cast the values.

Select A.HelpDeskName
      ,A.RecievedTime
      ,B.*
 From  YourTable A
 Cross Apply (values ('LoggedInAgents'   ,A.LoggedInAgents)
                    ,('AvailableAgents'  ,A.AvailableAgents)
                    ,('UnavailableAgents',A.UnavailableAgents)
                    ,('TotalCalls'       ,A.TotalCalls)
                    ,('CallsHandled'     ,A.CallsHandled)
             ) B (Category,CategoryValue)

Option 2 - Via UnPivot

Select HelpDeskName,RecievedTime,Category,CategoryValue
 From  YourTable
UnPivot ( CategoryValue For Category in (LoggedInAgents,AvailableAgents,UnavailableAgents,TotalCalls,CallsHandled) ) u;

Both would return something like this