(DIR) Return Create A Forum - Home
       ---------------------------------------------------------
       ExcelSoft Database Professionals
 (HTM) https://esdbp.createaforum.com
       ---------------------------------------------------------
       *****************************************************
 (DIR) Return to: Scripts
       *****************************************************
       #Post#: 30--------------------------------------------------
       To list columns with specified delimiter horizontally /verticall
       y 
       By: srinivasma_exceldbp Date: March 21, 2014, 4:52 am
       ---------------------------------------------------------
       -- To list columns with specified delimiter horizontally
       /vertically
       --  Procedure to list columns with specified delimiter
       horizontally (one row ) or vertically ( columns)
       -- Select Query -> Results to -> Text
       /*
       EXEC sp_lib_columns 'employee',',','H'
       EXEC sp_lib_columns 'employee',',','V'
       */
       CREATE procedure dbo.sp_lib_columns
       @vchtablename varchar(200),
       @chrdelimiter char(1) = ',',
       @hvflag char(1) = 'H/V' -- Horizontal/Vertical
       as
       set nocount on
       if @vchtablename is null or len(@vchtablename)= 0
       return
       if @chrdelimiter is null
       return
       set nocount on
       declare @vchcolumns varchar(8000),@intmaxslno int
       declare @tblcolumn table (Column_Name varchar(200),slno int
       identity(1,1))
       set @vchcolumns='('
       insert into @tblcolumn(Column_Name)
       select column_name
       from information_schema.columns where table_name=@vchtablename
       order by Ordinal_position
       select @intmaxslno=@@rowcount
       if @hvflag = 'H'
       begin
       select @vchcolumns=@vchcolumns+column_name+case when
       slno<>@intmaxslno then   @chrdelimiter else '' end
       from @tblcolumn
       order by slno
       select @vchcolumns + ')'
       end
       else
       begin
       select @vchcolumns = @vchcolumns + char(10)
       select @vchcolumns = @vchcolumns + column_name + case when
       slno<>@intmaxslno then @chrdelimiter else '' end + char(10)
       from @tblcolumn order by slno
       select @vchcolumns + ')' + char(10)
       end
       GO
       *****************************************************