Converting a Hash Map to a Table

Several times I have run into a conversion where I have needed to take a table that stores key value pairs into a table that lists all values for a given record.

The table look something like this:

Item_IDFieldValue
3ITEM CLASSIFICATIONHOT DOG
3ITEM DESCRIPTIONA TASTY CHICAGO STYLE DOG
3UNIT OF MEASURELENGTH
3HAS BUNY
4ITEM CLASSIFICATIONCORNDOG
4DIPPING SAUCEMUSTARD
4HAS BUNN
5ITEM CLASSIFICATIONHAMBURGER
5ITEM DESCRIPTIONA HAMBURGER WITH NO CHEESE
5UNIT OF MEASUREPOUND
5HAS BUNY

What I really wanted was something like this:

Item_IDItem ClassificationItem DescriptionUnit Of MeasureHas BunDipping Sauce
3HOT DOGA TASTY CHICAGO STYLE DOGLENGTHY
4CORNDOG NMUSTARD
5HAMBURGERA HAMBURGER WITH NO CHEESEPOUNDY

This is kind of like a PIVOT function, but since there can be a varied number of distinct records in the FIELD column, a pivot won’t always work.

What we can do is create a dynamic sql statement that will scan the distinct FIELD column and create a table for us based on the elements in the column.

DECLARE @selectpiece VARCHAR(1000)
   ,@field VARCHAR(1000)
   ,@columnDef VARCHAR(1000)
   ,@selectquery VARCHAR(MAX)
   ,@insertQuery VARCHAR(MAX)
   ,@createQuery VARCHAR(MAX)
         
SET @createQuery = 'CREATE TABLE dbo.TempElements (ITEM_ID INT'
SET @selectQuery = 'SELECT ITEM_ID'
SET @insertQuery = 'INSERT INTO dbo.TempElements (ITEM_ID'
 
DECLARE cur CURSOR FAST_FORWARD READ_ONLY
FOR
SELECT DISTINCT
        ',MAX(CASE WHEN [ELEMENT_DESC] = ''' + [ELEMENT_DESC] + '''
            THEN COALESCE([EL].[LIST_DESC],[DETAIL_VALUE])
            ELSE NULL
          END) AS [' + [ELEMENT_DESC] + ']' AS fieldSelect
       ,',[' + [ELEMENT_DESC] + ']' AS ColumnName
       ,',[' + [ELEMENT_DESC] + '] VARCHAR(MAX)' AS ColumnDef
FROM    [dbo].[ATTRIBUTE_ELEMENT] AS AE
WHERE   [ELEMENT_ID] IN (SELECT [ELEMENT_ID]
                         FROM   [ITEM_DETAIL] AS ID)
 
OPEN cur
FETCH NEXT FROM cur INTO @selectpiece, @field, @columnDef
WHILE @@FETCH_STATUS = 0
    BEGIN
     
        SET @createQuery += @columnDef
        SET @insertQuery += @field
        SET @selectQuery += @selectpiece
 
        FETCH NEXT FROM cur INTO @selectpiece, @field, @columnDef
    END
 
CLOSE cur
DEALLOCATE cur
 
SET @createQuery += ')'
 
SET @insertQuery += ')' + @selectquery + '
         FROM   [dbo].[ITEM_DETAIL] AS ID
                LEFT JOIN [dbo].[ELEMENT_LIST] AS EL
                    ON [ID].[ELEMENT_ID] = [EL].[ELEMENT_ID]
                       AND [ID].[LIST_ID] = [EL].[LIST_ID]
                JOIN [dbo].[ATTRIBUTE_ELEMENT] AS AE
                    ON [ID].[ELEMENT_ID] = [AE].[ELEMENT_ID]
            GROUP BY ITEM_ID'
 
EXEC sp_sqlexec @createQuery
EXEC sp_sqlexec @insertQuery

Leave a Reply

Your email address will not be published.