SQL Server 2012: How to find common values in a column

Question

SQL Server 2012: How to find common values in a column

Please find below my exact requirement and help me with the Fine tuned query. Please find below my exact requirement and help me with the Fine tuned query. Please find below my exact requirement and help me with the Fine tuned query.

  

Input :

select 1, 'sankar', 'GROUPLG'
union all 
select 1, 'sankar', 'GROUPLS'
union all 
select 1, 'sankar', 'GROUPNG'
union all 
select 1, 'sankar', 'GROUPNS'
union all
select 2, 'Srini', 'HYDRSPMLG'
union all 
select 2, 'Srini', 'HYDRSPMLS'
union all 
select 3, 'Ravi', 'AADSCLS'
union all
select 4, 'Arun',  'RREDFTLS'
union all
select 4, 'Arun',  'RREDFTNG'
union all
select 5, 'Raja',  '1234567'
union all
select 5, 'Raja',  'ABCDESLS'
union all
select 5, 'Raja',  'ABCDESLG'
union all
select 6, 'Dhilip',  'GGGGRASCDW_RV'


Output :

-- 1 Sankar GROUP(LG,LS,NG,NS)
-- 2 Srini  HYDRSPM(LG,LS)
-- 3 Ravi   AADSCLS
-- 4 Arun   RREDFT(LS,NG)
-- 5 Raja   1234567
-- 5 Raja   ABCDESLG(LG,LS)
-- 6 dhilip GGGGRASCDW_RV

Answer 1

Finally i have succeeded by the below query which i have developed. Thanks for you all. If you want You can copy the below query and execute in SSMS.

begin tran

Create table #temp (userid int, username varchar(50), groupname varchar(50))

insert into #temp(userid , username , groupname)
select 1, 'sankar', 'GROUPLG'
union all 
select 1, 'sankar', 'GROUPLS'
union all 
select 1, 'sankar', 'GROUPNG'
union all 
select 1, 'sankar', 'GROUPNS'
union all
select 2, 'Srini', 'HYDRSPMLG'
union all 
select 2, 'Srini', 'HYDRSPMLS'
union all 
select 3, 'Ravi', 'AADSCLS'
union all
select 4, 'Arun',  'RREDFTLS'
union all
select 4, 'Arun',  'RREDFTNG'
union all
select 5, 'Raja',  '1234567'
union all
select 5, 'Raja',  'ABCDESLS'
union all
select 5, 'Raja',  'ABCDESLG'
union all
select 6, 'Dhilip',  'GGGGRASCDW_RV'
union all
select 6, 'Dhilip',  'CDW_RV'
union all
select 6, 'Dhilip',  'GFNG'
union all
select 6, 'Dhilip',  'GFNS'
union all
select 7, 'Satya',    '184518451845'

select * from #temp

select tp.userid , tp.username, groupname + CASE WHEN tp.flag = 1 THEN  CASE WHEN ct.cnt > 1 then ' (' else '' end + 								
																			ISNULL(pt.grouptype1,'')+case when grouptype2 is not null 
																									      and grouptype1 is not null then ',' else '' end +
																			ISNULL(pt.grouptype2,'')+case when grouptype3 is not null 
																										  and (grouptype1 is not null 
																										  or   grouptype2 is not null ) then ',' else '' end +
																			ISNULL(pt.grouptype3,'')+case when grouptype4 is not null 
																										  and (grouptype1 is not null
																										  or grouptype2 is not null
																										  or grouptype3 is not null) then ',' else '' end +
																			ISNULL(pt.grouptype4,'') + case when ct.cnt > 1 then ')' else '' end
											ELSE ''
											END as Permission

