Combine result return by query into one string - sql server

Question

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.

Not my answer, but check out stackoverflow.com/questions/194852/… — AndrewP
– AndrewP, Commented Feb 16, 2017 at 21:45

Gurwinder Singh · Accepted Answer · 2017-02-16 20:15:13Z

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'
        );

edited Feb 16, 2017 at 20:15

answered Feb 16, 2017 at 19:58

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

NonProgrammer Over a year ago

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?

Gurwinder Singh Over a year ago

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.

NonProgrammer Over a year ago

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.

Collectives™ on Stack Overflow

Combine result return by query into one string - sql server

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related