Insert a row as first row in the result of a dynamic query

Question

My below query is returning results successfully however I want to add a second header in the result:

DECLARE @cols AS NVARCHAR(MAX),
        @SubHeader AS NVARCHAR(MAX)
        @query AS NVARCHAR(MAX)

SELECT
    @cols = STUFF((SELECT ',' + QUOTENAME(SP)
                   FROM #MyTable
                   WHERE SP != ''
                   GROUP BY SP
                   ORDER BY SP
                   FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

SET @query = 'SELECT ET,' + @cols + ' FROM 
                   (SELECT ET, SP, ET
                    FROM #MyTable 
                    WHERE SP != '''') x
               PIVOT
                   (MAX(ET)
                    FOR SP IN (' + @cols + ')) p '

PRINT @query
EXECUTE(@query);

The second header comes from the values in @SubHeader variable declared above.

Alternatively, if we can merge existing header and SubHeader and show like:

14-DEF, 15-GHI

Result presently is shown below:

I need output of headers as below:

Edited The below schema can be considered from where the data is being pulled using above query:

declare @SampleData Table
(
    ET varchar(30),
    ProductId varchar(30),
    PID varchar(20),
    SP varchar(30),
    EventT Time,
    EventD Date
)

ET - values in numeric: Any two digit numbers PID - abc, def, geh

(This is the sub-header values of which this post is all about) SP -

These are the numbers which are at present coming as Primary Header

EventT - This is the first column in the result shown above

Rest other fields we don't need.

The records in this table look something like this:

Answer 1

If you wanted column name in a format of "Header=Subheader", then below is my stab at it. I've changed the table definition a bit for ease of display while experimenting. Let me know if it helps.

DECLARE @cols AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX)

DROP TABLE IF EXISTS #MyTable

Create table #MyTable
(
    ET varchar(30),
    PID varchar(20),
    SP varchar(30),
    EventT date
)

insert into #MyTable
VALUES
    (2042, 'Ext', 14, '2023-01-01' ),
    (null, 'Lac', 16, '2023-01-01' ),
    (11, 'Moi', 17, '2023-01-01' ),
    (11, 'Moi', 17, '2023-01-05' ),
    (35, 'Moi', 18, '2023-01-05' )

SELECT
    @cols = STUFF((SELECT ',' + QUOTENAME(PID+ '=' + SP)
    FROM #MyTable
    WHERE SP != ''
    GROUP BY PID+ '=' + SP
    ORDER BY PID+ '=' + SP
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')


SET @query = 'SELECT EventT,' + @cols + ' FROM 
                   (SELECT ET, PID + ''='' + SP AS SP, EventT
                    FROM #MyTable 
                    WHERE SP != '''') x
               PIVOT
                   (MAX(ET)
                    FOR SP IN (' + @cols + ')) p '

PRINT @query
EXECUTE(@query);