Select single row of data for each ID in Select Statement

I'm receiving the following "Ambiguous column name 'JoinFromHdrPKey'."

DECLARE @start DATE
DECLARE @end DATE
SET @start = '20170101'
SET @end = '20170401'
;
WITH
      CTEid AS (
                  SELECT
                        JoinFromHdrPKey
                  FROM [dataKLL].[dbo].[KLL Extender UDF Values with Data]
                  WHERE UDF_Date_Data >= @start AND UDF_Date_Data < @end
            )
,     CTEraw AS (
                  SELECT
                        JoinFromHdrPKey
                      , UDF_FIELD
                      , UDF_Char_Data
                      , UDF_Date_Data
                      , UDF_Numeric_Data
                  FROM [dataKLL].[dbo].[KLL Extender UDF Values with Data] t
                  INNER JOIN CTEid r on t.JoinFromHdrPKey = r.JoinFromHdrPKey
            )
SELECT
      JoinFromHdrPKey
    , max(case when UDF_FIELD = 'FOB1VENDOR' then UDF_Char_Data    end) as FOB1VENDOR
    , max(case when UDF_FIELD = 'FOB1DATE'   then UDF_Date_Data    end) as FOB1DATE
    , max(case when UDF_FIELD = 'FOB1AMT'    then UDF_Numeric_Data end) as FOB1AMT

    , max(case when UDF_FIELD = 'FOB2VENDOR' then UDF_Char_Data    end) as FOB2VENDOR
    , max(case when UDF_FIELD = 'FOB2DATE'   then UDF_Date_Data    end) as FOB2DATE
    , max(case when UDF_FIELD = 'FOB2AMT'    then UDF_Numeric_Data end) as FOB2AMT

    , max(case when UDF_FIELD = 'MISC1VENDOR' then UDF_Char_Data    end) as MISC1VENDOR
    , max(case when UDF_FIELD = 'MISC1DATE'   then UDF_Date_Data    end) as MISC1DATE
    , max(case when UDF_FIELD = 'MISC1AMT'    then UDF_Numeric_Data end) as MISC1AMT

    , max(case when UDF_FIELD = 'MISC2VENDOR' then UDF_Char_Data    end) as MISC2VENDOR
    , max(case when UDF_FIELD = 'MISC2DATE'   then UDF_Date_Data    end) as MISC2DATE
    , max(case when UDF_FIELD = 'MISC2AMT'    then UDF_Numeric_Data end) as MISC2AMT

FROM CTEraw d
GROUP BY
      JoinFromHdrPKey
;

Select allOpen in new window

Do you know what that error message means? If not you should really learn how to deal with it (it will save you a lot of time).

In one of the queries I provided there is more than one table (or derived table) that has the column JoinFromHdrPKey and because there is more than 1 reference it is "ambiguous" if you don't specify which one of those 2 columns you want to use

,     CTEraw AS (
                  SELECT
                        t.JoinFromHdrPKey
                      , t.UDF_FIELD
                      , t.UDF_Char_Data
                      , t.UDF_Date_Data
                      , t.UDF_Numeric_Data
                  FROM [dataKLL].[dbo].[KLL Extender UDF Values with Data] t
                  INNER JOIN CTEid r on t.JoinFromHdrPKey = r.JoinFromHdrPKey
            )

where you see the yellow highlight is where I forgot to use a specific alias to ensure the query wasn't ambiguous

In truth production grade code should explicitly specify where all columns come from - this is a best practice sometimes missing from our proposed solutions

Understood, So I just about have this where it needs to be however I have records that do not contain any dates 16 thru 20. Why are these displaying? See sample data

