StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL How to effectively use dynamic sql query when output COLUMN order is important
text
Body
copied!<p>My question is really how to ensure that the ORDER of the OUTPUT is maintained when using dynamic query to get data out. I have a couple of dynamic queries that I am utilizing and I notice that when I run the same query on different machines, I get output in different orders. Currently, I store the data outputted into a temp table which assume the order of the output to be the same as that on my local machine but on production, it is all switched up. How do I get more control of this so that <strong>every time</strong> the query is run, the order of the output is maintained? </p> <p>When I go to use an Order BY in my dynamic query I get the error </p> <p><strong>Msg 1033, Level 15, State 1, Line 18 The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.</strong></p> <pre><code> DECLARE @colsUnpivot AS NVARCHAR(MAX), @query AS NVARCHAR(MAX), @colsPivot as NVARCHAR(MAX) IF EXISTS ( SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#ibutes') ) BEGIN DROP TABLE #ibutes END Create table #ibutes ( ProductID uniqueIdentifier, PAID uniqueidentifier, Label nvarchar(50), Val nvarchar(3072), unit nvarchar(50) ) ;With Number As ( Select 1 as rownum union all Select 2 union all Select 3 union all Select 4 union all Select 5 union all Select 6 union all Select 7 union all Select 8 union all Select 9 union all Select 10 union all Select 11 union all Select 12 union all Select 13 union all Select 14 union all Select 15 union all Select 16 union all Select 17 union all Select 18 union all Select 19 union all Select 20 union all Select 21 ), ProductDetail as ( select P.ProdID, N.rownum from IDWProduct P cross join Number N ) Insert into #ibutes Select ProdID , PAVibuteID, COALESCE(PANAme,''), COALESCE(PAVValue,''), COALESCE(unitLabel,'') from ProductDetail P Left join (select Pr.*, PAName, row_number() over (partition by PAVProductID order by PAVID) as Rn from IDWProductibuteValues Pr inner join IDWibutes on PAID = PAVibuteID Where PAIscustom = 0 AND PAIsManufacturerSpecific =0 AND PANAME NOT IN ('Brand Name', 'Standard', 'Application', 'Sub Brand', 'Type', 'Special Features')) Pr ON rownum = Pr.Rn And PAVProductID = ProdID left join IDWUnitofMeasures on Pr.PAVunit = unitID --Select * from #ibutes select @colsUnpivot = stuff((select ','+quotename(C.name) from tempdb.sys.columns as C where C.object_id = object_id('tempdb..#ibutes') AND C.name Not in ('ProductID') for xml path('')), 1, 1, '') --select @colsUnpivot select @colsPivot = STUFF((SELECT ',' + quotename(c.name + cast(t.rn as varchar(10))) from ( select row_number() over(partition by ProductID order by ProductID) rn from #ibutes ) t cross apply tempdb.sys.columns as C where C.object_id = object_id('tempdb..#ibutes') AND C.name Not in ('ProductID') group by c.name, t.rn order by t.rn FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') --select @colsPivot set @query = 'select * from ( select ProductID, col + cast(rn as varchar(10)) new_col, val from ( select Cast (PAID as NVarchar(3072)) PAID ,Cast (ProductID as NVarchar(3072)) ProductID ,Cast (Label as NVarchar(3072)) Label ,Cast (Val as NVarchar(3072)) Val ,Cast (unit as NVarchar(3072)) unit ,row_number() over(partition by ProductID order by ProductID) rn from #ibutes ) x unpivot ( val for col in ('+ @colsunpivot +') ) u ) x1 pivot ( max(val) for new_col in ('+ @colspivot +') ) p' exec(@query) </code></pre> <p>The results of running the query </p> <p>ON MY LOCAL/DEV MACHINE</p> <p>========================</p> <pre><code>select * from ( select ProductID, col + cast(rn as varchar(10)) new_col, val from ( select Cast (PAID as NVarchar(3072)) PAID ,Cast (ProductID as NVarchar(3072)) ProductID ,Cast (Label as NVarchar(3072)) Label ,Cast (Val as NVarchar(3072)) Val ,Cast (unit as NVarchar(3072)) unit ,row_number() over(partition by ProductID order by ProductID) rn from #ibutes ) x unpivot ( val for col in ([Label],[unit],[Val],[PAID]) ) u ) x1 pivot ( max(val) for new_col in ([Label1],[unit1],[Val1],[PAID1],[Label2],[unit2],[Val2],[PAID2],[Label3],[unit3],[Val3],[PAID3],[Label4],[unit4],[Val4],[PAID4],[Label5],[unit5],[Val5],[PAID5],[Label6],[unit6],[Val6],[PAID6],[Label7],[unit7],[Val7],[PAID7],[Label8],[unit8],[Val8],[PAID8],[Label9],[unit9],[Val9],[PAID9],[Label10],[unit10],[Val10],[PAID10],[Label11],[unit11],[Val11],[PAID11],[Label12],[unit12],[Val12],[PAID12],[Label13],[unit13],[Val13],[PAID13],[Label14],[unit14],[Val14],[PAID14],[Label15],[unit15],[Val15],[PAID15],[Label16],[unit16],[Val16],[PAID16],[Label17],[unit17],[Val17],[PAID17],[Label18],[unit18],[Val18],[PAID18],[Label19],[unit19],[Val19],[PAID19],[Label20],[unit20],[Val20],[PAID20],[Label21],[unit21],[Val21],[PAID21]) ) p </code></pre> <h2>ON PRODUCTION MACHINE HOWEVER IT IS DIFFERENT. PLEASE NOTICE THE CHANGE IN ORDER OF THE</h2> <p>COLUMNS</p> <pre><code>select * from ( select ProductID, col + cast(rn as varchar(10)) new_col, val from ( select Cast (PAID as NVarchar(3072)) PAID ,Cast (ProductID as NVarchar(3072)) ProductID ,Cast (Label as NVarchar(3072)) Label ,Cast (Val as NVarchar(3072)) Val ,Cast (Unit as NVarchar(3072)) Unit ,row_number() over(partition by ProductID order by ProductID) rn from #ibutes ) x unpivot ( val for col in ([PAID],[Label],[Val],[Unit]) ) u ) x1 pivot ( max(val) for new_col in ([PAID1],[Label1],[Val1],[Unit1],[PAID2],[Label2],[Val2],[Unit2],[PAID3],[Label3],[Val3],[Unit3],[PAID4],[Label4],[Val4],[Unit4],[PAID5],[Label5],[Val5],[Unit5],[PAID6],[Label6],[Val6],[Unit6],[PAID7],[Label7],[Val7],[Unit7],[PAID8],[Label8],[Val8],[Unit8],[PAID9],[Label9],[Val9],[Unit9],[PAID10],[Label10],[Val10],[Unit10],[PAID11],[Label11],[Val11],[Unit11],[PAID12],[Label12],[Val12],[Unit12],[PAID13],[Label13],[Val13],[Unit13],[PAID14],[Label14],[Val14],[Unit14],[PAID15],[Label15],[Val15],[Unit15],[PAID16],[Label16],[Val16],[Unit16],[PAID17],[Label17],[Val17],[Unit17],[PAID18],[Label18],[Val18],[Unit18],[PAID19],[Label19],[Val19],[Unit19],[PAID20],[Label20],[Val20],[Unit20],[PAID21],[Label21],[Val21],[Unit21]) ) p </code></pre> <p>Please note the difference btw local machine where the order seems alphabetical </p> <p><strong>[Label1],[unit1],[Val1],[PAID1],[Label2],[unit2].</strong> . . . </p> <p>while on Production machine, it is not</p> <p><strong>[PAID1],[Label1],[Val1],[Unit1],[PAID2],[Label2],[Val2],[Unit2], .. . . .</strong></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload