multiple child records per row, dynamically?

  • 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
    ----------------------------------------------------------------------------

    Yeah, well...The Dude abides.
  • 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • 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
    ----------------------------------------------------------------------------

    Yeah, well...The Dude abides.
  • 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 4 posts - 1 through 3 (of 3 total)

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