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: 1004339
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: 246960 More actions · Answer 1

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

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Grant Fritchey SSC Guru Points: 398882 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: 101098 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".