How to add top 1 row as header on SQL server as extra row?

Transact-SQL
, ,
1

I work on SQL server 2014 I face issue

but I don't know how to solve it

I need to add columns header as first row on table

I Try as below but i get error

Msg 213, Level 16, State 1, Line 1

Column name or number of supplied values does not match table definition.

what i have tried

expected result as below :slight_smile:

PartID Code CodeTypeID RevisionID ZPLID ConCount FeatureName FeatureValue
PartID Code CodeTypeID RevisionID ZPLID ConCount FeatureName FeatureValue
2020 ab5060 877491 26553312 4125 10 Heat IC

what i have tried :slight_smile:

CREATE TABLE #AllData
(

PartID INT,
Code VARCHAR(20),
CodeTypeID INT,
RevisionID BIGINT,
ZPLID INT,
ConCount INT,
FeatureName nvarchar(500),
FeatureValue  nvarchar(500)

)
insert into #AllData VALUES(2020,'ab5060',877491,26553312,4125,10,'Heat','IC')
insert into #AllData
SELECT STUFF((
            SELECT ',' + CAST(name AS VARCHAR(50))
            FROM (
                SELECT name
                FROM tempdb.sys.columns
WHERE [object_id] = OBJECT_ID(N'tempdb..#AllData')

                ) k
            FOR XML PATH('')
            ), 1, 1, '')

SELECT * FROM #AllData
2

information schema.columns
column_name

3

You should revisit and review the business case for doing this. It is fraught with problems - for example, you will need to convert the table so all the columns are character columns - varchar, char etc. That is a very bad idea and goes against the very essence of good database design.

3 Likes
4

"Best Advice" and "Best Practices" are sometimes at extreme odds of the needs of "Reality".

If you could explain why such a need exists and what the "Reality"and ultimate use/purpose/reason for the need is, perhaps we could show you a way to do this without totally destroying the inherent functionality of a given table. In other words, provide you a solution with the best of both worlds.

1 Like
5

@ahmedbarbary

How 'bout it? Why are you so resistant to just explaining WHY you want or need to do this? IMHO, there's a really simple method for doing this but I'd like to know the reason first because you'll never tell us if you have the solution first.

1 Like
6

Search @ahmedbarbary in this forum and subject add top row.

Op is only interested in answers not explaining requirements

1 Like
7

thanks for support it solved

drop table if exists #AllData
    
 CREATE TABLE #AllData
 (
     PartID INT,
     Code VARCHAR(20),
     CodeTypeID INT,
     RevisionID BIGINT,
     ZPLID INT,
     ConCount INT,
     FeatureName nvarchar(500),
     FeatureValue  nvarchar(500)
 )
    
 insert into #AllData VALUES(2020,'ab5060',877491,26553312,4125,10,'Heat','IC')
    
 SELECT * FROM #AllData
    
 declare @s varchar(max) = ''
    
 set @s =
     concat( 
     'select ',
         STUFF((
             SELECT ',' + quotename(name)
                 FROM tempdb.sys.columns
                 WHERE [object_id] = OBJECT_ID(N'tempdb..#AllData')
             FOR XML PATH('')
             ), 1, 1, ''),
     ' from ( select ',
         STUFF((
             SELECT ',' + cast(quotename(name, '''') AS VARCHAR(50)) + ' as ' + quotename(name)
                 FROM tempdb.sys.columns
                 WHERE [object_id] = OBJECT_ID(N'tempdb..#AllData')
             FOR XML PATH('')
             ), 1, 1, ''),
         ', g=0 union all select ',
         STUFF((
             SELECT ',cast(' + quotename(name) + ' AS VARCHAR(50))'
                 FROM tempdb.sys.columns
                 WHERE [object_id] = OBJECT_ID(N'tempdb..#AllData')
             FOR XML PATH('')
             ), 1, 1, ''),
         ', g=1 from tempdb..#AllData) t order by g' )
    
 print @s
    
 exec (@s)
8

I'm glad you got what you wanted BUT your solution isn't what you asked for. You meant "first row on a RESULT SET" and that's a whole lot different.

NOR will it work the way you think it's going to work. You have a couple of serious issues with your code. Yeah... it works with the data you posted. But it won't work with all the data possible according to your table definition. For example, you have these two columns...

 FeatureName nvarchar(500),
 FeatureValue  nvarchar(500)

What do you suppose is going to happen to those since your code converts them to VARCHAR(50)?

You still haven't explained why you need to do this. It would be much appreciated if you did. Thanks.

p.s. I can think of only one reason to do this. Hopefully, we'll find out if I was right.