Challenge with Pivoting data

Hello Tom,

Thank you very much for responding to my post. I have adapted your example to fit with the databases that I am extracting data from. Here is the query that I have written (so far)...

DECLARE @SQL nvarchar(max);

;
WITH cteRN AS
(
        SELECT 
        T0.ItemCode
        , T0.ItemName
        , T1.BINLABEL AS Binlabel
        , T1.QUANTITY AS BinQty
        , ROW_NUMBER()
                OVER(PARTITION BY T0.ItemCode ORDER BY T1.BINLABEL) AS rn

        FROM OITM T0
        LEFT JOIN A1Warehouse..BINLOCAT T1 ON T1.PRODUCT = T0.ItemCode collate SQL_Latin1_General_CP850_CI_AS
)

SELECT @SQL = (
        SELECT
        CAST('' AS nvarchar(max)) + ', MAX(CASE WHEN rn = ' + CAST(rn AS nvarchar(5)) + ' THEN BinLabel END) As Binlabel' + CAST(rn AS nvarchar(5)) 
    + ', MAX(CASE WHEN rn = ' + CAST(rn AS nvarchar(5)) + ' THEN BinQty END) AS BinQty' + CAST(rn AS nvarchar(5))
   
        FROM 
        (
                SELECT DISTINCT rn FROM cteRN
        )x
        ORDER BY rn
   
        FOR XML PATH('')) 

SELECT @SQL = STUFF(@SQL, 1, 2, '');

SELECT @SQL = 
        'SELECT
        Tx.ItemCode
        , Max(Tx.ItemName) AS Description
        ,' + @SQL + ' 
        
        FROM 
        (
                SELECT 
                T0.ItemCode
                , T0.ItemName
                , T1.BINLABEL AS Binlabel
                , T1.QUANTITY AS BinQty
                , ROW_NUMBER() 
                        OVER(PARTITION BY T0.ItemCode ORDER BY T1.BINLABEL) AS rn

                FROM
                OITM T0
                LEFT JOIN A1Warehouse..BINLOCAT T1 ON T1.PRODUCT = T0.ItemCode collate SQL_Latin1_General_CP850_CI_AS

        ) AS Tx 
        GROUP BY Tx.ItemCode'

Exec(@SQL);

The results of the query above look like so -

There are many NULLs in the output, and I would like to remove these. However to be completely honest I do not know where to start with regards to removing them. I only started with T-SQL earlier this year and I do not fully understand everything that is taking place in the query. I am very comfortable with SELECT, and have a working understanding of the ROW_NUMBER() function, which I have used before on several occasions (though admittedly not quite like this) however I don't have any understanding of the following

• How you can 'assign' multiple values to the @SQL variable (if that is indeed what is happening)


• What 'FOR XML PATH('') actually does


• Dynamic SQL in general...

Ultimately I have a long way to come before I am truly comfortable with this query, however if you could share some insights into how I can go about removing the NULLs it will be greatly appreciated.

Thanks again.

Kind Regards,

David