SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Display rows vertically


Display rows vertically

Author
Message
Steve McRoberts-357330
Steve McRoberts-357330
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 82
Comments posted to this topic are about the item Display rows vertically

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
ziangij
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3150 Visits: 376
supplied stored proc doesn't execute successfully.
Steve McRoberts-357330
Steve McRoberts-357330
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 82
Please use the code at the top of this discussion page instead. That will fix the problem.
Bruc0Blachf0rd-780736
Bruc0Blachf0rd-780736
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 27
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
Ken Wymore
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5109 Visits: 2377
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
William Soranno
SSC-Enthusiastic
SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)

Group: General Forum Members
Points: 182 Visits: 519
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
Bruc0Blachf0rd-780736
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 27
Thank-you sir; that worked.
Steve McRoberts-357330
Steve McRoberts-357330
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 82
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')?
Mark Starr
Mark Starr
Old Hand
Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)

Group: General Forum Members
Points: 330 Visits: 2569
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.
Iwas Bornready
Iwas Bornready
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14114 Visits: 885
Thanks for the script.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search