Post reply

Display rows vertically

  • October 1, 2009 at 9:15 am

    #214938

    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)

  • October 4, 2009 at 11:09 pm

    #1061543

    supplied stored proc doesn't execute successfully.

  • October 5, 2009 at 3:47 am

    #1061608

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

  • October 5, 2009 at 6:31 am

    #1061668

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

  • October 5, 2009 at 7:09 am

    #1061695

    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?

  • October 5, 2009 at 7:10 am

    #1061696

    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

  • October 5, 2009 at 7:32 am

    #1061707

    Thank-you sir; that worked.

  • October 5, 2009 at 9:48 am

    #1061840

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

  • October 9, 2009 at 2:15 pm

    #1064347

    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.

  • May 23, 2016 at 4:52 pm

    #1880918

    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