CODEWITHSUNDEEP
sqlsql-server
user618520
user618520

Reputation: 139

Get header as the first row in dynamic SQL

My table:

id    name    city
--------------------
1     Jim     NewYork
2     Rose    London
3     Kitty   Seattle
......

The output:

id    name    city
--------------------
id    name    city
1     Jim     NewYork
2     Rose    London
3     Kitty   Seattle

I can easily do it in static SQL like this, but how to do it in dynamic SQL?

Declare @sql as varchar(max)='select *';
set @sql =@sql+' from mytable';

exec(@sql)

Upvotes: 0

Views: 4725

Answers (2)

an33sh
an33sh

Reputation: 1124

I don't know if I am overdoing this. But if you want a pure dynamic way, you can try something like 

declare @sql varchar(max),
    @colnameHeads varchar(max), 
    @colnames varchar(max);

-- building the row with column headers
set @colnameHeads   = STUFF((
SELECT ',''' + COLUMN_NAME + ''''
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'<your-table>'
FOR XML PATH('')
), 1, 1, '')

-- casting all the values to varchar to match with header row
set @colnames   = STUFF((
SELECT ',' +  CONCAT('CAST(', COLUMN_NAME, ' AS VARCHAR(100))')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'<your-table>'
FOR XML PATH('')
), 1, 1, '')

-- dynamic script
set  @sql  = 'select '+ @colnameHeads  + ' union all select '+ @colnames + ' from <your-table>';

EXEC (@sql)

Upvotes: 0

PSK
PSK

Reputation: 17943

I am not sure why you want to do that as your id looks int to me. If you want one extra string type value to the column to appear on the top, in that case you have to convert all the int to string.

Still if you want to do that you can write your query like following.

SELECT *
FROM (
    SELECT cast(id AS VARCHAR(10)) AS id
        ,Name
        ,city
    FROM mytable

    UNION ALL

    SELECT 'id'
        ,'name'
        ,'city'
    ) t
ORDER BY CASE 
        WHEN t.id = 'id'
            THEN 0
        ELSE 1
        END

Same query can be written as dynamic query like following.

DECLARE @sql AS VARCHAR(max) = 'select * from (
                select cast(id as varchar(10)) as id,Name,city';
SET @sql = @sql + ' from mytable union select ''id'',''name'', ''city'')t 
                      order by case when t.id=''id'' then 0 else 1 end';

EXEC (@sql)

Upvotes: 1

Related Questions