Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Posts
    2

    Unanswered: Remove repeating rows

    I would like to know how to produce the following in a query.

    Parent Child
    ---------------
    Dave Sarah
    .......... Brad
    .......... Alice
    .......... Hanna

    Rather than:

    Parent Child
    ---------------
    Dave Sarah
    Dave Brad
    Dave Alice
    Dave Hanna
    Last edited by DarrylSibson; 04-29-04 at 11:18. Reason: Spaces removed from text

  2. #2
    Join Date
    Nov 2003
    Posts
    94
    create table #t
    (
    [Parent] varchar(50),
    [Child] varchar(50)
    )

    create table #s
    (
    [Parent] varchar(50),
    [Child] varchar(50)
    )

    insert into #t values( 'Dave', 'Sarah' )
    insert into #t values( 'Dave', 'Brad' )
    insert into #t values( 'Dave', 'Alice' )
    insert into #t values( 'Dave', 'Hanna' )

    declare @lastParent varchar(50)
    set @lastParent = ''

    declare @Parent varchar(50)
    declare @Child varchar(50)

    declare @blankLine varchar(50)
    set @blankLine = ''


    declare cur cursor local fast_forward for
    select [Parent], [Child] from #t

    open cur

    fetch next from cur into @Parent, @child

    while @@fetch_status=0
    begin
    insert into #s ( [Parent], [Child] )
    select
    case
    when @Parent = @lastParent
    then @blankLine
    else
    @Parent
    end,
    @Child
    set @lastParent = @Parent

    fetch next from cur into @Parent, @Child
    end

    select * from #s

    close cur
    deallocate cur

    drop table #t
    drop table #s

  3. #3
    Join Date
    Apr 2004
    Posts
    2
    Perhaps I should have mentioned that I need to be able to do this in a Select statement and can't use any variables

  4. #4
    Join Date
    Nov 2003
    Posts
    94
    If you can safely assume that no parent has more than one child with same name (e.g. a newborn child named after a prior deceased sibling) the following construct will work:

    Code:
    create table #t
    (
    	[Parent]	varchar(50),
    	[Child]		varchar(50)
    )
    
    
    insert into #t values( 'Dave', 'Sarah' )
    insert into #t values( 'Dave', 'Brad' )
    insert into #t values( 'Dave', 'Alice' )
    insert into #t values( 'Dave', 'Hanna' )
    insert into #t values( 'Jo', 'Leslie' )
    insert into #t values( 'Jo', 'Kofi' )
    
    
    select 
    	case
    		when [Child] IN	(
    			select top 1 with ties [Child]
    				from #t
    				where [Parent] = T.[Parent]
    				order by [Parent],[Child]
    			)
    		then [Parent]
    		else ''
    	end as [Filtered_Parent],
    	[Child]
    	from #t T
    	order by [Parent],[Child]
    
    
    drop table #t
    Last edited by HanafiH; 04-29-04 at 13:11. Reason: correction

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Do the group suppression on the client, not on the server. It is quite possible on the server (within the SELECT statement), but doing it there it is ugly, wasteful of resources, and pointless when it can be done so easily on the client.

    -PatP

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Absolutely. Unless you are just interested in solving this as an SQL puzzle, most reporting packages (Access, Crystal, etc...) give the option of suppressing duplicated values. This is definitely out of the scope of the database server, and writing code for this functionality is just reinventing the wheel.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •