creating a dynamic list of columns for CROSS APPLY

[MadPiet]

This is an academic question at this point, but I was just wondering....
(I had dozens of these tables to deal with... I was just wondering if there was a less painful way of creating the unpivoted view of the table)

I have a table with an absurd number of repeating groups

USE [Itchy]
GO

/****** Object:  Table [dbo].[Toxicity]    Script Date: 3/8/2026 2:39:09 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Toxicity](
    [RecordID] [int] IDENTITY(1,1) NOT NULL,
    [PatientID] [int] NOT NULL,
    [Cycle] [tinyint] NOT NULL,
    [ANEMIA] [tinyint] NULL,
    [Causality1] [tinyint] NULL,
    [Relatedness1] [tinyint] NULL,
    [ALOPECIA] [tinyint] NULL,
    [Causality2] [tinyint] NULL,
    [Relatedness2] [tinyint] NULL,
    [ANOREXIA] [tinyint] NULL,
    [Causality3] [tinyint] NULL,
    [Relatedness3] [tinyint] NULL
) ON [PRIMARY]
GO

This is the query I'm trying to create:

SELECT PatientID, Cycle, Symptom, Grade, Causality, Relatedness
FROM dbo.Toxicity
CROSS APPLY (
    VALUES    ('Anemia', ANEMIA, Causality1, Relatedness1),
            ('Alopecia',ALOPECIA, Causality2, Relatedness2),
            ('Anorexia', ANOREXIA, Causality3, Relatedness3)
) ca(Symptom, Grade, Causality, Relatedness);

Is there an easy(ish) way to write this in TSQL? I can find the Causality[n] columns and return the previous and next columns easily. The hard part is building the VALUES () section. If I query the sys.all_columns table, I can return the field names and order in T-SQL... nothing doing.

SELECT ao.name AS TableName, ac.name AS ColumnName, ac.column_id
FROM sys.all_objects ao
    INNER JOIN sys.all_columns ac
    ON ao.object_id = ac.object_id
WHERE ao.Name = 'Toxicity'
AND ao.type_desc = 'USER_TABLE';

Do I have to loop through the columns in Currentdb().TableDefs("Toxicity").Fields collection? I mean, I could turn it into a table, but ... Then I can find the "Causality" columns and look left and right to find the related columns and build this dynamically. Can I filter the fields collection without iterating over the entire table? I mean, I could just persist that into another table and do it that way...

Is there an easier option that I'm just not seeing?

 

[tvanstiphout]

What did CoPilot or another AI say when you asked this question? I found it to be quite good at understanding T-SQL.

 

[Galaxiom]

You can use SQL to write dynamic SQL. I've done this where I kept all the information about temp tables I needed to generate and query in another table. The same thing would be possible by querying the system tables for information about the user tables you are working with.

I've only taken a glance but this page but appears to be covering the fundamentals of a technique you could probably employ. It can be a lot to take in but it quickly starts to make sense once you see output from the query that generates the SQL for your tables. Just think logically what you are trying to append to the sql string, (brackets, keywords etc) with each query and concatenate it.

Writing Dynamic SQL (A how to)

A little while back I wrote Best practice recommendations for writing Dynamic SQL where I made a number of suggestions for good habits when writing dynamic SQL. Along the same lines, this is my met…

sqlstudies.com

 

[June7]

If you are building query in Access, only way I know to use SQL to unpivot fields is with UNION or UNION ALL (limit of 50 SELECT lines). And as for dynamically building query, that would need VBA looping through fields.

 

[DHookom]

If you are building query in Access, only way I know to use SQL to unpivot fields is with UNION or UNION ALL (limit of 50 SELECT lines). And as for dynamically building query, that would need VBA looping through fields.

I agree with @June7 and would write code to create the union query in VBA. This should be too difficult based on the proscribed table structure with a limited number of repeating columns.

 

[June7]

Duane, did you mean "should not be too"?

Mad, why are there dozens of tables like this? Are their field names the same?

 

[Minty]

You can script it in a stored procedure, without going into too much actual detail (as I think I've done something similar but can't find it.),

I would loop through the field names into a #temp table then use that to drive the dynamic SQL string generation for the cross apply.
If there are hundreds of fields it might get a bit slow, but you could call it from another temp table with all the table names and make the whole process almost automatic.

 

[MadPiet]

Duane, did you mean "should not be too"?

Mad, why are there dozens of tables like this? Are their field names the same?

The fun part is that the column names are not consistent. The pattern is {PatientID, Cycle, {ToxicityName (where the grade is stored, as if it were a spreadsheet), Causality[n], [Relatedness[n]} )

Sorry, the curly braces are the repeating groups. In a nutshell, if you find the column_id (or column number), you can do something like tdf.Fields(n - 1) to get the previous column... (then I think name, too, but that's trivial). I did this once using UNION, but it's the ugliest way of doing anything. That's why I wanted to do it with CROSS APPLY... way cleaner.

 

[DHookom]

Yes, I meant "shouldn't be too difficult". I think the only pattern required is the grouping of three fields with the condition, causality, and relatedness.
Possible code but it could be much more generic by using function arguments to pass number of repeating fields and field names.

Public Function CreateUnion()
    Dim db As DAO.Database
    Dim td As DAO.TableDef
    Dim strPKField As String
    Dim strSQL As String
    Dim intI As Integer
    Dim intFirstFieldStart  As Integer  'which field begins the repeating
    Dim intFieldCount As Integer
    strPKField = "RecordID"
    Set db = CurrentDb
    Set td = db.TableDefs("Toxicity")
    intFieldCount = td.Fields.Count
    intFirstFieldStart = 3   'field numbers begin with 0
    strSQL = ""
    For intI = intFirstFieldStart To intFieldCount - 1 Step 3
        strSQL = strSQL & "SELECT [" & strPKField & "] , '" & td.Fields(intI).Name & "' as Symptom,  [" & td.Fields(intI).Name & "]  AS Grade, [" & td.Fields(intI + 1).Name & "] AS Causality, [" & _
            td.Fields(intI + 2).Name & "] AS Relatedness FROM Toxicity UNION ALL "
    Next
    strSQL = Left(strSQL, Len(strSQL) - Len("UNION ALL "))
    Set td = Nothing
    Set db = Nothing
    CreateUnion = strSQL
    
    
End Function

 

[sonic8]

temp table with all the table names

-> INFORMATION_SCHEMA.TABLES, INFORMATION_SCHEMA.COLUMNS

 

[MadPiet]

Duane, that's pretty much what I did when I did it in Access in a previous lifetime.
If I could do it with CROSS APPLY (and build the SQL for it), I could just return a sequence of "sets"
tdf.Fields(i).Name AS Symptom, tdf.fields(i) AS Grade, tdf.fields(i+1) AS Causality, tdf.fields(i+2) AS Relatedness. I just find the T-SQL syntax way neater, but it's a lot easier to write demonic SQL in Access.

 

[DHookom]

I don’t believe Access supports CROSS APPLY.

 

[MadPiet]

Think Pass through Query. It doesn't. Access SQL is stuck in the 1600s or so.

 

[sonic8]

but it's a lot easier to write demonic SQL in Access.

That's a very secret flavor of SQL.

Access SQL is stuck in the 1600s or so.

And that's where it is coming from...
now hush! Don't stir the Witchmaster General!

SCNR

 

[MadPiet]

Some daze... I swear. Maybe because it was Monday?

Public Function ListColumns(ByVal strTableName As String)
    Dim tdf As TableDef
    Dim i As Integer
    
    Set tdf = CurrentDb.TableDefs(strTableName)
    
    For i = 3 To CurrentDb.TableDefs(strTableName).Fields.Count - 1
    '--    Debug.Print CurrentDb.TableDefs(strTableName).Fields(i).Name
            If Left$(CurrentDb.TableDefs(strTableName).Fields(i).Name, 9) = "Causality" Then
                Debug.Print "CAUSALITY COLUMN: " & vbTab & CurrentDb.TableDefs(strTableName).Fields(i).Name
                Debug.Print "GRADE COLUMN: " & vbTab & CurrentDb.TableDefs(strTableName).Fields(i - 1).Name
                Debug.Print "Symptom: " & "'" & CurrentDb.TableDefs(strTableName).Fields(i - 1).Name & "'"
            End If
    Next i
End Function

Note to self: did you notice that when you create a linked table to SQL Server, it turns the "dbo." schema name into "dbo_" and then appends the object name? Funny how that works, isn't it? Was wondering why I couldn't refer to the tabledef "Toxicity" when that's not its name.

Yeah, here's your sign.

 

[MadPiet]

"Use the force, Luke!"

Do it in T-SQL, where the data is... just use a little dynamic SQL, like this...

use itchy;
go

DECLARE @TableName NVARCHAR(20) = 'Toxicity';
DECLARE @CrossApplyColumnList NVARCHAR(2000);
DECLARE @sql AS NVARCHAR(3000);
/*
    need to construct the CROSS APPLY stuff for each row.
*/
/* finally, aggregate the chunks into a single string */
SELECT @CrossApplyColumnList = STRING_AGG(tg.caGrp,', ')
FROM
(
    /* this is where I'm gluing the parts together to form a single row for each part of the cross apply */
    SELECT tName, ColumnGroup, SymptomColumn, GradeColumn, CausalityColumn, RelatednessColumn,
        '(' + SymptomColumn + ', ' + GradeColumn + ', ' + CausalityColumn + ', ' + RelatednessColumn + ')' AS caGrp
    FROM
    (
        SELECT   
            cl.tName,
            cl.column_id,
            cl.columnGroup,
            cl.rn,
            cl.colName,
            /* this is kinda silly... so I can get all the column names in the group into a single record */
            CASE WHEN cl.rn = 1 THEN QUOTENAME(colName,'''') ELSE NULL END AS SymptomColumn,
            CASE WHEN cl.rn = 1 THEN colName ELSE NULL END AS GradeColumn,
            CASE WHEN cl.rn = 1 THEN LEAD(colName,1) OVER (PARTITION BY ColumnGroup ORDER BY column_id) ELSE NULL END AS CausalityColumn,
            CASE WHEN cl.rn = 1 THEN LEAD(colName,2) OVER (PARTITION BY ColumnGroup ORDER BY column_id) ELSE NULL END AS RelatednessColumn
        FROM
        /* basically gets all the column names and groups them (that's what columnGroup is about) */
        (SELECT
                ao.name AS tName,
                ac.name as colName,
                ac.column_id,
                columnGroup = (column_id-1)/3,
                ROW_NUMBER() OVER (PARTITION BY (column_id-1)/3 ORDER BY ac.column_id) AS rn
        FROM sys.all_objects ao
            INNER JOIN sys.all_columns ac
            ON ao.object_id = ac.object_id
        WHERE ao.name = @TableName
        AND ac.column_id > 3) cl
    ) cn
    WHERE cn.rn = 1
) tg
GROUP BY tg.tName;

--PRINT @CrossApplyColumnList;
SET @sql = 'SELECT PatientID, Cycle, ca.Symptom, ca.Grade, ca.Causality, ca.Relatedness FROM [' + @TableName + ']'
SET @sql = @sql + ' CROSS APPLY (VALUES ' + @CrossApplyColumnList + ') ca(Symptom, Grade, Causality, Relatedness) WHERE Grade IS NOT NULL';
--PRINT @sql;

/* landing spot for the cleaned up data */
CREATE TABLE FixedToxicity(PatientID INT NOT NULL, Cycle TINYINT NOT NULL, Symptom VARCHAR(30), Grade TINYINT, Causality TINYINT, Relatedness TINYINT);
GO

/* now insert the cleaned up data (from the SQL in the @sql variable) into a permanent table. */
INSERT INTO FixedToxicity(PatientID, Cycle, Symptom, Grade, Causality, Relatedness)
EXEC (@sql);

That weird [Symptom], [Grade] part was giving me fits!

 

[DickyP]

Think Pass through Query. It doesn't. Access SQL is stuck in the 1600s or so.

CROSS APPLY in SQL Server specific, not standard SQL. Not to say it isn't useful though!

Of course, arguably, the uses for it are for badly non normalised data (which happens in the real world, unfortunately for we purists).

 

[MadPiet]

I sorted it out.. the answer was a tiny bit of dynamic SQL

ECLARE @TableName NVARCHAR(20) = 'Toxicity';
DECLARE @CrossApplyColumnList NVARCHAR(2000);
DECLARE @sql AS NVARCHAR(3000);
/*
need to construct the CROSS APPLY stuff for each row.
*/
/* finally, aggregate the chunks into a single string */
SELECT @CrossApplyColumnList = STRING_AGG(tg.caGrp,', ')
FROM
(
/* this is where I'm gluing the parts together to form a single row for each part of the cross apply */
SELECT tName, ColumnGroup, SymptomColumn, GradeColumn, CausalityColumn, RelatednessColumn,
'(' + SymptomColumn + ', ' + GradeColumn + ', ' + CausalityColumn + ', ' + RelatednessColumn + ')' AS caGrp
FROM
(
SELECT
cl.tName,
cl.column_id,
cl.columnGroup,
cl.rn,
cl.colName,
/* this is kinda silly... so I can get all the column names in the group into a single record */
CASE WHEN cl.rn = 1 THEN QUOTENAME(colName,'''') ELSE NULL END AS SymptomColumn,
CASE WHEN cl.rn = 1 THEN colName ELSE NULL END AS GradeColumn,
CASE WHEN cl.rn = 1 THEN LEAD(colName,1) OVER (PARTITION BY ColumnGroup ORDER BY column_id) ELSE NULL END AS CausalityColumn,
CASE WHEN cl.rn = 1 THEN LEAD(colName,2) OVER (PARTITION BY ColumnGroup ORDER BY column_id) ELSE NULL END AS RelatednessColumn
FROM
/* basically gets all the column names and groups them (that's what columnGroup is about) */
(SELECT
ao.name AS tName,
ac.name as colName,
ac.column_id,
columnGroup = (column_id-1)/3,
ROW_NUMBER() OVER (PARTITION BY (column_id-1)/3 ORDER BY ac.column_id) AS rn
FROM sys.all_objects ao
INNER JOIN sys.all_columns ac
ON ao.object_id = ac.object_id
WHERE ao.name = @TableName
AND ac.column_id > 3) cl
) cn
WHERE cn.rn = 1
) tg
GROUP BY tg.tName;

--PRINT @CrossApplyColumnList;
SET @sql = 'SELECT PatientID, Cycle, ca.Symptom, ca.Grade, ca.Causality, ca.Relatedness FROM [' + @TableName + ']'
SET @sql = @sql + ' CROSS APPLY (VALUES ' + @CrossApplyColumnList + ') ca(Symptom, Grade, Causality, Relatedness) WHERE Grade IS NOT NULL';
--PRINT @sql;

CREATE TABLE FixedToxicity(PatientID INT NOT NULL, Cycle TINYINT NOT NULL, Symptom VARCHAR(30), Grade TINYINT, Causality TINYINT, Relatedness TINYINT);
GO

INSERT INTO FixedToxicity(PatientID, Cycle, Symptom, Grade, Causality, Relatedness)
EXEC (@sql);