none
Vertical Result Set

    Question

  • hi all...  Is it possible to get results vertically rather than horizontally... I have a table with over 70 colulmns I have to display top 3 records vertically.. I could get columns arranged vertically by using  information_schema.columns

    Any help is greatly appreciated.

    Friday, August 27, 2010 10:13 PM

Answers

  • Yes, you can construct dynamic UNPIVOT differently, like this:

    declare @SQL nvarchar(max)
    
    select @SQL = coalesce(@SQL + '
    FROM AdventureWorks.Person.Address 
     UNION ALL
    ','') + 'SELECT convert(varchar(max),' + quotename(column_name) + ') as Column_Value, ' +
    QUOTENAME(Column_Name,'''') + ' as Column_Name'
     from AdventureWorks.INFORMATION_SCHEMA.COLUMNS 
    where TABLE_NAME = 'Address' and TABLE_SCHEMA = 'Person'
    --print @cols
    
    
    set @SQL =' ;with cte as (' + @SQL + '
    FROM AdventureWorks.Person.Address) 
    
    select * from (select *, 
    row_number() over (partition by Column_Name order by Column_Value) as row 
    from cte) X 
    where Row <=10'
    
    print @SQL
    
    execute(@SQL)
    
    We convert all columns to varchar(max)


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by vishal.... _ Friday, August 27, 2010 11:39 PM
    Friday, August 27, 2010 11:37 PM
    Moderator

All replies

  • If the type of the columns is the same or compatible, then you can UNPIVOT (or dynamically UNPIVOT) your table.

    Here is an example based on AdventureWorks.People.Address table:

    declare @Cols varchar(max)
    
    select @Cols = coalesce(@Cols + ', ','') + quotename(column_name)
     from AdventureWorks.INFORMATION_SCHEMA.COLUMNS 
    where DATA_TYPE like '%char' 
    and COLUMN_NAME like 'Addr%'
    and TABLE_NAME = 'Address'
    --print @cols
    declare @SQL varchar(max)
    
    set @SQL = 'select * from (select * from AdventureWorks.Person.Address) src
    UNPIVOT (Address for AddressType in (' + @Cols + ')) unpvt'
    
    --print @SQL
    
    execute(@SQL)
    

     


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, August 27, 2010 10:25 PM
    Moderator
  • hi naom.. I tried it... but i dont have the same data types.... is there any other way to do it...
    Friday, August 27, 2010 11:15 PM
  • hi naom.. I tried it... but i dont have the same data types.... is there any other way to do it...


    You could cast them all to sql_variant.

    Can you tell us how are you planning to consume this resultset, because this is really a weird request (sorry about that).


    AMB

    Some guidelines for posting questions...

    Friday, August 27, 2010 11:24 PM
    Moderator
  • yes it is... I just want to export the resultset to excel... where i need all the fields and just 3 or 4 sample records from the table...
    Friday, August 27, 2010 11:26 PM
  • Yes, you can construct dynamic UNPIVOT differently, like this:

    declare @SQL nvarchar(max)
    
    select @SQL = coalesce(@SQL + '
    FROM AdventureWorks.Person.Address 
     UNION ALL
    ','') + 'SELECT convert(varchar(max),' + quotename(column_name) + ') as Column_Value, ' +
    QUOTENAME(Column_Name,'''') + ' as Column_Name'
     from AdventureWorks.INFORMATION_SCHEMA.COLUMNS 
    where TABLE_NAME = 'Address' and TABLE_SCHEMA = 'Person'
    --print @cols
    
    
    set @SQL =' ;with cte as (' + @SQL + '
    FROM AdventureWorks.Person.Address) 
    
    select * from (select *, 
    row_number() over (partition by Column_Name order by Column_Value) as row 
    from cte) X 
    where Row <=10'
    
    print @SQL
    
    execute(@SQL)
    
    We convert all columns to varchar(max)


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by vishal.... _ Friday, August 27, 2010 11:39 PM
    Friday, August 27, 2010 11:37 PM
    Moderator
  • yes it is... I just want to export the resultset to excel... where i need all the fields and just 3 or 4 sample records from the table...


    I guess you are also adding the columns that uniquely identify each row, right?

    If not, you will not be able to tell each column and column value, to which row they belong to.

    USE AdventureWorks2008;
    GO
    SELECT
    	C.*
    FROM
    	(
     SELECT TOP (3)
    	 AddressID,
    	 AddressLine1,
    	 AddressLine2,
    	 City,
    	 StateProvinceID,
    	 PostalCode
     FROM
    	 Person.Address
     ORDER BY
      ModifiedDate
     ) AS T
     CROSS APPLY
     (
     VALUES
      (AddressID, 'AddressID', CAST(AddressID AS sql_variant)),
      (AddressID, 'AddressLine1', CAST(AddressLine1 AS sql_variant)),
      (AddressID, 'AddressLine2', CAST(AddressLine2 AS sql_variant)),
      (AddressID, 'City', CAST(City AS sql_variant)),
      (AddressID, 'StateProvinceID', CAST(StateProvinceID AS sql_variant)),
      (AddressID, 'PostalCode', CAST(PostalCode AS sql_variant))
     ) AS C(rowident, cn, cv)
    ORDER BY
     rowident,
     cn;
    GO
    

    To simulate exactly the UNPIVOT operator, filter the resultset by "C.cv IS NOT NULL".


    AMB

    Some guidelines for posting questions...

    Friday, August 27, 2010 11:39 PM
    Moderator
  • Thanks naom.. i'll try it..
    Friday, August 27, 2010 11:39 PM
  • DECLARE

     

    @SQL nvarchar(max)

     

    select

     

    @SQL = coalesce(@SQL + '

    FROM TestOasisVish.dbo.AUDIT_DATE_DIM_Temp

    UNION ALL

    '

     

    ,'') + 'SELECT convert(varchar(max),' + quotename(column_name) + ') as Column_Value, ' +

    QUOTENAME

     

    (Column_Name,'''') + ' as Column_Name'

     

    from TestOasisVish.INFORMATION_SCHEMA.COLUMNS

    where

     

    TABLE_NAME = 'AUDIT_DATE_DIM_Temp' and TABLE_SCHEMA = 'dbo'

    --print @cols

     

    set

     

    @SQL =' ;with cte as (' + @SQL + '

    FROM TestOasisVish.dbo.AUDIT_DATE_DIM_Temp)

    select * from (select *,

    row_number() over (partition by Column_Name order by Column_Value) as row

    from cte) X

    where Row <=10'

    print

     

    @SQL

    execute

     

    (@SQL)

    Msg 170, Level 15, State 1, Line 1

    Line 1: Incorrect syntax near 'max'.

    Msg 137, Level 15, State 2, Line 3

    Must declare the variable '@SQL'.

    Msg 137, Level 15, State 2, Line 13

    Must declare the variable '@SQL'.

    Msg 137, Level 15, State 2, Line 21

    Must declare the variable '@SQL'.

    Msg 137, Level 15, State 2, Line 23

    Must declare the variable '@SQL'.

     

    Can you plz correct it...

    Saturday, August 28, 2010 12:51 AM
  • It looks like you're using SQL Server 2000, so varchar(max) and row_number() and cte will not work for you. It makes the query a bit more complex.

    Find out first what is the version of your SQL Server.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Sunday, August 29, 2010 2:35 AM
    Moderator