SQL Unpivot With Every Column

Question

I have the below query which works well for the 4 columns chosen, but the issue is that I need to select every column of the table (there is about 50 columns). Is there an easier way to do this instead without including each of the 50 columns in the SELECT and IN statements. I also realize that there could be a data type issue. There is only 1 row of data returned.

SELECT g.property, 
   g.value 
FROM   (SELECT applicationversion, 
           ftpservername, 
           smtpservername, 
           Cast(numberofservers AS NVARCHAR(max)) AS numberofservers 
    FROM   globals) Person 
   UNPIVOT (value 
           FOR property IN (applicationversion, 
                            ftpservername, 
                            smtpservername, 
                            numberofservers)) AS g; 

Answer 1

Please try this:

DECLARE @sql AS NVARCHAR(MAX)
DECLARE @cols1 AS NVARCHAR(MAX)
DECLARE @cols2 AS NVARCHAR(MAX)

SELECT @cols1= ISNULL(@cols1 + ',','') + QUOTENAME(name)
FROM (select c.name from sys.tables t
inner join sys.columns c on c.object_id = t.object_id
where t.name = 'globals'
 ) cols1

SELECT @cols2= ISNULL(@cols2 + ',cast(','cast(') + QUOTENAME(name) + ' as nvarchar(max))'+ QUOTENAME(name) 
FROM (select c.name from sys.tables t
inner join sys.columns c on c.object_id = t.object_id
where t.name = 'globals'
 ) cols2


SET @sql =
  N'SELECT g.property, g.value  
FROM   (SELECT ' + @cols2 + '  
    FROM  globals) Person 
   UNPIVOT (value 
           FOR property IN (' + @cols1 +')) AS g; '

EXEC sp_executesql @sql

It's not beautiful and can certainly be improved, but it should work.