from (SELECT distinct userid , username, CASE WHEN RIGHT(groupname,2) IN ('LG','LS','NG','NS') THEN Substring(groupname,1,len(groupname)-2)
											  ELSE groupname END as groupname ,
											  CASE WHEN RIGHT(groupname,2) IN ('LG','LS','NG','NS') THEN 1
											  ELSE 0 END as flag from #temp ) tp

--WHERE Substring(groupname,1,len(groupname)-2) IN ('LG','LS','NG','NS')

join (select userid , [LG] as grouptype1 , [LS] as grouptype2 , [NG] as grouptype3 , 
[NS] as grouptype4
FROM (SELECT userid , RIGHT(groupname,2) as grouptype FROM #temp) as Sourcetable

PIVOT (MAX(grouptype)
for grouptype in ([LG],[LS],[NG],[NS])) As Pivottable) pt
ON tp.userid	=	pt.userid
join (select userid, count(*) as cnt from #temp group by userid ) ct
on ct.userid = tp.userid

DROP TABLE #temp

-- Expected Output
 -- 1 Sankar GROUP(LG,LS,NG,NS)
 -- 2 Srini  HYDRSPM(LG,LS)
 -- 3 Ravi   AADSCLS
 -- 4 Arun   RREDFT(LS,NG)
 -- 5 Raja   1234567
 -- 5 Raja   ABCDESLG(LG,LS)
 -- 6 dhilip GGGGRASCDW_RV

rollback

Answer 2

Try the below code snippet -

    select a.userid,a.username,
CASE WHEN PATINDEX('%,%',a.groupname) > 0 THEN 
     LEFT(a.groupname,PATINDEX('%,%',a.groupname)-3)+'('+REPLACE(a.groupname,LEFT(a.groupname,PATINDEX('%,%',a.groupname)-3),'')+')'         
ELSE a.groupname 
END as groupname 
from
(select userid,username,
    stuff((
        select ',' + t.[Groupname]
        from #users t
        where t.Userid = t1.userid
        order by t.[Groupname]
        for xml path('')
    ),1,1,'') as groupname
from #users t1
group by userid,Username) a

Edit: Changed the code as per the requirement. Please re-check now.

Answer 3

Assuming userid + username combination is unique, please try following query. Also considering that you don't need parenthesis around group in case of a single group, I'v updated the below query.

explanation: I've used STUFF function to calculate the grouping of groups with a comma followed by every group code like LS,LG, Now based on comparison of position of comma using CHARINDEX with length of string using LEN, we attach the logic either adding GROUP() or GROUP to the string

   select 
    userid,
    username, 
    CASE 
        WHEN CHARINDEX(',',groupname)<LEN(groupname) 
        THEN 'GROUP('+ SUBSTRING(groupname,1,LEN(groupname)-1) +')'
        ELSE 'GROUP'+SUBSTRING(groupname,1,LEN(groupname)-1)
    end as groupname 
from 
    (
        select 
          userid, 
          username,
              stuff((
                            select 
                                replace(groupname,'group','') + ',' 
                            from tbl 
                                where 
                                    userid=t.userid and username=t.username 
                            for xml path(''),type).value('.','varchar(max)'),1,0,'')

            as groupname
        from tbl t
        group by userid, username 
    )t

and updated sql fiddle link http://sqlfiddle.com/#!6/21f65/1

gives this exact output

  Userid    Username    Groupname
  1             Sankar         GROUP(LG,LS,NG,NS)
  2             Srini          GROUP(LG,LS)
  3             Aathi          GROUPLS

Answer 4

Using FOR XML PATH('') to concatenate the groups:

SQL Fiddle

SELECT
    t.Userid,
    t.Username,
    Groupname = 'GROUP(' + 
        STUFF((
            SELECT ',' + STUFF(GroupName, 1, 5, '')
            FROM tbl
            WHERE Userid = t.Userid
            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
        , 1, 1, '') + ')'
FROM tbl t
GROUP BY t.Userid, t.Username
DROP TABLE tbl

RESULT:

| Userid | Username |          Groupname |
|--------|----------|--------------------|
|      1 |   sankar | GROUP(LG,LS,NG,NS) |
|      2 |    Srini |       GROUP(LG,LS) |
|      3 |    Aathi |          GROUP(LS) |