
Table_Name |
Field_Name |
IS_Visible |
Table_ABC |
Field_01 |
1 |
Table_ABC |
Field_02 |
1 |
Table_ABC |
Field_03 |
0 |
Table_XYZ |
Field_01 |
1 |
Table_XYZ |
Field_02 |
1 |
Table_XYZ |
Field_03 |
0 |
I have a table that has meta data about other tables. How would I get the following results DYNAMICALLY (with N groups), basing from the provided sample (above picture or above data)?
RESULTS:
SELECT
'From Table_ABC'
, Field_01
, Field_02
FROM Table_ABC
SELECT
'From Table_XYZ'
, Field_01
, Field_02
FROM Table_XYZ
Once I get the above, I would like to able to execute that dynamic SQL string with something similar to:
EXECUTE sp_executesql @SQLQuery
Thank You
I might be able to get to the result on my own but would appreciate if someone can answer this for me. Both produce the same "row to string" concatenation result but why choose one (FOR XML PATH) over the other?
SET @Fields =''
SET @Fields =
(
SELECT
CHAR(10) + CHAR(9) + QUOTENAME(aT1.Field_Name) + CHAR(9) + ' AS ' + QUOTENAME(aT1.Field_Name) + ','
FROM #TEMPTable aT1
ORDER BY aT1.My_ID
FOR XML PATH ('')
)
PRINT @Fields
SET @Fields =''
SELECT
@Fields = @Fields + CHAR(13) + CHAR(10) + CHAR(9) + QUOTENAME(aT1.Field_Name) + CHAR(9) + ' AS ' + QUOTENAME(aT1.Field_Name) + ','
FROM #TEMPTable aT1
ORDER BY aT1.My_ID
PRINT @Fields
I was able to get to the result, based on some code researching, using STUFF, FOR XML PATH and grouping.
However, would still like to understand my previous question on why use FOR XML PATH over the second query.
In case someone else might need this and feel free to critique for me. Thank You
--FOR XML PATH('') is used to concatenate multiple rows of data into a single string.
--STUFF removes the leading comma
SELECT
QUOTENAME(aT2.Table_Name) AS [Table_Name]
, 'SELECT' + CHAR(13) + CHAR(10) + CHAR(9) +
--SUBSTRING(
STUFF(
(
SELECT
CHAR(10) + CHAR(9) + ', ' + QUOTENAME(aT1.Field_Name) + CHAR(9) + ' AS ' + QUOTENAME(aT1.Field_Name)
FROM #TEMPTable aT1
WHERE aT1.Table_Name = aT2.Table_Name
AND aT1.Is_Visible = 1
ORDER BY aT1.My_ID
FOR XML PATH ('')
)
, 1, 3, ' ') --For STUFF function (STUFF vs REPLACE) Replace function replaces everything in the string. Stuff function replace only a section of the string.
--, 4, 2000) --For SUBSTRING function
+ CHAR(13) + CHAR(10) +
'FROM ' + QUOTENAME(aT2.Table_Name) + CHAR(13) + CHAR(10)
AS [Dynamic_SQL]
FROM
(
SELECT Table_Name FROM #TEMPTable
GROUP BY Table_Name
) aT2
I think, you can use a cursor with loop to make a sql string to execute.
Phat, yeah, that is what I did.
Would you be able to answer my second post (above), the difference between the two SQLs?
Thanks
Results of both are equal, but the second use recursion.
1 Like
The written code can able to generate the required output,
But, I would like to add one thing here,
- Could have added the schema name before the table name in the [Dynamic_SQL] column which could work without issue if any table has other than default schema