Display rows vertically

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

  • supplied stored proc doesn't execute successfully.

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

  • 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'.

  • 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?

  • 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

  • Thank-you sir; that worked.

  • 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')?

  • 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.

  • Thanks for the script.

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

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