DECLARE @start DATE;
DECLARE @end DATE;
SET @start = '20170307';
SET @end = '20170313';
WITH    CTEid
          AS ( SELECT   JoinFromHdrPKey
               FROM     [dataKLL].[dbo].[KLL Extender UDF Values with Data]
               WHERE    UDF_Date_Data >= @start
                        AND UDF_Date_Data < @end
             ),
        CTEraw
          AS ( SELECT   D.division ,
                        SHPMH.shpm_ref ,
                        t.JoinFromHdrPKey ,
                        t.UDF_FIELD ,
                        t.UDF_Char_Data ,
                        t.UDF_Date_Data ,
                        t.UDF_Numeric_Data
               FROM     [dataKLL].[dbo].[KLL Extender UDF Values with Data] t
                        INNER JOIN CTEid r ON t.JoinFromHdrPKey = r.JoinFromHdrPKey
                        INNER JOIN ZZMSHPMH SHPMH ON t.JoinFromHdrPKey = SHPMH.pkey
                        LEFT OUTER JOIN ZZCORDRD D ON D.SHP_SEQ = SHPMH.SHIPMENT_NUM
             )
    SELECT  Division ,
            shpm_ref ,
            JoinFromHdrPKey ,
            MAX(CASE WHEN UDF_FIELD = 'FOB1VENDOR' THEN UDF_Char_Data
                END) AS FOB1VENDOR ,
            MAX(CASE WHEN UDF_FIELD = 'FOB1DATE' THEN UDF_Date_Data
                END) AS FOB1DATE ,
            MAX(CASE WHEN UDF_FIELD = 'FOB1AMT' THEN UDF_Numeric_Data
                END) AS FOB1AMT ,
            MAX(CASE WHEN UDF_FIELD = 'FOB2VENDOR' THEN UDF_Char_Data
                END) AS FOB2VENDOR ,
            MAX(CASE WHEN UDF_FIELD = 'FOB2DATE' THEN UDF_Date_Data
                END) AS FOB2DATE ,
            MAX(CASE WHEN UDF_FIELD = 'FOB2AMT' THEN UDF_Numeric_Data
                END) AS FOB2AMT ,
            MAX(CASE WHEN UDF_FIELD = 'MISC1VENDOR' THEN UDF_Char_Data
                END) AS MISC1VENDOR ,
            MAX(CASE WHEN UDF_FIELD = 'MISC1DATE' THEN UDF_Date_Data
                END) AS MISC1DATE ,
            MAX(CASE WHEN UDF_FIELD = 'MISC1AMT' THEN UDF_Numeric_Data
                END) AS MISC1AMT ,
            MAX(CASE WHEN UDF_FIELD = 'MISC2VENDOR' THEN UDF_Char_Data
                END) AS MISC2VENDOR ,
            MAX(CASE WHEN UDF_FIELD = 'MISC2DATE' THEN UDF_Date_Data
                END) AS MISC2DATE ,
            MAX(CASE WHEN UDF_FIELD = 'MISC2AMT' THEN UDF_Numeric_Data
                END) AS MISC2AMT ,
            MAX(CASE WHEN UDF_FIELD = 'OCN1VENDOR' THEN UDF_Char_Data
                END) AS OCN1VENDOR ,
            MAX(CASE WHEN UDF_FIELD = 'OCNC1DATE' THEN UDF_Date_Data
                END) AS OCN1DATE ,
            MAX(CASE WHEN UDF_FIELD = 'OCN1AMT' THEN UDF_Numeric_Data
                END) AS OCN1AMT ,
            MAX(CASE WHEN UDF_FIELD = 'OCN2VENDOR' THEN UDF_Char_Data
                END) AS OCN2VENDOR ,
            MAX(CASE WHEN UDF_FIELD = 'OCN2DATE' THEN UDF_Date_Data
                END) AS OCN2DATE ,
            MAX(CASE WHEN UDF_FIELD = 'OCN2AMT' THEN UDF_Numeric_Data
                END) AS OCN2AMT ,
            MAX(CASE WHEN UDF_FIELD = 'DUTY1VENDOR' THEN UDF_Char_Data
                END) AS DUTY1VENDOR ,
            MAX(CASE WHEN UDF_FIELD = 'DUTY1DATE' THEN UDF_Date_Data
                END) AS DUTY1DATE ,
            MAX(CASE WHEN UDF_FIELD = 'DUTY1AMT' THEN UDF_Numeric_Data
                END) AS DUTY1AMT ,
            MAX(CASE WHEN UDF_FIELD = 'DUTY2VENDOR' THEN UDF_Char_Data
                END) AS DUTY2VENDOR ,
            MAX(CASE WHEN UDF_FIELD = 'DUTY2DATE' THEN UDF_Date_Data
                END) AS DUTY2DATE ,
            MAX(CASE WHEN UDF_FIELD = 'DUTY2AMT' THEN UDF_Numeric_Data
                END) AS DUTY2AMT ,
            MAX(CASE WHEN UDF_FIELD = 'INLAND1VENDOR' THEN UDF_Char_Data
                END) AS INLAND1VENDOR ,
            MAX(CASE WHEN UDF_FIELD = 'INLAND1DATE' THEN UDF_Date_Data
                END) AS INLAND1DATE ,
            MAX(CASE WHEN UDF_FIELD = 'INLAND1AMT' THEN UDF_Numeric_Data
                END) AS INLAND1AMT ,
            MAX(CASE WHEN UDF_FIELD = 'INLAND2VENDOR' THEN UDF_Char_Data
                END) AS INLAND2VENDOR ,
            MAX(CASE WHEN UDF_FIELD = 'INLAND2DATE' THEN UDF_Date_Data
                END) AS INLAND2DATE ,
            MAX(CASE WHEN UDF_FIELD = 'INLAND2AMT' THEN UDF_Numeric_Data
                END) AS INLAND2AMT
    FROM    CTEraw d
    GROUP BY Division ,
            JoinFromHdrPKey ,
            shpm_ref
    ORDER BY Division ,
            JoinFromHdrPKey ,
            shpm_ref;

Select allOpen in new window

Sample-Data.xlsx

Thank you. All the information you provided was very clear.

Happy to help.
Appreciate the prompt closure.
Cheers, Paul