Covert columns to rows

Question

Post reply

Covert columns to rows

sqlbaby2

Ten Centuries

Points: 1351
More actions
December 28, 2015 at 8:13 pm

#303526

Hi guys, may i know how can i output result from this format to this format ? query is
Select * from sys.databases where name = 'your_db_name'
Original
------------
name databaseid source_database_id owner_sid ...............
xxx xxxx xxxx xxxx
To
------------
Parameter Value
name xxxx
databaseid xxxx
source_database_id xxxx
owner_sid xxxx

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 1

Quick solution

😎

USE tempdb;

GO

SET NOCOUNT ON;

SELECT

X.[DATABASE]

,X.[PARAMETER]

,X.[VALUE]

FROM sys.databases SDB

CROSS APPLY

(

SELECT SDB.name,N'name' ,CONVERT(NVARCHAR(250),SDB.name ) UNION ALL

SELECT SDB.name,N'database_id' ,CONVERT(NVARCHAR(250),SDB.database_id ) UNION ALL

SELECT SDB.name,N'source_database_id' ,CONVERT(NVARCHAR(250),SDB.source_database_id ) UNION ALL

SELECT SDB.name,N'owner_sid' ,CONVERT(NVARCHAR(250),SDB.owner_sid ) UNION ALL

SELECT SDB.name,N'create_date' ,CONVERT(NVARCHAR(250),SDB.create_date ) UNION ALL

SELECT SDB.name,N'compatibility_level' ,CONVERT(NVARCHAR(250),SDB.compatibility_level ) UNION ALL

SELECT SDB.name,N'collation_name' ,CONVERT(NVARCHAR(250),SDB.collation_name ) UNION ALL

SELECT SDB.name,N'user_access' ,CONVERT(NVARCHAR(250),SDB.user_access )

) AS X([DATABASE],[PARAMETER],[VALUE])

;

Sample output (masked)

DATABASE PARAMETER VALUE

---------- ------------------- --------

master name mxxxxxx

master database_id 1xxxxxx

master source_database_id Nxxxxxx

master owner_sid xxxxxx

master create_date Axxxxxx

master compatibility_level 1xxxxxx

master collation_name Lxxxxxx

master user_access 0xxxxxx

tempdb name txxxxxx

tempdb database_id 2xxxxxx

tempdb source_database_id Nxxxxxx

tempdb owner_sid xxxxxx

tempdb create_date Dxxxxxx

tempdb compatibility_level 1xxxxxx

tempdb collation_name Lxxxxxx

tempdb user_access 0xxxxxx

model name mxxxxxx

model database_id 3xxxxxx

model source_database_id Nxxxxxx

model owner_sid xxxxxx

model create_date Axxxxxx

model compatibility_level 1xxxxxx

model collation_name Lxxxxxx

model user_access 0xxxxxx

sqlbaby2 Ten Centuries Points: 1351 More actions · Answer 2

sqlbaby2

Ten Centuries

Points: 1351

December 29, 2015 at 1:51 am

#1848407

Thanks !!!!!!

kaza.rohan Grasshopper Points: 23 More actions · Answer 3

Can we write dynamically for sys.databases , is there any possibility ?

But for other tables it is possible by using sys.columns and then we can unpivot it.

but sys.databases is a system database

Thanks,

Rohan k.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

kaza.rohan (12/30/2015)
Can we write dynamically for sys.databases , is there any possibility ?
But for other tables it is possible by using sys.columns and then we can unpivot it.
but sys.databases is a system database
Thanks,
Rohan k.

What's the problem?

SELECT c.name

FROM master.sys.all_columns c

JOIN master.sys.all_objects o ON c.object_id = o.object_id

JOIN master.sys.schemas s ON o.schema_id = s.schema_id

WHERE o.name = 'databases'

AND s.name = 'sys'

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 5

kaza.rohan (12/30/2015)
Can we write dynamically for sys.databases , is there any possibility ?
But for other tables it is possible by using sys.columns and then we can unpivot it.
but sys.databases is a system database
Thanks,
Rohan k.

sys.databases is a system view, not a system "database". If you can SELECT from it, it can be used in any SELECT, dynamic or not.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kaza.rohan Grasshopper Points: 23 More actions · Answer 6

Thanks Luis , for clarifying .. 🙂

