How to flat out the duplicate

Transact-SQL
1

Hi,

How to modify my select to eliminate the "duplicate"?

My question could be explained better in the sample case.

-- sample data and code

If there is a table called #_Stu
SubjectID StudentName RoleType

1 Mary M
1 John M
1 Sam M
2 Alaina M
2 Edward M
3 Trouble M
3 Maker Null

Result I expected was:
SubjectID StudentName RoleType

1 Mary, John, Sam M
2 Alaina, Edward M
3 Maker, Trouble M

But currently I got
SubjectID StudentName RoleType

1 Mary, John, Sam M
2 Alaina, Edward M
3 Maker, Trouble Null
3 Maker, Trouble M

Select distinct ST2.SubjectID, St2.RoleType,
substring(
(
Select ','+ST1.StudentName AS [text()]
From dbo.#_Stu ST1
Where ST1.SubjectID = ST2.SubjectID
ORDER BY ST1.SubjectID, ST1.RoleType
For XML PATH ('')
), 2, 1000) [Students]
From dbo.#_Stu ST2

and, by the way, adding another select on top to filter out the Null is not an valid option because there are legit null records, i.e. multiple line of subjectID 4 with Null roleType are concatenated into one line correctly.

if object_ID('tempdb..#_Stu') is not null drop table #_Stu;

Create table #_Stu (SubjectID int, StudentName varchar(20), RoleType Varchar(5))

Insert into #_Stu
Select 1, 'Mary', 'M' union
Select 1, 'John', 'M' union
Select 1, 'Sam', 'M' union
Select 2, 'Alaina', 'M' union
Select 2, 'Edward', 'M' union
Select 3, 'Trouble', 'M' union
Select 3, 'Maker', Null

drop table #_Stu;

Thanks!

2

What you need to do will vary depending on the rule you want to use to eliminate the duplicate row.
For example, what should the output be if the first row was "Select 1, 'Mary', 'X' "? That is, X instead of M.
Or, what should the output be if you had another row that was "Select 4, 'Bob', NULL "

Here is one way to eliminate the duplicate row, but depending on your answer to the above questions, this may or may not be what you want.

Select  ST2.SubjectID, MAX(St2.RoleType) as RoleType,
substring(
(
Select ','+ST1.StudentName AS [text()]
From dbo.#_Stu ST1
Where ST1.SubjectID = ST2.SubjectID
ORDER BY ST1.SubjectID, ST1.RoleType
For XML PATH ('')
), 2, 1000) [Students]
From dbo.#_Stu ST2
group by St2.SubjectId

BTW, it is very rarely that I see posts with the required DDL and sample data. Thank you for posting your question in a way that is easy to consume.

1 Like
3

My bad, the business rule said, if there is a M, no need for Null.