ccravenbartle
asked on
SQL SERVER Unpivot on unknown number of columns
I need to modify this statement :
SELECT AC.FieldName, UDFN.AnalysisType FROM AnalysisCodes AC
INNER JOIN
(
SELECT AnalysisType, AnlCol FROM UserDefFieldNames
UNPIVOT
( AnalysisType
for AnlCol in ([Anl1], [Anl2], [Anl3], [Anl4], [Anl5], [Anl14],[Anl22])
) UNPIV
) UDFN ON AC.FieldName = UDFN.anlcol
to replace the fixed list of AnlCol columns with the column names generated by the query below. How do I do this? I can't get the syntax right.
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'UserDefFieldNames' AND COLUMN_NAME LIKE 'Anl%'
SELECT AC.FieldName, UDFN.AnalysisType FROM AnalysisCodes AC
INNER JOIN
(
SELECT AnalysisType, AnlCol FROM UserDefFieldNames
UNPIVOT
( AnalysisType
for AnlCol in ([Anl1], [Anl2], [Anl3], [Anl4], [Anl5], [Anl14],[Anl22])
) UNPIV
) UDFN ON AC.FieldName = UDFN.anlcol
to replace the fixed list of AnlCol columns with the column names generated by the query below. How do I do this? I can't get the syntax right.
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'UserDefFieldNames' AND COLUMN_NAME LIKE 'Anl%'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER