SQL -过滤重复项
SQL -过滤重复项
提问于 2014-02-19 11:52:52
我有一个sql查询可以正常工作，但不能生成准确的结果
我的查询详情如下：
declare @test varchar(500)
SELECT @test=coalesce(@test+',','') + cast(RoleName as varchar) FROM
( select roles.RoleName from LU_BCOMS_usersroles usrroles
inner join LU_BCOMS_roles roles
on roles.roleid = usrroles.Roles_roleid
where Users_Userid='MV10310'
) as Tbl
select repfamily.ProductName as Category,repfamily.Family as SeqChange,repfamily.RepFamily as DescOfChange, req.*,
TaskCompVer =
CASE WHEN req.UpdateByASPM is not null THEN 'Provide PLQM Wish List'
WHEN req.UpdateByASPM is null THEN 'Provide ASPM Wish List'
WHEN req.CreatedBy is not null THEN 'Provide ASPM Wish List'
END
from performa.TX_BCOMS_Request as req
inner join TX_BCOMS_Requestrepfamily family on
family.request_requestid=req.requestid
inner join LU_BCOMS_RepFamily as repfamily on
family.RepFamily_repFamilyid=repfamily.repfamilyid
where req.UpdatedByPLQM is null and (
((CHARINDEX('ASPM',@test)> 0 and CHARINDEX('PLQM',@test)> 0) and req.UpdatedByPLQM IS null)
or
((CHARINDEX('PLQM' ,@test)> 0) and req.UpdateByASPM IS NOT null)
or
((CHARINDEX('ASPM',@test)> 0 ) and req.UpdateByASPM IS null)
or
((CHARINDEX('PLQM' ,@test)> 0) and req.UpdateByASPM IS NOT null)
or
((CHARINDEX('ASPM' ,@test)< 0 and CHARINDEX('PLQM',@test) < 0) and req.CreatedBy IS null)
)输出：
Caterogy SeqCategory DescofChange RequestId TaskCompVer
BIGBEAR BIGBEAR BIGBEAR B14020002 Provide ASPM Wish List
ARCUS3PL KOJN-RE ARCUS3PL B14020002 Provide ASPM Wish List
AURORA Aurora Aurora B14020003 Provide ASPM Wish List当requestId和TaskCompVer相同时，不需要显示2条记录，必须像下面这样过滤。
我需要如下输出：
Output :
Caterogy SeqCategory DescofChange RequestId TaskCompVer
BIGBEAR,ARCUS3PL BIGBEAR,KOJN-RE BIGBEAR,ARCUS3PL B14020002 Provide ASPM Wish List
AURORA Aurora Aurora B14020003 Provide ASPM Wish List我需要显示的实际如上所述，我尝试使用填充函数无法生成实际的输出…
Stack Overflow用户
发布于 2014-02-19 12:43:50
希望能对你有所帮助。
CREATE TABLE tempTable(name VARCHAR(50),subjects VARCHAR(50),phone VARCHAR(50))
INSERT INTO tempTable VALUES
('siddique','CRM','123456'),('siddique','Asp.net','9874563'),
('siddique','sql server','44451685'),('Danish','MVC','123456'),
('Danish','sql server','9874563'),('Danish','WCF','44451685'),
('shah g','Account','123456'),('shah g','MBA','9874563'),
('shah g','Math','44451685')您的简单查询选择所有数据
SELECT * FROM tempTable
name subjects phone
siddique CRM 123456
siddique Asp.net 9874563
siddique sql server 44451685
Danish MVC 123456
Danish sql server 9874563
Danish WCF 44451685
shah g Account 123456
shah g MBA 9874563
shah g Math 44451685使用STUFF以逗号分隔每个名称的值(按名称分组)
SELECT
name
,STUFF((SELECT ', ' + subjects
FROM tempTable temp2 WHERE temp2.name=temp1.name
FOR XML PATH('')), 1, 1, '') AS subjects
,STUFF((SELECT '; ' + phone
FROM tempTable temp2 WHERE temp2.name=temp1.name
FOR XML PATH('')), 1, 1, '') AS phones
FROM tempTable temp1
GROUP BY name
DROP TABLE tempTable输出：
name subjects phones
Danish MVC, sql server, WCF 123456; 9874563; 44451685
shah g Account, MBA, Math 123456; 9874563; 44451685
siddique CRM, Asp.net, sql server 123456; 9874563; 44451685页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/21870659
复制相关文章
相似问题
腾讯云开发者
