Get distinct group, please help urgent.

[peac3]

Hi all,

I have security id and owner id as the table below and would like to group the owner
with different security id and generate new owner_group_id as result below:

data

secu_ID		Owner_ID
000000000007016	000CARLE0C4S000
000000000007016	000CRAICARHO001
000000000007016	000NATAHOLDI000
000000000007016	000CARLCOPTL000
000000000007016	000CARLE0M4S000
000000000696197	000MCVIFAMTR002
000000000696197	000MCVITRPTL000
000000000696197	000MERMBETPL000
000000000696197	000ROSSFIHPL000
000000000696197	000ROSSHOTTR000
000000000696198	000MCVIFAMTR002
000000000696198	000MCVITRPTL000
000000000696198	000MERMBETPL000
000000000696198	000ROSSFIHPL000
000000000696198	000ROSSHOTTR000

expected result:

SECU_ID		Owner_group_Id
000000000007016		1
000000000696197 	2
000000000696198 	2

any input would be highly appreciated

Thank you

 

[imex]

Hi,

Try something like this:

with CTE_Concat as
(
    SELECT DISTINCT
        a.secu_ID, 
        STUFF( (SELECT ', ' + s.Owner_ID
                FROM MyTable as s
                WHERE s.secu_ID = a.secu_ID
                FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
                ,1, 2, '') as Owner_ID_Concat
    FROM MyTable as a
)

select
    secu_id,
    DENSE_RANK() OVER(ORDER BY Owner_ID_Concat) as Owner_group_Id
from CTE_Concat

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[peac3]

it works, thanks!