SQL Server 2012:如何在列中找到公共值

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

SQL Server 2012:如何在列中找到公共值

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

4 个解决方案

#1

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.

编辑:按要求修改代码。现在请重新审视。

#2

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

使用XML路径(")连接组:

SQL Fiddle

SQL小提琴

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) |

#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.

假设userid + username组合是唯一的，请尝试以下查询。还考虑到，在单个组的情况下，您不需要围绕组进行括号，我将更新下面的查询。

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

说明:我使用了STUFF函数来计算组的分组，每个组后面都有一个逗号，比如LS,LG，现在通过使用LEN使用CHARINDEX对逗号位置和字符串长度进行比较，我们将逻辑添加group()或group到字符串中

   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

并更新了sqlfiddle链接http://sqlfiddle.com/#

gives this exact output

给这个输出

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

#4

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.

最后，我开发了下面的查询。谢谢你们所有人。如果需要，可以复制下面的查询并在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