1

Following query returns two rows. I'd like to combine these two rows. Is this possible? This logic will later become one of the columns for another query:

select SUM(case 
        when FreeRentMonth = 'Y'
            then 1
        else 0
        end) as months
from LARBICRMFact
where rlm_spaceID in (
    select RefCRMSpaceID
    from #OneAE_Multiple_CRM_deals fs
    where fs.RefLeaseAssumptionID = '58500-TSPECTENB-LSPECTENB-0210'
    )
group by rlm_spaceID

Desired result:
6|7 (instead of 6 and 7 being in two separate rows).

NOTE*: The subquery can return 1 or many values.

NonProgrammer
  • 1,337
  • 2
  • 23
  • 53
  • 1
    Not my answer, but check out http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string – AndrewP Feb 16 '17 at 21:45

1 Answers1

2

You need to know the values of rlm_spaceID to convert then rows into columns.

for e.g. say the values 6 and 7 are for rlm_spaceID "value1" and "value2".

rlm_spaceID   months
value1        6
value2        7

Something like this:

select SUM(case 
            when rlm_spaceID = 'value1'
                and FreeRentMonth = 'Y'
                then 1
            else 0
            end) as val1,
    SUM(case 
            when rlm_spaceID = 'value2'
                and FreeRentMonth = 'Y'
                then 1
            else 0
            end) as val2
from LARBICRMFact
where rlm_spaceID in (
        select RefCRMSpaceID
        from #OneAE_Multiple_CRM_deals fs
        where fs.RefLeaseAssumptionID = '58500-TSPECTENB-LSPECTENB-0210'
        );
Gurwinder Singh
  • 38,557
  • 6
  • 51
  • 76
  • The problem is it is random how many rlm_spaceIDs are returned by the sub-query. It could be 1 or up to 10. Is there a function I could create which would do this for me? – NonProgrammer Feb 16 '17 at 21:16
  • do you want to get the result in one row and one column? or one row and separate columns? the first one can be easily achieved. – Gurwinder Singh Feb 17 '17 at 04:05
  • Correct, I'd like the result in one row and one column. However, I'll be passing the RefLeaseAssumptionID at row level from another select statement. This could will pretty much become a column on that select statement. – NonProgrammer Feb 17 '17 at 14:21