Thanks for the help this makes sense. The query works considerably faster. However I have one issue. Each part needs to run in order e.g.
For the 1st ID - if it was type 1 for example it needs to run
udf_TSC_qry_ExportBlockColumnParam1
and then
udf_TSC_qry_ExportBlockColumnParam2
So that in the output it is organised like:
OutputParam1 (ID 1)
OutputParam2 (ID 1)
OutputParam1 (ID 2)
OutputParam2 (ID 2)
and not
OutputParam1 (ID 1)
OutputParam1 (ID 2)
OutputParam2 (ID 1)
OutputParam2 (ID 2)
I hope this makes sense. Many thanks for your help, its almost there and runs in 1 second compared to 1 minute!
Oliver
SELECT *
FROM [dbo].[TSC_ExportedColumnIds] a
CROSS APPLY [dbo].[udf_TSC_qry_ExportBlockColumnParam1](a.ID) b
WHERE a.ColumnType = 1
UNION ALL
SELECT *
FROM [dbo].[TSC_ExportedColumnIds] a
CROSS APPLY [dbo].[udf_TSC_qry_ExportBlockColumnParam2](a.ID,4) b
WHERE a.ColumnType = 1
UNION ALL
SELECT *
FROM [dbo].[TSC_ExportedColumnIds] a
CROSS APPLY [dbo].[udf_TSC_qry_ExportSequenceColumnParam1](a.ID) b
WHERE a.ColumnType = 2
UNION ALL
SELECT *
FROM [dbo].[TSC_ExportedColumnIds] a
CROSS APPLY [dbo].[udf_TSC_qry_ExportSequenceColumnParam2](a.ID,4) b
WHERE a.ColumnType = 2
UNION ALL
SELECT A,B,C,D,E,F,G
FROM [dbo].[TSC_ExportedColumnIds] a
CROSS APPLY [dbo].[udf_TSC_qry_ExportEventColumnParam0](a.ID,4) b
WHERE a.ColumnType = 3;