Display rows vertically

  • Steve McRoberts-357330

    Old Hand

    Points: 349

    Comments posted to this topic are about the item Display rows vertically[/url]

    12/22/2009: Revised code to use nvarchar(max) instead of SQL Variant. This allow for the data-types that were previously excluded, and fixes a bug when the table contains an nvarchar(max) column [which cannot be converted to SQL Variant].

    Here is the revised code (please use it instead of any other version):

    CREATE 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(nvarchar(max), '+c.column_name + ') AS '+c.column_name+','

    from

    information_schema.columns c

    where table_name = @table_name

    and table_schema = 'dbo'

    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)

  • ziangij

    SSCertifiable

    Points: 6954

    supplied stored proc doesn't execute successfully.

  • Steve McRoberts-357330

    Old Hand

    Points: 349

    Please use the code at the top of this discussion page instead. That will fix the problem.

  • Bruc0Blachf0rd-780736

    Grasshopper

    Points: 24

    When I run it vselect 'dbo.OracleStaging'

    Get following errors

    Msg 537, Level 16, State 5, Procedure vselect, Line 24

    Invalid length parameter passed to the LEFT or SUBSTRING function.

    Msg 537, Level 16, State 5, Procedure vselect, Line 25

    Invalid length parameter passed to the LEFT or SUBSTRING function.

    Msg 156, Level 15, State 1, Line 6

    Incorrect syntax near the keyword 'FROM'.

  • Ken Wymore

    SSCoach

    Points: 16385

    Is there a DB setting that we need to set in order to execute this code? I cannot get it to run even if I remove the CREATE PROCEDURE statement and declare all of the parameters separately. Maybe there is a non-standard setting applied to his database?

  • William Soranno

    SSCommitted

    Points: 1578

    Bruc0Blachf0rd-780736 ,

    Do not pass dbo. as part of the table name.

    Bill Soranno
    MCP, MCTS, MCITP DBA
    Database Administrator
    Winona State University
    Maxwell 143

    "Quality, like Success, is a Journey, not a Destination" - William Soranno '92

  • Bruc0Blachf0rd-780736

    Grasshopper

    Points: 24

    Thank-you sir; that worked.

  • Steve McRoberts-357330

    Old Hand

    Points: 349

    Is there a DB setting that we need to set in order to execute this code? I cannot get it to run even if I remove the CREATE PROCEDURE statement and declare all of the parameters separately. Maybe there is a non-standard setting applied to his database?

    I don't think you need any non-standard setting. Did you try copying the code from my forum posting above? Are you running on SS2005? Did you try it without a database prefix on the table-name (e.g. 'mytable' instead of 'dbo.mytable')?

  • starunit

    SSCommitted

    Points: 1807

    Hey Steve;

    Thanks for the code. I tweaked it a little for my situation, added parameter markers, and stuck in my templates...

    Pretty handy!

    USE <databasename, sysname, >;

    SET NOCOUNT ON

    DECLARE

    @table_name sysname

    , @where_clause varchar(MAX)

    , @col_names varchar(MAX)

    , @variants varchar(MAX)

    , @sql varchar(MAX), @DELIM varchar(1)

    SELECT @table_name = '<Table Name, sysname, >'

    , @where_Clause = '<Where Clause, varchar(max), WHERE F = V>'

    , @col_names='', @variants = '', @DELIM = '';

    SELECT

    @col_names = @col_names + @DELIM +c.column_name,

    @variants = @variants + @DELIM + 'CONVERT(SQL_VARIANT, '+c.column_name + ') AS '+c.column_name,

    @DELIM = ','

    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.ordinal_position;

    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);

    Cheers,

    Mark
    Just a cog in the wheel.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply