creating a dynamic list of columns for CROSS APPLY (1 Viewer)

[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.