Shows Blanks for Repeating Values in a Result Set

shows blanks for repeating values in a result set

Wrap your existing query in CTE adding ROW_NUMBER OVER PARTITION BY your columns, which will create RNs for each group of values. In outer query just use CASE to select values where GRP_RN = 1 and empty string otherwise.

WITH CTE AS 
(
    Select  distinct top 100000 
        o.EventSetName,       
        o.EventSetDisplay,
        o.EventSetDescription,
        o.ChildSetName,
        ROW_NUMBER() Over (Order By f.ChildSetName) RN,
        f.DocumentDispSequence,
        f.SectionDispSequence,
        o.ObsSetDispSequence,
        null  as NullColumnNeedsName,
        ROW_NUMBER() OVER (PARTITION BY o.EventSetName, o.EventSetDisplay,o.EventSetDescription ORDER BY f.ChildSetName) GRP_RN
    From   ##ObsSetLevel o,
    INNER JOIN ##Final f ON f.ChildSetName = o.EventSetName and o.EventSetName = @variableName
)
SELECT
    CASE WHEN GRP_RN = 1 THEN o.EventSetName ELSE '' END AS EventSetName,
    CASE WHEN GRP_RN = 1 THEN o.EventSetDisplay ELSE '' END AS EventSetDisplay,
    CASE WHEN GRP_RN = 1 THEN o.EventSetDescription ELSE '' END AS EventSetDescription,
    other columns
FROM CTE  
Order By RN asc, DocumentDispSequence asc, SectionDispSequence asc, o.ObsSetDispSequence asc

PS: I have also corrected your use of old-style joins. That usage is outdated more than 20 years ago with introduction of SQL-92 standards. You should avoid using them.

Duplicate data in unique rows, show blank instead

You can do what you want in the database. However, such presentation considerations are often better done at the application layer. Here is one method:

with bc as (
      SELECT b.title AS Title, c.[Name] AS Char_Name, b.Plot_Summary AS Summary
      FROM Books b INNER JOIN
           Book_Char bc
           ON b.id = bc.Book_id INNER JOIN
           Characters c
           ON c.id = bc.Char_id
     )
select (case when seqnum = 1 then bc.title else '' end) as title, bc.Char_name,
       (case when seqnum = 1 then bc.Summary else '' end) as Summary
from (select bc.*, row_number() over (partition by title order by char_name) as seqnum
      from bc
     ) bc
order by bc.title, bc.char_name;

The real problem with your query, though, is that you are expecting results in a particular order. You only get results in a particular order (guaranteed) when you use order by. And, your query has no order by.

Replace duplicate values only in consecutive records with NULL

In both SQL Server and Oracle you can use LAG analytic function. Oracle:

WITH data (tran_id, tran_name, flag) AS (
    SELECT  01,   'Lend', 'A' FROM DUAL UNION ALL
    SELECT 101,   'Lend', 'B' FROM DUAL UNION ALL
    SELECT 101,   'Lend', 'C' FROM DUAL UNION ALL
    SELECT 101,   'Lend', 'D' FROM DUAL UNION ALL
    SELECT 102, 'Borrow', 'E' FROM DUAL UNION ALL
    SELECT 101,   'Lend', 'F' FROM DUAL UNION ALL
    SELECT 101,   'Lend', 'G' FROM DUAL 
)
SELECT
    NVL2(keep, tran_id, null) tran_id,
    NVL2(keep, tran_name, null) tran_name,
    flag
FROM (
    SELECT
        tran_id, tran_name, flag,
        CASE WHEN LAG(tran_id) OVER (ORDER BY flag) <> tran_id OR LAG(tran_name) OVER (ORDER BY flag) <> tran_name THEN 1 END keep
    FROM
        data)

How to make next repeated values as a blank if the value appears greater then 3 times

FWIW, here is another data.table solution using rleid() instead of duplicated().

Note that the OP requested to make next repeated values as a blank if the value appears greater then 3 times. This implies that for a Value which is repeated exactly two times no blanks should appear in the result. I've amended my sample data set to include the case of exactly two repetitions of the same value.

Edit: The OP hasn't made it clear whether he is counting repetitions of the same Value in the given sequence regardless of Name or if he is counting repetitions in the sequence per Name group. See also this comment.

In addition, the OP hasn't specified what result he expects if there's a sequence of repeated Values but with a change in Name.

