Please help with SELECT statement.

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 2005 Forums

Transact-SQL (2005)

Please help with SELECT statement.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2008-08-14 : 12:01:57

I try to put the log files on the same row as data file and wonder if you can helps.
Please see the results desire below. I am using SQL 2005. Thank you in advance.

drop table [MyTestTable]
GO

CREATE TABLE [MyTestTable] (
[DatabaseName] [varchar] (25) NULL ,
[LogicalFileName] [varchar] (34) NULL ,
[PhysicalFileName] [varchar] (50) NULL ,
[Size] [char] (7) NULL ,
[ExistingDrive] [char] (1) NULL ,
[FILEGROUP_TYPE] [nvarchar] (4) NOT NULL ,
[FileId] [smallint] NOT NULL
) ON [PRIMARY]
GO

Insert into [MyTestTable] ([DatabaseName], [LogicalFileName], [PhysicalFileName], [Size], [ExistingDrive], [FILEGROUP_TYPE], [FileId]) values('QNXT_PLANDATA_WA', 'PLANDATA_WA_Primary', 'R:\MSSQL2000\DATA\QNXT_PLANDATA_WA_Data.MDF', '675', 'R', 'Data', 1)
Insert into [MyTestTable] ([DatabaseName], [LogicalFileName], [PhysicalFileName], [Size], [ExistingDrive], [FILEGROUP_TYPE], [FileId]) values('QNXT_PLANDATA_WA', 'PLANDATA_WA_Log', 'T:\MSSQL2000\DATA\QNXT_PLANDATA_WA_Log.LDF', '30000', 'T', 'Log', 2)
Insert into [MyTestTable] ([DatabaseName], [LogicalFileName], [PhysicalFileName], [Size], [ExistingDrive], [FILEGROUP_TYPE], [FileId]) values('QNXT_PLANDATA_WA', 'PLANDATA_WA_Plandata', 'R:\MSSQL2000\DATA\QNXT_PLANDATA_WA_Data1.NDF', '126000', 'R', 'Data', 3)
Insert into [MyTestTable] ([DatabaseName], [LogicalFileName], [PhysicalFileName], [Size], [ExistingDrive], [FILEGROUP_TYPE], [FileId]) values('QNXT_PLANDATA_WA', 'PLANDATA_WA_2_Data', 'S:\MSSQL2000\DATA\QNXT_PLANDATA_WA_Data2.NDF', '134000', 'S', 'Data', 4)
Insert into [MyTestTable] ([DatabaseName], [LogicalFileName], [PhysicalFileName], [Size], [ExistingDrive], [FILEGROUP_TYPE], [FileId]) values('QNXT_PLANDATA_WA', 'PLANDATA_WA_3_DATA', 'S:\MSSQL2000\DATA\PLANDATA_WA_3_DATA_Data.NDF', '50000', 'S', 'Data', 5)
Insert into [MyTestTable] ([DatabaseName], [LogicalFileName], [PhysicalFileName], [Size], [ExistingDrive], [FILEGROUP_TYPE], [FileId]) values('QNXT_PLANDATA_WA', 'PLANDATA_WA_4_Data', 'S:\MSSQL2000\DATA\PLANDATA_WA_4_DATA_Data.NDF', '20000', 'S', 'Data', 6)
Insert into [MyTestTable] ([DatabaseName], [LogicalFileName], [PhysicalFileName], [Size], [ExistingDrive], [FILEGROUP_TYPE], [FileId]) values('QNXT_PLANDATA_WA_ASO', 'QNXT_PLANDATA_FL_Data', 'S:\MSSQL2000\DATA\QNXT_PLANDATA_WA_ASO.mdf', '9000', 'S', 'Data', 1)
Insert into [MyTestTable] ([DatabaseName], [LogicalFileName], [PhysicalFileName], [Size], [ExistingDrive], [FILEGROUP_TYPE], [FileId]) values('QNXT_PLANDATA_WA_ASO', 'QNXT_PLANDATA_FL_Log', 'T:\MSSQL2000\DATA\QNXT_PLANDATA_WA_ASO_log.ldf', '1149', 'T', 'Log', 2)
go

DatabaseName LogicalFileName PhysicalFileName Size ExistingDrive FILEGROUP_TYPE FileId
------------------------- ---------------------------------- -------------------------------------------------- ------- ------------- -------------- ------
QNXT_PLANDATA_WA PLANDATA_WA_Primary R:\MSSQL2000\DATA\QNXT_PLANDATA_WA_Data.MDF 675 R Data 1

QNXT_PLANDATA_WA PLANDATA_WA_Log T:\MSSQL2000\DATA\QNXT_PLANDATA_WA_Log.LDF 30000 T Log 2

