Need help with TSQL. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Need help with TSQL.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2015-02-18 : 12:17:50

I need to run query and output onto Excel and the User(s) the format like below.
How can I return distinct server name and if the user in Server role, just
display once only. Please see desire output below. SQL 2012.
I know I can do this can be done SSRS but this report run only twice a year.

Thank you so much in advance.

USE [Tempdb]
GO

DROP TABLE [dbo].[T1];
CREATE TABLE [dbo].[T1]
(
[servername] [varchar](18) NULL,
[loginname] [varchar](15) NULL,
[dbname] [varchar](10) NULL,
[dbrole] [varchar](20) NULL,
[serverrole] [varchar](20) NULL,
[typedesc] [varchar](15) NULL
);
GO

INSERT INTO dbo.T1([servername],[loginname],[dbname],[dbrole],[serverrole],[typedesc])
VALUES ('CA12345NB', 'John', 'Dev', 'db_owner', NULL, 'SQL_USER' ) ;

INSERT INTO dbo.T1([servername],[loginname],[dbname],[dbrole],[serverrole],[typedesc])
VALUES ('CA12345NB', 'PDC\languyen', 'Dev', 'db_datareader', 'sysadmin', 'WINDOWS_USER' ) ;

INSERT INTO dbo.T1([servername],[loginname],[dbname],[dbrole],[serverrole],[typedesc])
VALUES ('CA12345NB', 'John', 'Dev', 'db_datareader', NULL, 'SQL_USER' ) ;

INSERT INTO dbo.T1([servername],[loginname],[dbname],[dbrole],[serverrole],[typedesc])
VALUES ('CA12345NB', 'PDC\languyen', 'Dev', 'db_datawriter', 'setupadmin', 'WINDOWS_USER' ) ;

INSERT INTO dbo.T1([servername],[loginname],[dbname],[dbrole],[serverrole],[typedesc])
VALUES ('CA12345NB', 'John', 'Dev', 'db_datawriter', NULL, 'SQL_USER' ) ;

INSERT INTO dbo.T1([servername],[loginname],[dbname],[dbrole],[serverrole],[typedesc])
VALUES ('CA12345NB', 'John', 'BC', 'db_owner', NULL, 'SQL_USER' ) ;

INSERT INTO dbo.T1([servername],[loginname],[dbname],[dbrole],[serverrole],[typedesc])
VALUES ('CA12345NB', 'PDC\LaNguyen', 'BC', 'db_datareader', 'sysadmin', 'WINDOWS_USER' ) ;

INSERT INTO dbo.T1([servername],[loginname],[dbname],[dbrole],[serverrole],[typedesc])
VALUES ('CA12345NB', 'John', 'BC', 'db_datareader', NULL, 'SQL_USER' ) ;

INSERT INTO dbo.T1([servername],[loginname],[dbname],[dbrole],[serverrole],[typedesc])
VALUES ('CA12345NB', 'John', 'BC', 'db_datawriter', NULL, 'SQL_USER' ) ;

INSERT INTO dbo.T1([servername],[loginname],[dbname],[dbrole],[serverrole],[typedesc])
VALUES ('CA9584617MA', 'John', 'Dev', 'db_datareader', NULL, 'SQL_USER' ) ;
INSERT INTO dbo.T1([servername],[loginname],[dbname],[dbrole],[serverrole],[typedesc])
VALUES ('CA9584617MA', 'John', 'Dev', 'db_datawriter', NULL, 'SQL_USER' ) ;

INSERT INTO dbo.T1([servername],[loginname],[dbname],[dbrole],[serverrole],[typedesc])
VALUES ('CA9584617MA', 'PDC\LaNguyen', 'Dev', 'db_datareader', 'sysadmin', 'SQL_USER' ) ;

SELECT *
FROM dbo.T1
GO

-- Desire output.
servername loginname dbname dbrole serverrole typedesc
------------------ --------------- ---------- -------------------- -------------------- ---------------
CA12345NB John Dev db_owner NULL SQL_USER
PDC\languyen Dev db_datareader sysadmin WINDOWS_USER
John Dev db_datareader NULL SQL_USER
PDC\languyen Dev db_datawriter setupadmin WINDOWS_USER
John Dev db_datawriter NULL SQL_USER
John BC db_owner NULL SQL_USER
PDC\LaNguyen BC db_datareader WINDOWS_USER
John BC db_datareader NULL SQL_USER
John BC db_datawriter NULL SQL_USER

CA9584617MA John Dev db_datareader NULL SQL_USER
John Dev db_datawriter NULL SQL_USER
PDC\LaNguyen Dev db_datareader sysadmin SQL_USER

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-18 : 13:21:06

something like this?


select  
		case rn when 1 then servername else '' end as servername,
		loginname, dbname, dbrole, serverrole, typedesc
	
from(
	SELECT *, 
		  rn = row_number() over(partition by servername
								 order by dbname, dbrole, serverrole, typedesc)
	FROM dbo.T1
) _

NguyenL71
Posting Yak Master

228 Posts

Posted - 2015-02-18 : 13:41:14

Thank you so much. I changed your query a little bit and looks like is working. Thanks.

SELECT case rn1 when 1 then servername else '' end as servername
,loginname
,dbname
,dbrole
,case when rn2 > 1 then '' else serverrole end as serverrole
,typedesc
from ( SELECT row_number() over(partition by servername order by dbname, dbrole, serverrole, typedesc) AS rn1
,row_number() over(partition by servername, serverrole order by loginname) AS rn2
,*
FROM dbo.T1 ) as T2;
GO

quote:

Originally posted by gbritton

something like this?


select  
		case rn when 1 then servername else '' end as servername,
		loginname, dbname, dbrole, serverrole, typedesc
	
from(
	SELECT *, 
		  rn = row_number() over(partition by servername
								 order by dbname, dbrole, serverrole, typedesc)
	FROM dbo.T1
) _

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources