This query retrieves the XML for a Lookup Table and displays the results in table format.

• Row and Column Titles will be shown in the first result set.
• You will need to replace the table name on line 1 of the query with the Lookup Table name in Control.
• Not compatible with SQL Server version 2008 or prior.

None. This is a selection query and no data is modified in the running of it.

DECLARE @PricingTable int = (select id from PricingTable where PricingTableName = 'Test')
IF OBJECT_ID('tempdb.dbo.#temp', 'U') IS NOT NULL drop table #temp;
IF OBJECT_ID('tempdb.dbo.#columns', 'U') IS NOT NULL drop table #columns; 
DECLARE @XMLData XML  = (select TableElementStr from PricingTable where ID = @PricingTable )
DECLARE @RowVariable varchar(50) = (select v.VariableName from PricingTable pt join variable v on v.id = pt.RowParam1ID where pt.id = @PricingTable)
DECLARE @ColVariable varchar(50) = (select v.VariableName from PricingTable pt join variable v on v.id = pt.ColParamID where pt.id = @PricingTable)
 
SELECT * INTO #Temp FROM (
SELECT
    Row1ParamValue = Node.Data.value('(Row1ParamValue)[1]', 'varchar(200)')
    , ColParamValue = ISNULL(Node.Data.value('(ColParamValue)[1]', 'VARCHAR(200)'),'Value')
    , ElementValue = ISNULL(CAST(Node.Data.value('(ElementValue)[1]', 'VARCHAR(100)') as decimal(10,4)),0)
FROM @XMLData.nodes('/TableElements/TableElement') Node(Data)
) T
 
SELECT distinct ColParamValue
INTO #Columns
FROM #Temp
ORDER BY ColParamValue DESC
 
 
DECLARE @Column varchar(max) = NULL
DECLARE @Columns nvarchar(max) = ''
DECLARE @statement nvarchar(max)
DECLARE @i int = 0;
 
 
DECLARE @count int
SET @count = (SELECT count(distinct ColParamValue) FROM #Temp)
 
SET @statement = 'select v1.VariableName as [Row Parameter], ISNULL(v2.VariableName, ''Value'') as [Column Parameter]
                    from PricingTable pt
                    join variable v1 on v1.id = pt.RowParam1ID
                    LEFT OUTER join variable v2 on v2.id = pt.ColParamID
                    where pt.id = '
SET @statement += (select cast(@PricingTable as nvarchar(50)))
SET @statement += '; '
 
SET @statement += 'SELECT Row1ParamValue,'
 
DECLARE @ColumnTable Table (col nvarchar(100))
 
 
WHILE (@i < @count)
BEGIN
 
    SET @Column = (SELECT top 1 ColParamValue FROM #Columns Order By ColParamValue)
 
    IF ISNUMERIC(@Column) = 1
        SET @Column = (SELECT top 1 CAST(ColParamValue as int) FROM #Columns Order By CAST(ColParamValue as int))
    ELSE
        SET @Column = (SELECT top 1 ColParamValue FROM #Columns Order By ColParamValue)
 
    IF @i = 0
        SET @Columns = '[' + @Column + ']'
    ELSE
        SET @Columns += ',' + '[' + @Column + ']'
 
    DELETE FROM #Columns WHERE ColParamValue = @Column
 
    SET @i += 1
 
END
 
SET @statement += @Columns
 
SET @statement +=  ' FROM
(SELECT Row1ParamValue, ElementValue, ColParamValue
    FROM #Temp) AS SourceTable
PIVOT
(    max(ElementValue)
    FOR ColParamValue in ('
 
SET @statement += @Columns
 
SET @statement +=  ')
    ) as Test Order by Row1ParamValue'
 
 
EXEC sp_executesql @statement
 
DROP TABLE #Columns
DROP TABLE #Temp

• Entered : 12/6/2018
• Version : Control 6.0+