But when i was executing the query its showing error ,

ERROR : The "The type of column "database_id" conflicts with the type of other columns specified in the UNPIVOT list."

Later when i analysed , the unpivot query cannot handle more than 2 columns , if we keep more than two there will be conflicts .

DECLARE @USEPIVOT AS NVARCHAR(MAX),

@EXECUTE AS NVARCHAR(MAX)

SELECT @USEPIVOT

= stuff((select ','+quotename(C.name)

FROM master.sys.all_columns c

JOIN master.sys.all_objects o ON c.object_id = o.object_id

JOIN master.sys.schemas s ON o.schema_id = s.schema_id

WHERE o.name = 'databases'

AND s.name = 'sys'

for xml path('')), 1, 1, '')

set @EXECUTE

= 'select PARAMETER, VALUE

from SYS.DATABASES

unpivot

(

VALUE

for PARAMETER in ('+ @USEPIVOT +')

) u'

exec sp_executesql @EXECUTE;

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 7

Another quick solution, details in the comment

😎

CREATE PROCEDURE dbo.USP_LIST_TABLE_AS_EAV

(

@DB_NAME SYSNAME = N''

,@SCHEMA_NAME SYSNAME = N''

,@TABLE_NAME SYSNAME = N''

)

AS

SET NOCOUNT ON;

/*

OBJECT: dbo.USP_LIST_TABLE_AS_EAV

DESCRIPTION: Unpivot table content into EAV style list

AUTHOR: Eirikur Eiriksson

DATE: 2015-12-31

VERSION: 0.0.01

PREREQUISITE: The object must reside in the target database

USAGE:

DECLARE @DB_NAME SYSNAME = N'master';

DECLARE @SCHEMA_NAME SYSNAME = N'sys';

DECLARE @TABLE_NAME SYSNAME = N'databases';

EXEC dbo.USP_LIST_TABLE_AS_EAV @DB_NAME,@SCHEMA_NAME , @TABLE_NAME

*/

DECLARE @EXEC_STR NVARCHAR(MAX) = N'

USE {{@DB_NAME}}

SET NOCOUNT ON;

SELECT

QUOTENAME(DB_NAME()) AS [DATABASE]

,''{{@SCHEMA_NAME}}'' AS [SCHEMA]

,''{{@TABLE_NAME}}'' AS

,X.[PARAMETER]

,X.[VALUE] COLLATE Latin1_General_CI_AS

FROM {{@SCHEMA_NAME}}.{{@TABLE_NAME}} SDB

CROSS APPLY

(

{{@COL_LIST}}

) AS X([PARAMETER],[VALUE])

;';

SELECT @EXEC_STR = REPLACE(REPLACE(REPLACE(REPLACE(@EXEC_STR,N'{{@COL_LIST}}',

(

SELECT

CASE WHEN COUNT(SAC.name) OVER (PARTITION BY (SELECT NULL)) <> ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) THEN

N'SELECT ' + NCHAR(39) + SAC.name + NCHAR(39) + N',CONVERT(NVARCHAR(250),' + QUOTENAME(SAC.name) +N') UNION ALL '

ELSE N'SELECT ' + NCHAR(39) + SAC.name + NCHAR(39) + N',CONVERT(NVARCHAR(250),' + QUOTENAME(SAC.name) +N') '

END

FROM sys.all_objects SAO

INNER JOIN sys.schemas SSC

ON SAO.schema_id = SSC.schema_id

INNER JOIN sys.all_columns SAC

ON SAO.object_id = SAC.object_id

WHERE SAO.name = @TABLE_NAME

AND SSC.name = @SCHEMA_NAME

FOR XML PATH(''), TYPE

).value('.[1]','NVARCHAR(MAX)')),N'{{@DB_NAME}}',QUOTENAME(@DB_NAME)),N'{{@SCHEMA_NAME}}',QUOTENAME(@SCHEMA_NAME)),N'{{@TABLE_NAME}}',QUOTENAME(@TABLE_NAME));

EXEC ( @EXEC_STR );

kaza.rohan Grasshopper Points: 23 More actions · Answer 8

kaza.rohan

Grasshopper

Points: 23

December 31, 2015 at 4:19 am

#1848906

Thanks Eirikur Eiriksson , it helped a lot .. 🙂