SQL Consolidate rows

Mauro Cazabonnet
Mauro Cazabonnet used Ask the Experts™
on
Hi,
I'm trying to consolidate the rows of this query

select CASE WHEN [type_desc] = 'ROWS' THEN [physical_name] ELSE NULL END as [database_path], CASE WHEN [type_desc] = 'LOG' THEN [physical_name] ELSE NULL END as [log_path] from sys.master_files 
WHERE [database_id] = 13

Open in new window


database_path      log_path
E:\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Exchange_2013.mdf      NULL
NULL      L:\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\Exchange_2013_log.ldf

Should look like this

database_path                                                                                                                              log_path
E:\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Exchange_2013.mdf      L:\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\Exchange_2013_log.ldf
      
Any help much appreciated

Regards,
M
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
IT Engineer
Distinguished Expert 2017
Commented:
This may work but only if you've a single row for the transaction log and it will repeat it if you have more than one that file:
select [physical_name] as [database_path], 
	(SELECT [physical_name] 
	from sys.master_files 
	WHERE [database_id] = 13 AND [type_desc] = 'LOG') as [log_path] 
from sys.master_files
WHERE [database_id] = 13 AND [type_desc] = 'ROWS'

Open in new window

Mauro CazabonnetSenior .NET Software Engineer
Top Expert 2015

Author

Commented:
thx!!!!
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Correction: "it will repeat log files if you have more than one data file"

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial