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

Question

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

请找到以下我的确切要求，并帮助我的精细调优查询。请找到以下我的确切要求，并帮助我的精细调优查询。请找到以下我的确切要求，并帮助我的精细调优查询。

​

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

试试下面的代码片段-

    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

编辑：根据需求修改了代码。请现在再检查一下。

Answer 2

使用FOR XML PATH('')连接组：

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

结果：

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

Answer 3

假设userid + username组合是唯一的，请尝试以下查询。另外，考虑到对于单个组，您不需要在组周围加上括号，我更新了下面的查询。

解释:我已经使用STUFF函数来计算带有逗号的组的分组，后面跟着每个组代码(如LS,LG, )，现在基于使用CHARINDEX的逗号位置与使用LEN的字符串长度的比较，我们将添加组()或组的逻辑附加到字符串中。

   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

和更新的sql花键链接http://sqlfiddle.com/#!6/21f65/1。

给出这个精确的输出

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