Comma delimiting results of one column to show next to another?

Question

I have a table:

lease_id, suite_id

Lease is a key and you can have multiple suite’s to one lease. I’m trying to create a query that shows me all of the suite’s that a lease is associated with, essentially an output like the following:

lease_id: 1 suite_list: A1, A2, B1

Unfortunately I’m unsure how to approach this (or even how to begin) as this is a new kind of problem for me... Any help would be greatly appreciated!

Answer 1

You can use FOR XML.

The code will be something like this:

-- Sample data tables
select *
into #leases
from (
    select '1' as lease_id
    union 
    select '2' as lease_id
    ) a

select *
into #leaseSuites
from (
    select '1' as lease_id,
        'A1' as suite_id
    union 
    select '1' as lease_id,
        'A2' as suite_id
    union
    select '1' as lease_id,
        'B1' as suite_id
    union 
    select '2' as lease_id,
        'C2' as suite_id
    union
    select '2' as lease_id,
        'B3' as suite_id        
    ) a 


-- Creates comma delimited with child table.
select left(suite_list, LEN(suite_list) - 1) as suite_list
from (
    SELECT 'lease_id: ' + lease_id + ' ' +
      'suite_list: ' + (
      SELECT s.suite_id + ','
      FROM #leaseSuites s
      WHERE l.lease_id = s.lease_id
      ORDER BY s.suite_id
      FOR XML PATH('')
      ) AS suite_list
    FROM #leases l ) a

Click here to see an article with an example.

Answer 2

I'll assume your table is called LeasedSuites.

We'll need a function:

     create function dbo.AllSuite (@l int) returns varchar(100)
     as begin
       declare @v varchar(2);
       declare @r varchar(100);

       DECLARE sc CURSOR FOR select suite_id from LeasedSuites where lease_id = @l
       OPEN sc
       FETCH NEXT FROM sc INTO @v
       WHILE @@FETCH_STATUS = 0 BEGIN
         select @r = @r + ',' + @v;
         FETCH NEXT FROM sc INTO @v
       END
       CLOSE sc
       DEALLOCATE sc

       return substring(@r, 2, len(@r) - 1);
     end

And a query:

     declare @l int;

     create table #out (lease_id int, suite_str varchar(100) null)
     insert #out (lease_id) select distinct lease_id from LeasedSuites

     while (select count(*) from #out where suite_str is null) > 0 begin
       select @l = min(lease_id) from #out where suite_str is null;
       update #out set suite_str = dbo.AllSuite(@l) where lease_id = @l;
     end

     select 'Lease ID: ' + cast(lease_id as varchar(3)) + ' Suites: ' + suite_str from #out order by l;

Hope this helps. Regards JB

If this represents an answer please mark as answer.

Answer 3

I've ended up solving this in two ways. My first method, which was unfortunately quite slow was:

declare @period_id  integer =
                (
                     select period_id
                     from   property.period
                     where  getdate() between period_start and period_end
                 )



;with cte_data as
(
    select  lp.*
    from    property.lease_period lp
    where   period_id = @period_id
)       
, cte_suites as
(
    select  d.lease_id
        ,   (
                select stuff
                ( 
                    (   select ', ' + a.suite_id
                        from 
                        (   select  a.suite_id
                            from    cte_data a
                            where   a.lease_id = d.lease_id
                         ) a
                        for xml path(''))
                     , 1, 2, ''
                ) as suite_list 
            ) suite_list

    from    cte_data d
    group by d.lease_id
) ,
cte_count as
(
    select  lease_id ,
            count(suite_id) as 'suites'
    from    property.lease_period
    where   period_id = @period_id
            and lease_id <> 'No Lease'
    group by lease_id

)
select  d.period_id , 
        d.building_id , 
        d.lease_id , 
        s.suite_list
from    cte_data d
        left outer join cte_suites s
            on d.lease_id = s.lease_id
        inner join cte_count c
            on d.lease_id = c.lease_id
where   period_id = 270
        and d.lease_id <> 'No Lease'
        and c.suites > 1
group by 
        d.period_id , 
        d.building_id , 
        d.lease_id , 
        s.suite_list

I then stripped this back and re-approached it with a new direction, resulting in the following (much, much quicker):

declare @period_id  integer =
            (
                 select period_id
                 from   property.period
                 where  getdate() between period_start and period_end
             )

;with CteLeaseInMultSuites as 
(
    select  period_id, 
            building_id,
            lease_id
    from    property.lease_period
    where   period_id = @period_id
            and lease_id <> 'No Lease'
    group by 
            period_id, 
            building_id, 
            lease_id
    having  count(*) > 1
)

select  period_id, 
        building_id, 
        lease_id, 
        left(x.suite_list, len(x.suite_list) - 1) as suite_list
from    CteLeaseInMultSuites lm
        cross apply
        (
            select  suite_id + ', '
            from    CteLeaseInMultSuites lmx
                    inner join property.lease_period lp
                        on lp.period_id = lmx.period_id
                        and lp.building_id = lmx.building_id
                        and lp.lease_id = lmx.lease_id
            where   lmx.period_id = lm.period_id
                    and lmx.building_id = lm.building_id
                    and lmx.lease_id = lm.lease_id
            for xml path('')
        ) x (suite_list)