Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Get distinct group, please help urgent.

Status
Not open for further replies.

peac3

Technical User
Jan 17, 2009
226
AU
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:

Code:
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

Code:
expected result:

SECU_ID		Owner_group_Id
000000000007016		1
000000000696197 	2
000000000696198 	2


any input would be highly appreciated

Thank you
 
Hi,

Try something like this:

Code:
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]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top