查询给我重复记录我想显示单个记录 [英] Query give me duplicate record I want to display single record

问题描述

1> PersonalDetail



ClientID（pk）

名称，

姓

电子邮件



2> Skillmaster



Skillid（pk）

技能名号



3> EndUser

enduserid（pk）

userid（FK）// perosnaldeail clientid

FeesId（FK）



4> UserskillDetails

id（PK）

ClientId（FK）

SkillId（Fk）



我尝试过：



选择PersonalDetails.name，PersonalDetails.phone，PersonalDetails.email ，EndUser.Amount，SkillMaster.SkillName

来自PersonalDetails加入EndUser on PersonalDetails.ClientID = EndUser.UserId

加入Userskilldetail on Userskilldetail.clientId = PersonalDetails.ClientID

加入SkillMaster on SkillMaster.SkillID = Userskilldetail.Skillid







输出

1>PersonalDetail

ClientID (pk)
name,
surname
email

2> Skillmaster

Skillid(pk)
skillname

3> EndUser
enduserid(pk)
userid(FK) // perosnaldeail clientid
FeesId(FK)

4> UserskillDetails
id(PK)
ClientId (FK)
SkillId(Fk)

What I have tried:

select PersonalDetails.name,PersonalDetails.phone,PersonalDetails.email,EndUser.Amount,SkillMaster.SkillName
from PersonalDetails join EndUser on PersonalDetails.ClientID = EndUser.UserId
join Userskilldetail on Userskilldetail.clientId = PersonalDetails.ClientID
join SkillMaster on SkillMaster.SkillID = Userskilldetail.Skillid



output

jaydeep	444444	[e-mail]	34	mvc
jaydeep	444444	[e-mail]	34	jquery

i需要jaydeep作为一个记录，mvc和jquery在一行上用逗号

i requried jaydeep as a one record and mvc and jquery on one line with comma

jaydeep	444444	[e-mail]	34	mvc,jquery

推荐答案

Create table #temp(name varchar(50),phone int,email varchar(max),amount money, skillName varchar(10));

insert into #temp values('jaydeep',444444,'[e-mail]',34,'mvc'),
('jaydeep',444444,'[e-mail]',34,'jquery'),
('jaydeepshah',50,'[e-mail]',	50,	'css'),
('jaydeepshah',50,'[e-mail]',	50,	'sql');

SELECT DISTINCT name,phone,email,Amount,
STUFF((SELECT DISTINCT ', '+ SkillName 
FROM #temp as t1
WHERE T1.Name =t2.Name
FOR XML PATH(''),TYPE
).value('.','NVARCHAR(MAX)') ,1,2,'')AS SkillName 
FROM #temp t2;
------------------------------------------
name	phone	email	Amount	SkillName
------------------------------------------
jaydeep	444444	[e-mail]	34.00	jquery, mvc
jaydeepshah	50	[e-mail]	50.00	css, sql

-------------------------------------------------------------------
;WITH X AS( 
SELECT 
      PersonalDetails.name, PersonalDetails.phone,
       PersonalDetails.email,EndUser.Amount,SkillMaster.SkillName 
 FROM PersonalDetails INNER JOIN EndUser 
     ON PersonalDetails.ClientID = EndUser.UserId
   INNER JOIN Userskilldetail ON Userskilldetail.clientId = PersonalDetails.ClientID
   INNER JOIN SkillMaster ON SkillMaster.SkillID = Userskilldetail.Skillid)
 
   SELECT DISTINCT name,phone,email,Amount,
        STUFF((SELECT DISTINCT ', '+SKL.SkillName 
             FROM X AS SKL WHERE SKL.Name=X.Name
                 FOR XML PATH(''),TYPE
                    ).value('.','NVARCHAR(MAX)') ,1,2,'')AS  SkillName 
   FROM X

这篇关于查询给我重复记录我想显示单个记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！