Therefore, I've modified my sample data set to include the additional use cases as well:

DT
#    Name Value
# 1:    A    24
# 2:    A    24
# 3:    A    45
# 4:    A    45
# 5:    A    45
# 6:    A    45
# 7:    A    45
# 8:    A    93
# 9:    A    19
#10:    A    19
#11:    A    10
#12:    B    29
#13:    B    67
#14:    B    67
#15:    B    67
#16:    B    67
#17:    C   201
#18:    C   993
#19:    C   396
#20:    A    19
#21:    A    19
#22:    C    19
#23:    B    29
#24:    B    67
#25:    B    67
#26:    B    67
#27:    B    67
#28:    C    67
#29:    C    67
#30:    C    67
#31:    C    67
#    Name Value

As in the other answers, NA is taken for blank.

library(data.table)
setDT(DT)[, New := Value[.N < 3], by=rleid(Value)][rowid(rleid(Value)) == 1L, New := Value]
DT
#    Name Value New
# 1:    A    24  24
# 2:    A    24  24
# 3:    A    45  45
# 4:    A    45  NA
# 5:    A    45  NA
# 6:    A    45  NA
# 7:    A    45  NA
# 8:    A    93  93
# 9:    A    19  19
#10:    A    19  19
#11:    A    10  10
#12:    B    29  29
#13:    B    67  67
#14:    B    67  NA
#15:    B    67  NA
#16:    B    67  NA
#17:    C   201 201
#18:    C   993 993
#19:    C   396 396
#20:    A    19  19
#21:    A    19  NA
#22:    C    19  NA
#23:    B    29  29
#24:    B    67  67
#25:    B    67  NA
#26:    B    67  NA
#27:    B    67  NA
#28:    C    67  NA
#29:    C    67  NA
#30:    C    67  NA
#31:    C    67  NA
#    Name Value New

The first expression copies Value for all RLE groups with one or two repetitions. All RLE groups with more repetitions get NA. The second expressions copies Value only for the first row in each RLE group.

Note that each sequence of repeated values is treated separately regardless of Name but the change of A to C in row 22 and ofB to C in row 27 is being ignored.

This can be further improved to copy only if not already copied:

setDT(DT)[, New := Value[.N < 3], by=rleid(Value)
          ][is.na(New) & rowid(rleid(Value)) == 1L, New := Value]

In case the change in Name is expected to "restart" Value as well this variant could be used (credits to Jaap):

setDT(DT)[, New := Value[.N < 3], by = rleid(Name, Value)
          ][is.na(New) & rowid(rleid(Name, Value)) == 1L, New := Value][]
#    Name Value New
# 1:    A    24  24
# 2:    A    24  24
# 3:    A    45  45
# 4:    A    45  NA
# 5:    A    45  NA
# ...
#18:    C   993 993
#19:    C   396 396
#20:    A    19  19
#21:    A    19  19
#22:    C    19  19
#23:    B    29  29
#24:    B    67  67
#25:    B    67  NA
#26:    B    67  NA
#27:    B    67  NA
#28:    C    67  67
#29:    C    67  NA
#30:    C    67  NA
#31:    C    67  NA
#    Name Value New

Note the difference in rows 21, 22, and 27.

Data

DT <- structure(list(Name = c("A", "A", "A", "A", "A", "A", "A", "A", 
"A", "A", "A", "B", "B", "B", "B", "B", "C", "C", "C", "A", "A", 
"C", "B", "B", "B", "B", "B", "C", "C", "C", "C"), Value = c(24L, 
24L, 45L, 45L, 45L, 45L, 45L, 93L, 19L, 19L, 10L, 29L, 67L, 67L, 
67L, 67L, 201L, 993L, 396L, 19L, 19L, 19L, 29L, 67L, 67L, 67L, 
67L, 67L, 67L, 67L, 67L)), .Names = c("Name", "Value"), row.names = c(NA, 
-31L), class = "data.frame")

Note that rows 1 and 8 have been duplicated w.r.t. the OP's data set to cover the case of exactly two repetitions and that a fews rows have been added at the end.

mysql select to return blanks for all but first row of repeating column

Yes, it's possible, but it would be very expensive (subqueries!) and not at all efficient. Whatever language you use or however you transfer this data directly, it's not worth it to have SQL spare these few bytes.

Shows Blanks for Repeating Values in a Result Set