Get once time a duplicate record (SQL)

Question

SELECT DISTINCT A.LeaseID, 
                C.SerialNumber, 
                B.LeasedProjectNumber As 'ProjectNumber', 
                A.LeaseComment As 'LeaseContractComments'
FROM aLease A
LEFT OUTER JOIN aLeasedAsset B
ON a.LeaseID = B.LeaseID
LEFT OUTER JOIN aAsset C
ON B.LeasedProjectNumber = C.ProjectNumber AND B.PartID = C.aPartid
WHERE A.LeaseComment IS NOT NULL

enter image description here

I got this result from a query statement. But I don't want to get repeated the last column(Comments) for the 3 records in the second column.

I want for the values on the second column write once the repeated comment. Like a Group By

Showing the sql query would be incredibly helpful. There could be any number of reasons why you are getting duplicate results. And there could be a variety of ways to fix it. — Taryn, Mar 03 '15 at 17:42
SELECT DISTINCT A.LeaseID, C.SerialNumber, B.LeasedProjectNumber As 'ProjectNumber', A.LeaseComment As 'LeaseContractComments', B.LeasedAssetComment FROM aLease A LEFT OUTER JOIN aLeasedAsset B ON a.LeaseID = B.LeaseID LEFT OUTER JOIN aAsset C ON B.LeasedProjectNumber = C.ProjectNumber AND B.PartID = C.aPartid WHERE A.LeaseComment IS NOT NULL AND B.LeasedAssetComment IS NOT NULL — Elian, Mar 03 '15 at 17:46
You have 5 columns in your query, but are only displaying 4. I dont understand which of the comments you want to get rid of. — crthompson, Mar 03 '15 at 17:51
Hi my friend, disregard the last column, I putted it for test purposes. — Elian, Mar 03 '15 at 18:01
Add to your question what you'd like your results to be, and you'll be in business, it's unclear as it is. — Hart CO, Mar 03 '15 at 18:11
Ok.. that solves one question, the next is why do you think the text in the last column shouldnt be repeated? @HartCO's point is good to. Please add your expected results. — crthompson, Mar 03 '15 at 18:11
If this is for reporting, can you setup the report program to skip duplicates in the same column? Another option is to return the lease table separately. If that is not acceptable, you could create a temp #Table or CTE and set the comment on only the first matching row. — Paul Williams, Mar 03 '15 at 18:15
I want for the values on the second column write once the repeated comment. Like a Group By — Elian, Mar 03 '15 at 18:16
I don't follow what you mean by that, could you show us in the question? Someone can definitely help get you what you're after, just needs to be clear what you want. — Hart CO, Mar 03 '15 at 18:22
I don't want to get the values in the comment column thrice or repeated. Just once. Like if Group By A.LeaseComment only. — Elian, Mar 03 '15 at 18:30

score 0 · Answer 1 · edited May 23 '17 at 10:09

Alright, I'll take a stab at this. It's pretty unclear what exactly you're hoping for, but reading your comments, it sounds like you're looking to build a hierarchy of sorts in your table.

Something like this:

"Lease Terminated Jan 29, 2013 due to the event of..."
    216    24914   87
    216    724992  87
    216    724993  87
"Other potential column"
    217    2132    86
    ...
...

Unfortuantely, I don't believe that that's possible. SQL Server is pretty strict about returning a table, which is two-dimensional by definition. There's no good way to describe a hierarchy such as this in SQL. There is the hierarchyid type, but that's not really relevant here.

Given that, you only really have two options:

My preference 99% of the time, just accept the duplicates. Handle them in your procedural code later on, which probably does have support for these trees. Unless you're dealing with performance-critical situations, or if you're pulling back a lot of data (or really long comments), that should be totally fine.
If you're hoping to print this result directly to the user, or if network performance is a big issue, aggregate your columns into a single record for each comment. It's well-known that you can't have multiple values in the same column, for the very same reason as the above-listed result isn't possible. But what you could do, data and your own preferences permitting, is write an aggregate function to concatenate the grouped results into a single, comma-delimited column.

You'd likely then have to parse those commas out, though, so unless network traffic is your biggest concern, I'd really just do it procedural-side.

Thanks Matt, I'm trying to solve that with a produceral-side code. — Elian, Mar 03 '15 at 19:30

score 0 · Accepted Answer · answered Mar 05 '15 at 21:20

SELECT STUFF((SELECT DISTINCT ', ' + SerialNumber
          FROM [vLeasedAsset]
          WHERE A.LeaseID = LeaseID AND A.ProjectNumber = ProjectNumber
          FOR XML PATH (''))
          , 1, 1, '') AS SerialNumber, [ProjectNumber], 
          MAX(ContractComment) 'LeaseContractComment'               
  FROM [vLeasedAsset] A
  WHERE ContractComment != ''
  GROUP BY [ProjectNumber], LeaseID

Output:

SerialNumber

24914, 724993

23401, 720356

ProjectNumber

87

91

Get once time a duplicate record (SQL)

2 Answers2