Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Display rows vertically Expand / Collapse
Author
Message
Posted Thursday, October 1, 2009 9:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 29, 2010 10:43 AM
Points: 9, 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)



Post #796413
Posted Sunday, October 4, 2009 11:09 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, June 30, 2014 2:51 AM
Points: 1,999, Visits: 369
supplied stored proc doesn't execute successfully.
Post #797651
Posted Monday, October 5, 2009 3:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 29, 2010 10:43 AM
Points: 9, Visits: 82
Please use the code at the top of this discussion page instead. That will fix the problem.
Post #797738
Posted Monday, October 5, 2009 6:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 21, 2014 10:54 AM
Points: 2, Visits: 21
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'.
Post #797820
Posted Monday, October 5, 2009 7:09 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 4:24 PM
Points: 3,289, Visits: 1,969
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?
Post #797856
Posted Monday, October 5, 2009 7:10 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 14, 2014 12:51 PM
Points: 76, Visits: 423
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
Post #797858
Posted Monday, October 5, 2009 7:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 21, 2014 10:54 AM
Points: 2, Visits: 21
Thank-you sir; that worked.
Post #797873
Posted Monday, October 5, 2009 9:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 29, 2010 10:43 AM
Points: 9, 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')?
Post #798015
Posted Friday, October 9, 2009 2:15 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 28, 2014 11:08 AM
Points: 120, Visits: 2,442
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.
Post #801081
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse