Display Rows Vertically or Alphabetically

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AzDisplay]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[AzDisplay]
go

create procedure [dbo].[AzDisplay]
@TableName varchar(128) = null,
@WhereClause1 varchar(1000) = null,
@WhereClause2 varchar(1000) = null
as
-- written by Bill Talada

set nocount on

if @TableName is null
begin
print 'Samples on selecting all columns alphabetically (one row):'
print '  exec AzDisplay Customer,''CustomerKey = 123'''
return 0
end

declare
@col sysname,
@list varchar(4000),
@sql varchar(8000),
@crlf varchar(2),
@tab varchar(1)

set @crlf=char(13)+char(10)
set @tab=char(9)

set @col=''
set @list=''

create table #colvals( col varchar(128), val varchar(max))
create table #colvals2( col varchar(128), val varchar(max))

select
@col = min(c.name)
from
sys.columns c
join
sys.tables t
on c.object_id=t.object_id
where
t.name = @TableName

while @col is not null
begin
set @sql = 'insert into #colvals select '''+@col+''',cast(' + @col + ' as varchar(max)) from '+@tablename+ ' where '+@whereclause1
exec( @sql)

select
@col = min(c.name)
from
sys.columns c
join
sys.tables t
on c.object_id=t.object_id
where
t.name = @TableName
and
c.name > @col
end

----------------
if @whereclause2 is not null
begin
select
@col = min(c.name)
from
sys.columns c
join
sys.tables t
on c.object_id=t.object_id
where
t.name = @TableName

while @col is not null
begin
set @sql = 'insert into #colvals2 select '''+@col+''',cast(' + @col + ' as varchar(max)) from '+@tablename+ ' where '+@whereclause2
exec( @sql)

select
@col = min(c.name)
from
sys.columns c
join
sys.tables t
on c.object_id=t.object_id
where
t.name = @TableName
and
c.name > @col
end
end
----------------

if @whereclause2 is null
begin
select
col,
isnull(val,'<null>') as val 
from 
#colvals
end
else
begin
select
c1.col,
isnull(c1.val,'<null>') as val1,
isnull(c2.val,'<null>') as val2
from 
#colvals c1
join
#colvals2 c2 on c1.col = c2.col
order by
c1.col
end

drop table #colvals
drop table #colvals2

return 0
go

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AzSelect]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[AzSelect]
go

create procedure [dbo].[AzSelect]
@TableName varchar(128) = null,
@WhereClause varchar(1000) = null
as
-- written by Bill Talada

if @TableName is null
begin
print 'Samples on selecting all columns alphabetically:'
print '  exec AzSelect Accessions,''1=1'''
return 0
end

declare
@col sysname,
@list varchar(max),
@sql varchar(max),
@crlf varchar(2),
@tab varchar(1)

set @crlf=char(13)+char(10)
set @tab=char(9)

set @col=''
set @list=''

select
@col = min(c.name)
from
sys.columns c
join
sys.tables t
on c.object_id=t.object_id
where
t.name = @TableName

while @col is not null
begin
if datalength(@list) > 1 set @list = @list + ',' + @crlf

set @list = @list + @tab + @col
--print @list

select
@col = min(c.name)
from
sys.columns c
join
sys.tables t
on c.object_id=t.object_id
where
t.name = @TableName
and
c.name > @col
end

set @sql = 'select'
+@crlf+@list
+@crlf+'from'+@crlf+@tab+@TableName
+@crlf+'where'+@crlf+@tab+@WhereClause

print @sql
exec (@sql)

return 0
go