SQL Pivot Issue

Question

If you could please help with this. The code generates an error: Msg 8156, Level 16, State 1, Line 236 The column 'Classification_Value_Id' was specified multiple times for 'piv1'.

I am doing this on SQL Server. The steps for the code is as follows: 1. Unpivot the data from the source table DB.[dbo].[Classification] into one column 2. Join this unpivoted data to a table called DB.dbo.[Classification_Value] to return/add the column 'cv.Classification_Name' to the data set 3. Pivot this dataset (This is the part returning the error)

CODE:

SELECT
       activityCode
     , actjvPartnerRef
     , actMonth
     , actSalesChannel
     , addCBPCharge
     , agentId
     , appType
     , areaCode

--SELECT
--polRef,[Arrangement_Id],UnpivotedData.Classification_Value_Id,UnpivotedData.Classification_Scheme_Id,ColValues, ColNames,cv.Classification_Name

FROM
(
    SELECT top 10
           [polRef]
         , [Arrangement_Id]
         , [Classification_Scheme_Id]
         , [Classification_Value_Id]
       -- ,[Arrangement_Classification_Type_Id]
       -- ,[Effective_TimeStamp]
       -- ,[End_date]
         , CAST((ISNULL([character_measure],'')) AS NVARCHAR(MAX)) AS character_measure
         , CAST((ISNULL([datetime_measure],'')) AS NVARCHAR(MAX)) AS datetime_measure
         , CAST([decimal_measure] AS NVARCHAR(MAX)) AS decimal_measure
         , CAST((ISNULL([integer_measure],'')) AS NVARCHAR(MAX)) AS integer_measure
         , CAST((ISNULL([logical_measure],'')) AS NVARCHAR(MAX)) AS logical_measure
         , CAST((ISNULL([charmax_measure],'')) AS NVARCHAR(MAX)) AS charmax_measure
         , CAST((ISNULL([long_measure],'')) AS NVARCHAR(MAX)) AS long_measure
      FROM DB.[dbo].[Classification] 
) AS SrcDataConverted

UNPIVOT
(
ColValues FOR ColNames IN
(
     character_measure
   , datetime_measure
   , decimal_measure
   , integer_measure
   , logical_measure
   , charmax_measure
   , long_measure
)
) AS UnpivotedData

LEFT JOIN DB.dbo.[Classification_Value] cv
ON  cv.[Classification_Scheme_Id] = UnpivotedData.[Classification_Scheme_Id]
AND cv.Classification_Value_Id = UnpivotedData.Classification_Value_Id

PIVOT
(MAX(ColValues) for Classification_Name in (
    activityCode
  , actjvPartnerRef
  , actMonth
  , actSalesChannel
  , addCBPCharge
  , agentId
  , appType
  , areaCode
)) AS piv1; 

Any help would be much appreciated

Thank you

Answer 1

StuarLC: An additional derived table needs to wrap the results of the UNPIVOT before commencing the re-PIVOT, as the join introduces a duplicated Classification_Value_Id and Classification_Scheme_id, which is needed for the join.

select 
       activityCode
     , actjvPartnerRef
     , actMonth
     , actSalesChannel
     , addCBPCharge
     , agentId
     , appType
     , areaCode
from (
   SELECT polRef
        , [Arrangement_Id]
        , UnpivotedData.Classification_Value_Id
        , UnpivotedData.Classification_Scheme_Id
        , ColValues
        , ColNames
        , Classification_Name
    FROM (
        SELECT [polRef]
             , [Arrangement_Id]
             , [Classification_Scheme_Id]
             , [Classification_Value_Id]
             , CAST((ISNULL([character_measure],'')) AS NVARCHAR(MAX)) AS character_measure
             , CAST((ISNULL([datetime_measure],'')) AS NVARCHAR(MAX)) AS datetime_measure
             , CAST([decimal_measure] AS NVARCHAR(MAX)) AS decimal_measure
             , CAST((ISNULL([integer_measure],'')) AS NVARCHAR(MAX)) AS integer_measure
             , CAST((ISNULL([logical_measure],'')) AS NVARCHAR(MAX)) AS logical_measure
             , CAST((ISNULL([charmax_measure],'')) AS NVARCHAR(MAX)) AS charmax_measure
             , CAST((ISNULL([long_measure],'')) AS NVARCHAR(MAX)) AS long_measure
          FROM DB.[dbo].[Classification] 
    ) AS SrcDataConverted
    UNPIVOT
    (
        ColValues FOR ColNames IN
        (
             character_measure
           , datetime_measure
           , decimal_measure
           , integer_measure
           , logical_measure
           , charmax_measure
           , long_measure
        )
    ) AS UnpivotedData
    LEFT JOIN 
        DB.dbo.[Classification_Value] cv
    ON  cv.[Classification_Scheme_Id] = UnpivotedData.[Classification_Scheme_Id]
    AND cv.Classification_Value_Id = UnpivotedData.Classification_Value_Id
) as src
PIVOT
(   
    MAX(ColValues) for Classification_Name in (
            activityCode
          , actjvPartnerRef
          , actMonth
          , actSalesChannel
          , addCBPCharge
          , agentId
          , appType
          , areaCode
    )
) AS piv1;