Loop Through A Table Using Cross Apply And Union All The Results

Trying to write a query which will behave like a foreach Query : select label ,NTILE(10) over(order by label ASC) Quartile INTO #labelTempTab from dbo.ReportFieldsLookup The da

Solution 1:

Use Cross Apply to unpivot the result. Dynamic query query should be constructed in this format.

SELECT mnum,
       label,
       label_value,
       [Property Type]
FROM   #SetValuesTable
       CROSS apply(VALUES ('la1',la1),('la2',la2),('la3',la3),
                          ('sa1',sa1),('sa2',sa2),('sa3',sa3),
                          ('ha1',ha1),('ha2',ha2),('ha3',ha3),
                          ('ka1',ka1),('ka2',ka2)) cs (label, label_value) 

Dynamic query should be something like

DECLARE@labelVARCHAR(max)='',
        @sql   NVARCHAR(max)

SELECT@label+='('+''''+ label +''','+ label +'),'FROM   (SELECTDISTINCT Isnull(label, '') label
        FROM   #labelTempTab)a

SELECT@label=LEFT(@label, Len(@label) -1)

SET@sql='SELECT mnum,
             label,
             label_value,
             [Property Type]
    FROM   #SetValuesTable
           CROSS apply(VALUES '+@label+') cs (label, label_value) 'EXEC Sp_executesql @sql

Note : Since you are generating values list from #labelTempTab table make sure you have all the labels present in #SetValuesTable table