We help IT Professionals succeed at work.

select header from view in sql

VBdotnet2005
VBdotnet2005 asked
on
Is there a way to get column headers from a view?

select top (1) * from vw_customers_order

id      1
name    John
address  123 Beach st.
etc
Comment
Watch Question

SimonPrincipal Analyst

Commented:
select * from vw_customers_order where 1=2

Will get you just the headers without any data.
Senior Developer
Commented:
hmm, To get the headers you can use SET FMTONLY ON, e.g.

USE AdventureWorks2012;
GO

SET FMTONLY ON;

SELECT  *
FROM    HumanResources.vEmployee VE;

SET FMTONLY OFF;
GO

Open in new window


But your sample looks like you want to unpivot your view, e.g.

USE AdventureWorks2012;
GO

WITH    Casted ( BusinessEntityID, Title, FirstName, MiddleName, LastName, Suffix, JobTitle, PhoneNumber, PhoneNumberType, EmailAddress, EmailPromotion, AddressLine1, AddressLine2, City, StateProvinceName, PostalCode, CountryRegionName )
          AS ( SELECT   VE.BusinessEntityID ,
                        CAST(VE.Title AS NVARCHAR(255)) ,
                        CAST(VE.FirstName AS NVARCHAR(255)) ,
                        CAST(VE.MiddleName AS NVARCHAR(255)) ,
                        CAST(VE.LastName AS NVARCHAR(255)) ,
                        CAST(VE.Suffix AS NVARCHAR(255)) ,
                        CAST(VE.JobTitle AS NVARCHAR(255)) ,
                        CAST(VE.PhoneNumber AS NVARCHAR(255)) ,
                        CAST(VE.PhoneNumberType AS NVARCHAR(255)) ,
                        CAST(VE.EmailAddress AS NVARCHAR(255)) ,
                        CAST(VE.EmailPromotion AS NVARCHAR(255)) ,
                        CAST(VE.AddressLine1 AS NVARCHAR(255)) ,
                        CAST(VE.AddressLine2 AS NVARCHAR(255)) ,
                        CAST(VE.City AS NVARCHAR(255)) ,
                        CAST(VE.StateProvinceName AS NVARCHAR(255)) ,
                        CAST(VE.PostalCode AS NVARCHAR(255)) ,
                        CAST(VE.CountryRegionName AS NVARCHAR(255))
               FROM     HumanResources.vEmployee VE
             )
    SELECT  *
    FROM    Casted C UNPIVOT ( ColumnValue FOR ColumnName IN ( Title, FirstName, MiddleName, LastName, Suffix, JobTitle, PhoneNumber, PhoneNumberType,
                                                               EmailAddress, EmailPromotion, AddressLine1, AddressLine2, City, StateProvinceName, PostalCode,
                                                               CountryRegionName ) ) U;
GO

Open in new window

Commented:
SET NOCOUNT ON
DECLARE @TABLE_NAME nvarchar(200)
SET @TABLE_NAME = 'VendorsView'
DECLARE @COLUMN_NAME nvarchar(200)
DECLARE @SQL as nvarchar(MAX)
SET @SQL = ''
DECLARE myCursor cursor
   FOR SELECT C.COLUMN_NAME
         FROM INFORMATION_SCHEMA.COLUMNS C
        WHERE C.TABLE_NAME = @TABLE_NAME
        ORDER BY C.ORDINAL_POSITION

       
OPEN myCursor
FETCH NEXT FROM myCursor INTO @COLUMN_NAME
SET @SQL = 'SELECT '
WHILE @@FETCH_STATUS = 0 BEGIN
    SET @SQL = @SQL + '''' + @COLUMN_NAME + ': '' + CONVERT(nvarchar(500),' + QUOTENAME(@COLUMN_NAME) + '),'
    FETCH NEXT FROM myCursor INTO @COLUMN_NAME
END

CLOSE myCursor
DEALLOCATE myCursor
SET @SQL = LEFT(@SQL, LEN(@SQL) -1) + ' FROM ' + QUOTENAME(@TABLE_NAME)
EXEC (@SQL)
PRINT @SQL
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
I would suggest doing that type of pivoting in "the presentation layer" rather than SQL

To me this is presentation:

id             1                         2
name       John                  Jill
address  123 Beach st.  Ocean Grove.
you might want multiple persons on the same screen in this layout, and/or pagination through a set of records which will need to be in a predetermined order. SQL can furnish the ordered rows of data and your ASP code can do the 'flip' presentation (I am assuming ASP topic is relevant)
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
VBdotnet2005, do you still need help with this question?