Display Blank when column values are repeated

Santhosh Kumar Jayaraman

14y2.1k

My stored proc out put is
Week         Name
1220        LOndon
1220        AUstralia
1221        London
1221        Singapore
1221        Zurich

But i dont want to repeat week again in columns. So i am expecting something like below
Week        Name
1220        London
               Australia
1221        London
               SIngapore
               Zurich

I dont want to do this in Excel, i just want to do it in Stored proc output. Can anybody help me??

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nattudurai Eswaramurthy14y ago
Accepted answer
try this hope this will help u

with cte_prod (country_code, product_code, rn)
as
(select country_code,product_code, ROW_NUMBER() OVER (PARTITION BY country_code order by product_code) as rn
from EFLocalProducts )
select
case when rn=1 then country_code else '' end as country_code,
product_code
from cte_prod
0
Santhosh Kumar Jayaraman14y ago
@Nattudurai Worked perfectly.

I have used the following code, to display repeated values blank for multiple columns.

WITH cte_Staff (weekcode,destinationcode,TeacherId,rnWeek,rnDestination)
AS
(SELECT weekcode,destinationcode,TeacherId,
ROW_NUMBER() OVER (PARTITION BY weekcode order by weekcode,destinationcode,weekcode,destinationcode,TeacherId)as rnWeek,
ROW_NUMBER() OVER (PARTITION BY weekcode,DestinationCode
order by weekcode,destinationcode,TeacherId)as rnDestination
FROM Teacher
GROUP BY
WeekCode, Destinationcode,TeacherId)
SELECT
(CASE
WHEN rnweek=1 then weekcode
ELSE ''
END) AS WEEK ,
(CASE
WHEN rnDestination=1 then DestinationCode
ELSE ''
END) AS DESTINATION,
TeacherId AS TEACHERNAME
FROM
cte_Staff

Now my output is like this
WEEK Destination Teacher
1220 London 1234
123678
Sydney 4567
345
34567
1221 Sydney 4567
345
0