CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Michael A
Michael A

Reputation: 9900

Concatenated results for column?

Apologies for the horrible title but I've no idea how to describe this easily. I have the following query that returns 53 rows:

select distinct     
            p.building_name as 'Building', 
            p.building_id as 'ID', 
            p.state as 'State',
            d.division_code + ' - ' + d.division_name as 'Division',
            isnull(r.entity_id, 'None') as 'Entity Code'
        --  isnull(cc.cost_centre, 'None') as 'Cost Centre'
from        property.property p
        left outer join property.division d on p.division_code = d.division_code
        left outer join report_temp.entity_building r on p.building_id = r.building_id
        --left outer join property.cost_centre cc on cc.entity_id = r.entity_id
order by p.building_name asc

When I uncomment the joins above, the code becomes the following and I receive 695 rows as there's multiple cost centre's per entity code:

select distinct     
            p.building_name as 'Building', 
            p.building_id as 'ID', 
            p.state as 'State',
            d.division_code + ' - ' + d.division_name as 'Division',
            isnull(r.entity_id, 'None') as 'Entity Code',
            isnull(cc.cost_centre, 'None') as 'Cost Centre'
from        property.property p
        left outer join property.division d on p.division_code = d.division_code
        left outer join report_temp.entity_building r on p.building_id = r.building_id
        left outer join property.cost_centre cc on cc.entity_id = r.entity_id
order by p.building_name asc

What I'd like to do is display the results of the CostCentre column as a concatenated results so I only receive 53 rows of results. To explain a bit better - when all other columns are the same but there's four different cost centre's per entity code the cost centre column would display as '1111, 1112, 1113' in the one column.

Am I making sense? Is this possible?

edit (explaining better):

i.e.

Building ID Cost Centres
20001       1111, 1112, 1113

Instead of

Building ID Cost Centre
20001       1111
20001       1112
20001       1113

Edit (with answer):

Ended up solving this with the following. Going to make an attempt at a recursive CTE also and will post that when complete for interests sake:

with cte_building_data as
(
    select distinct     
                p.building_name,
                p.building_id,
                p.state as 'State',
                d.division_code + ' - ' + d.division_name as 'Division',
                isnull(r.entity_id, 'None') as entity_id,
                isnull(cc.cost_centre, 'None') as cost_centre
    from        property.property p
            inner join property.division d on p.division_code = d.division_code
            inner join report_temp.entity_building r on p.building_id = r.building_id
            inner join property.cost_centre cc on cc.entity_id = r.entity_id
)

SELECT 
    d.*         
    ,   
    (select stuff
    ( 
        (   SELECT ', ' + a.cost_centre
            FROM 
            (   SELECT  a.cost_centre, a.[entity_id] FROM cte_building_data a
                WHERE a.[entity_id] = d.[entity_id]

             ) a

            FOR XML PATH(''))
         , 1, 2, ''
    ) AS cc_list)

FROM (
        SELECT 

                d.entity_id,
                d.[State],
                d.[Division]
        FROM    cte_building_data d
        GROUP BY                
                d.entity_id,
                d.[State],
                d.[Division]
     ) d

Upvotes: 0

Views: 153

Answers (3)

necromancer
necromancer

Reputation: 24641

This cannot be done in pure SQL. You will need to write logic in some programming language which is calling the SQL. I. e. process it after the SQL stage. If you are lucky, there might be some stored procedure so that you can push the logic down to the database server, but I am not an expert on that.

Upvotes: 1

Chris Shain
Chris Shain

Reputation: 51339

Assuming that you know the set of cost centres that you will get back ahead of time, I think that what you are looking for is the SQL 2008 PIVOT command: http://msdn.microsoft.com/en-us/library/ms177410.aspx

The downsides here are that you need to know the set of cost centres ahead of time, both for the pivot command itself and because you'd get each cost centre as it's own column, which you'd them need to concatenate (assuming you really want one big concatenated column- in most cases having individual columns would be more flexible and therefore preferred).

Upvotes: 2

dotnetstep
dotnetstep

Reputation: 17485

This link definitely help you. http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

  1. Use SQL CLR to concate function
  2. Use CTE

Thanks.

Upvotes: 2

Related Questions