How to join id occurrences instead of simply showing count(*)?

Question

I did this snippet to demonstrate: http://sqlfiddle.com/#!6/ed243/2

Schema:

create table professional(
  id int identity(1,3) primary key,
  name varchar(20)
)
insert into professional values('professional A')
insert into professional values('professional B')
insert into professional values('professional C')

create table territory(
  id int identity(2,3) primary key,
  name varchar(20)
)
insert into territory values('territory A')
insert into territory values('territory B')
insert into territory values('territory C')

create table panel(
  id int identity(3,3) primary key,
  idProfessional int not null,
  idTerritory int not null,
)
insert into panel values(1, 2)
insert into panel values(4, 5)
insert into panel values(7, 8)
insert into panel values(1, 5)
insert into panel values(7, 8)
insert into panel values(7, 2)

And the query I've got so far:

select 
    p.id, p.name, count(*) as Territories
from 
    (select distinct idProfessional, idTerritory from panel) panel
inner join
    professional p 
    on p.id = panel.idProfessional
group by 
    p.id, 
    p.name
having count(*) > 1
order by p.id

The above query shows as result in how many territories each professional works filtering with distinct and by showing only professionals that work in more than one territory with having:

-------------------------------------------------------
|    id    |    name              |    Territories    |
-------------------------------------------------------
|    1     |    professional A    |    2              |
|    7     |    professional C    |    2              |
-------------------------------------------------------

Ok, but.. is it possible to show in Territories each idTerritory joined like "2, 5" instead of count(*) ?

Thanks in advance.

Pretty sure you are looking for something like this?? [Simulating group\_concat MySQL function in SQL Server?](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-sql-server) — Sean Lange, Mar 22 '16 at 19:45
@SeanLange. I think it can help joining the fields, but I don't know how to take those fields :-/ — , Mar 22 '16 at 19:59

score 1 · Accepted Answer · edited Nov 13 '18 at 23:28

1

When it's necessary, I usually use the FOR XML function to do this kind of concatenation of multiple rows. I think this query does what you are looking for:

select 
    p.id, p.name, STUFF(
        (select ', ' + CAST(t.id AS VARCHAR(10))
         from panel panel2
         inner join territory t
            ON t.id = panel2.idTerritory
         where panel2.idProfessional = p.id
         order by t.name
         for xml path(''), root('XMLVal'), type 
     ).value('/XMLVal[1]','varchar(max)') 
   , 1, 2, '') as Territories
from panel
inner join
    professional p
    on p.id = panel.idProfessional
group by
    p.id,
    p.name
having count(*) > 1
order by p.id

I used this blog in creating my answer: http://blogs.lobsterpot.com.au/2010/04/15/handling-special-characters-with-for-xml-path/

edited Nov 13 '18 at 23:28

Community

1
1

answered Mar 22 '16 at 20:01

Gyromite

769
6
16

Man, you are a ninja! Thank you :) – Mar 22 '16 at 20:14
if you leave off the `, root('XMLVal'), type ).value('/XMLVal[1]','varchar(max)'` part it reduces the overhead http://sqlfiddle.com/#!3/ec791/1 – JamieD77 Mar 22 '16 at 20:30
Using or not using it is the same? – Mar 22 '16 at 20:41
@JamieD77, that section of code handles converting any XML escape characters back to plain text. Since this source data has no special characters (<, >, ", ', or &), it works fine without it. If you are absolutely sure such characters won't appear in your data (e.g.: you are only working with numeric values), you'd be better off without that code. – Gyromite Aug 08 '16 at 14:28

How to join id occurrences instead of simply showing count(*)?

1 Answers1