Explanation of SQL
This query retrieves the XML for a Lookup Table and displays the results in table format.
Notes about SQL
- 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.
Risk of Data Corruption if Run Improperly
None. This is a selection query and no data is modified in the running of it.
SQL
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
Version Information
- Entered : 12/6/2018
- Version : Control 6.0+
You could leave a comment if you were logged in.