QNXT_PLANDATA_WA PLANDATA_WA_Plandata R:\MSSQL2000\DATA\QNXT_PLANDATA_WA_Data1.NDF 126000 R Data 3
QNXT_PLANDATA_WA PLANDATA_WA_2_Data S:\MSSQL2000\DATA\QNXT_PLANDATA_WA_Data2.NDF 134000 S Data 4
QNXT_PLANDATA_WA PLANDATA_WA_3_DATA S:\MSSQL2000\DATA\PLANDATA_WA_3_DATA_Data.NDF 50000 S Data 5
QNXT_PLANDATA_WA PLANDATA_WA_4_Data S:\MSSQL2000\DATA\PLANDATA_WA_4_DATA_Data.NDF 20000 S Data 6
QNXT_PLANDATA_WA_ASO QNXT_PLANDATA_FL_Data S:\MSSQL2000\DATA\QNXT_PLANDATA_WA_ASO.mdf 9000 S Data 1

QNXT_PLANDATA_WA_ASO QNXT_PLANDATA_FL_Log T:\MSSQL2000\DATA\QNXT_PLANDATA_WA_ASO_log.ldf 1149 T Log 2

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

-- How can I get the results like this:

DatabaseName LogicalFileName PhysicalFileName Size ExistingDrive LogicalLogFileName PhyLogFileName Size ExistingDrive
------------------------- ---------------------------------- -------------------------------------------------- ------- ------------- ------------------ ------------------------------------------ ----- -------------
QNXT_PLANDATA_WA PLANDATA_WA_Primary R:\MSSQL2000\DATA\QNXT_PLANDATA_WA_Data.MDF 675 R PLANDATA_WA_Log T:\MSSQL2000\DATA\QNXT_PLANDATA_WA_Log.LDF 30000 T
QNXT_PLANDATA_WA PLANDATA_WA_Plandata R:\MSSQL2000\DATA\QNXT_PLANDATA_WA_Data1.NDF 126000 R
QNXT_PLANDATA_WA PLANDATA_WA_2_Data S:\MSSQL2000\DATA\QNXT_PLANDATA_WA_Data2.NDF 134000 S
QNXT_PLANDATA_WA PLANDATA_WA_3_DATA S:\MSSQL2000\DATA\PLANDATA_WA_3_DATA_Data.NDF 50000 S
QNXT_PLANDATA_WA PLANDATA_WA_4_Data S:\MSSQL2000\DATA\PLANDATA_WA_4_DATA_Data.NDF 20000 S QNXT_PLANDATA_FL_Log T:\MSSQL2000\DATA\QNXT_PLANDATA_WA_ASO_log.ldf 1149 T

QNXT_PLANDATA_WA_ASO QNXT_PLANDATA_FL_Data S:\MSSQL2000\DATA\QNXT_PLANDATA_WA_ASO.mdf 9000 S

-- Testing...
SELECT DatabaseName,
LogicalFileName,
PhysicalFileName,
Size,
ExistingDrive
--,LogicalLogFileName
--,PhyLogFileName
--,Size
--,LogExistingDrive
FROM MyTestTable
GO

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-14 : 12:11:08

Why are you hardcoding like this?

You need to join 3 system tables to get desired output:
1)sys.databases
2)sysaltfiles
3)sysfilegroups.

NguyenL71
Posting Yak Master

228 Posts

Posted - 2008-08-14 : 12:46:42

quote:
Originally posted by sodeep

Why are you hardcoding like this?

You need to join 3 system tables to get desired output:
1)sys.databases
2)sysaltfiles
3)sysfilegroups.

I am try to select data from the table above. Data already populate into a table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-14 : 13:50:09

[code]SELECT DatabaseName,
MAX(CASE WHEN [FILEGROUP_TYPE]='Data' THEN LogicalFileName ELSE NULL END) AS LogicalFileName,
MAX(CASE WHEN [FILEGROUP_TYPE]='Data' THEN PhysicalFileName ELSE NULL END) AS PhysicalFileName,
MAX(CASE WHEN [FILEGROUP_TYPE]='Data' THEN Size ELSE NULL END) AS Size,
MAX(CASE WHEN [FILEGROUP_TYPE]='Data' THEN ExistingDrive ELSE NULL END) AS ExistingDrive,
MAX(CASE WHEN [FILEGROUP_TYPE]='Log' THEN LogicalFileName ELSE NULL END) AS LogicalLogFileName,
MAX(CASE WHEN [FILEGROUP_TYPE]='Log' THEN PhysicalFileName ELSE NULL END) AS PhysicalFileName,
MAX(CASE WHEN [FILEGROUP_TYPE]='Log' THEN Size ELSE NULL END) AS Size,
...
FROM [MyTestTable]
GROUP BY DatabaseName[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources