Show only once repeated value in result a set

Question

Post reply

Show only once repeated value in result a set

gaurav

SSC-Addicted

Points: 404

February 11, 2022 at 10:34 am

Go to Answer

#3981874

Hello Everyone,

Here again I came with a question. Below is my query for a report that I want. It's working fine with no issues.

SELECT TP.Location, 
       TE.Name, 
       TEI.imp_amount_approved_by_finance AS Amount, 
       CONVERT(DATE, TEI.imp_fin_appr_date, 103) AS PaidDate
FROM tbl_emp_imprest TEI
     JOIN tbl_Projects TP ON TP.Sno = TEI.imp_emp_location
     JOIN tbl_Employee TE ON TE.Sno = TEI.imp_id
WHERE imp_amount_approved_by_finance > 0
      AND CAST(imp_fin_appr_date AS DATE) BETWEEN CAST('2021-01-01' AS DATE) AND CAST('2021-12-31' AS DATE)
GROUP BY TP.Location, 
         TE.Name, 
         TEI.imp_amount_approved_by_finance, 
         TEI.imp_fin_appr_date;

And the result of the above query as follows in the screen-shot

What I want is; that let's say for 1st row Location and Name( Agai-JSL , Barun Mondal) should come only once for repeated values and repeated values should replaced with blank values.

Kindly suggest.

Thanks

Jeff Moden

SSC Guru

Points: 1003853
More actions
February 11, 2022 at 3:01 pm
Answer
#3981922

I almost always agree with Phil and Grant that this should be done at presentation time in whatever app is generating the final output.
But, then there's the human factors of not having anyone that actually knows how to do such things in the tool they're using to make the final output. A reasonable example of that is how to do it auto-magically in a spreadsheet. Another problem may be that you have no app to produce the final output other than what you can squirt out of SSMS into an email or whatever.
So... I'll make you an offer. It you change the output that you've posted as an otherwise unusable graphic into "readily consumable" data with a CREATE TABLE and INSERT/VALUES so I have a source table to demonstrate code with, I'll show ya how to do it. If you have no clue as to what I'm talking about, study the article at the first link in my signature line below.
Be advised that I'm NOT going to import data from a bloody spreadsheet or file to help you. Please post the data in the form I've asked for.

--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246686 More actions · Answer 1

This sort of thing is best done in the report (ie, at presentation time) rather than in the query.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 2

Yep. Formatting is best done in the client app. Always.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ScottPletcher SSC Guru Points: 100942 More actions · Answer 3

You didn't provide any sample data so I can't test this at all:

SELECT TP.Location, 
       TE.Name, 
       CASE WHEN LAG(Amount, 1) OVER(PARTITION BY TP.Location, TE.Name ORDER BY TEI.imp_fin_appr_date) = TEI.imp_amount_approved_by_finance
            THEN '' ELSE CAST(TEI.imp_amount_approved_by_finance AS varchar(20)) END AS Amount, 
       CONVERT(DATE, TEI.imp_fin_appr_date, 103) AS PaidDate
FROM tbl_emp_imprest TEI
     JOIN tbl_Projects TP ON TP.Sno = TEI.imp_emp_location
     JOIN tbl_Employee TE ON TE.Sno = TEI.imp_id
WHERE imp_amount_approved_by_finance > 0
      AND CAST(imp_fin_appr_date AS DATE) BETWEEN CAST('2021-01-01' AS DATE) AND CAST('2021-12-31' AS DATE)
GROUP BY TP.Location, 
         TE.Name, 
         TEI.imp_fin_appr_date,
         TEI.imp_amount_approved_by_finance
ORDER BY TP.Location, 
         TE.Name, 
         TEI.imp_fin_appr_date;

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.