Display rows vertically

, 2009-10-05 (first published: )

When you have a table with many columns, it can become a chore to remember all of the column names and find what you need. This script displays the selected rows vertically with the columns in alpha order to help locate columns when you don't know the exact names (or if you just want to see all the columns without scrolling horizontally). This uses SQL Server 2005's UNPIVOT command, and so will not work in SQL 2000.

It will not display columns having null values or columns of type text, ntext, image, or timestamp.

 

It takes two parameters, the second of which is optional: tablename (in single-quotes) and an optional Where clause in quotes (including the word WHERE).

 

For example:

vselect 'mytable', 'where id=123'

 

ALTER procedure [dbo].[vselect]
@table_name varchar(max),
@where_clause varchar(max)=''
AS
    -- Display table row(s) vertically, in alphabetic order.
    -- Usage: vselect 'tablename' [,'where ~']
set nocount on

declare @col_names varchar(max)
declare @variants varchar(max)
declare @sql varchar(max)
set @col_names=''
set @variants = ''

select @col_names = @col_names + c.column_name + ',',
    @variants = @variants + 'CONVERT(SQL_VARIANT, '+c.column_name + ') AS '+c.column_name+','
from 
    information_schema.columns c
where table_name = @table_name
 and table_schema = 'dbo'
 and data_type not in ('text','ntext','image','timestamp')
order by c.column_name

set @col_names = left(@col_names,len(@col_names)-1)
set @variants = left(@variants,len(@variants)-1)

set @sql = 
'
SELECT 
 TBLPIVOT.Column_Name,
 TBLPIVOT.Value
FROM 
(SELECT ' + @variants +
' FROM ' + @table_name + ' ' +
@where_clause + ') x UNPIVOT (VALUE FOR Column_Name IN ('
+ @col_names + ')) AS TBLPIVOT'

exec(@sql)

Rate

2.2 (5)

Share

Share

Rate

2.2 (5)

Related content

Stairway to T-SQL DML

Stairway to T-SQL DML Level 1: The Basic SELECT Statement

There are lots of different aspects of managing data in a SQL Server database. Before you can get into the complex management issues associated with managing application data you need to start with the basic of retrieving data from a table. To return data from a SQL Server table you use a SELECT statement. In this level I will be cover the components of the basic SELECT statement and how you can use it to retrieve data from a single SQL Server table.

2011-10-07

21,847 reads