Click here to monitor SSC
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
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 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
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2986 Visits: 376
supplied stored proc doesn't execute successfully.
Steve McRoberts-357330
Steve McRoberts-357330
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 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
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4642 Visits: 2356
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 (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)

Group: General Forum Members
Points: 110 Visits: 514
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
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 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
SSC Veteran
SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)SSC Veteran (270 reputation)

Group: General Forum Members
Points: 270 Visits: 2568
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
SSCrazy Eights
SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)

Group: General Forum Members
Points: 9972 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