May 21, 2015 at 11:51 am
I have two tables:
tblServer
tblInstance
tblServer represents a server, tblInstance represents any SQL Server instances present on the server. They're related thru srvID.
I'd like to write a query that gives me servers with all instances, on one row. This dataset will populate a List in an SSRS report.
I have this but it's clunky and does not provide for more than 2 instances on the server. I'd like this to account for any number of instances all on one row without having to hard code multiple joins:
with serverInfo as
(
select
srv.srvType as Type
,srv.srvName as ServerName
,srv.srvIP as ServerIP
,ins.instNetName as InstanceNetName
,ins.instName as InstanceName
,ins.instPort as InstancePort
,row_number() over (partition by srv.srvName order by srv.srvName) as rownum
from
tblServer srv
join
tblInstance ins
on srv.srvID = ins.srvID
)
select
a.Type
,a.ServerName
,a.ServerIP
,a.InstanceNetName
,a.InstanceName
,a.InstancePort
,b.InstanceNetName
,b.InstanceName
,b.InstancePort
from
(select * from serverInfo where rownum = 1) a
left join
(select * from serverInfo where rownum > 1) b
on a.ServerName = b.ServerName
order by
a.Type;
Here's the DDL and sample data:
CREATE TABLE tblServer(
[srvID] [int] IDENTITY(1,1) NOT NULL,
[srvName] [varchar](256) NOT NULL,
[srvIP] [varchar](15) NOT NULL
)
CREATE TABLE tblInstance(
[instID] [int] IDENTITY(1,1) NOT NULL,
[instNetName] [varchar](256) NOT NULL,
[instName] [varchar](256) NULL,
[instPort] [int] NULL,
[srvID] [int] NOT NULL
)
insert into tblServer values
('prdServer1', '10.10.10.1'),
('prdServer2', '10.10.10.2'),
('devServer1', '192.168.100.1')
insert into tblInstance values
('prdLoadInstance', '', 1433, 1),
('prdReportInstance', '', 1433, 2),
('devLoadInstance', 'devLoad', 1433, 3),
('devReportInstance', 'devReport', 6100, 3),
('devInstance', 'devSandbox', 6200, 3)
Thanks in advance.
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
May 21, 2015 at 1:01 pm
Here's one quick-and-dirty way to do this for up to 5 instances:
Edit: changed the column names to better match the table defs.
with instanceInfo as
(
select
ins.srvID
,ins.instNetName as InstanceNetName
,ins.instName as InstanceName
,ins.instPort as InstancePort
,row_number() over (partition by ins.srvID order by ins.instNetName) as rownum
from
tblInstance ins
)
select
srv.Type
,srv.srvname
,srv.srvip
,MAX(case when rownum = 1 then ii.InstanceNetName end) as InstanceNetName1
,MAX(case when rownum = 1 then ii.InstanceName end) as InstanceName1
,MAX(case when rownum = 1 then ii.InstancePort end) as InstancePort1
,MAX(case when rownum = 2 then ii.InstanceNetName end) as InstanceNetName2
,MAX(case when rownum = 2 then ii.InstanceName end) as InstanceName2
,MAX(case when rownum = 2 then ii.InstancePort end) as InstancePort2
,MAX(case when rownum = 3 then ii.InstanceNetName end) as InstanceNetName3
,MAX(case when rownum = 3 then ii.InstanceName end) as InstanceName3
,MAX(case when rownum = 3 then ii.InstancePort end) as InstancePort3
,MAX(case when rownum = 4 then ii.InstanceNetName end) as InstanceNetName4
,MAX(case when rownum = 4 then ii.InstanceName end) as InstanceName4
,MAX(case when rownum = 4 then ii.InstancePort end) as InstancePort4
,MAX(case when rownum = 5 then ii.InstanceNetName end) as InstanceNetName5
,MAX(case when rownum = 5 then ii.InstanceName end) as InstanceName5
,MAX(case when rownum = 5 then ii.InstancePort end) as InstancePort5
from tblServer srv
inner join instanceInfo ii on
ii.srvID = srv.srvID
group by
srv.Type
,srv.srvname
,srv.srvip
order by
srv.Type
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
May 21, 2015 at 4:10 pm
Thanks for the reply. You eliminated the join but I should have been clearer: I was hoping to be able to choose any amount of instances without having to hard code the extra joins, or instance names.
I was hoping there's a way to do this recursively using a set based approach.
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
May 21, 2015 at 4:18 pm
You'd have to generate dynamic SQL to do that. First, get the highest count as it exists ni your current data. Then modify the above overall structure to dynamically add enough lines to match the highest count.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply