Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Location
    Germany
    Posts
    17

    Unanswered: Merging Rows in a view

    Hi Im having trouble with this it seems simple enough but its not!

    I have a source Table called Access_table example

    Name Role1 Role2 Role3 Role4 Role5
    a 1 0 0 0 0
    a 0 0 1 0 0
    b 1 0 0 0 0
    c 0 1 0 0 0
    d 0 0 0 0 1
    e 0 0 1 0 0
    e 0 1 0 0 0
    f 1 0 0 0 0
    g 0 0 1 0 0

    I need to create a view that basically finds all the names with double Roles and merge the results into 1 row example.
    Name Role1 Role2 Role3 Role4 Role5
    a 1 0 1 0 0
    e 0 1 1 0 0

    I cannot change the information in the source table and the results need to be in a view as the roles will change. Every time I try and do this I duplicate the row again. Can anybody suggest a solution.

    Thanks in advance.

  2. #2
    Join Date
    Nov 2003
    Posts
    94
    create table #t
    (
    Name nchar(1),
    Role1 int,
    Role2 int,
    Role3 int,
    Role4 int,
    Role5 int
    )

    insert into #t values('a', 1, 0, 0, 0, 0)
    insert into #t values('a', 0, 0, 1, 0, 0)
    insert into #t values('b', 1, 0, 0, 0, 0)
    insert into #t values('c', 0, 1, 0, 0, 0)
    insert into #t values('d', 0, 0, 0, 0, 1)
    insert into #t values('e', 0, 0, 1, 0, 0)
    insert into #t values('e', 0, 1, 0, 0, 0)
    insert into #t values('f', 1, 0, 0, 0, 0)
    insert into #t values('g', 0, 0, 1, 0, 0)

    select [Name], max([Role1]), max([Role2]), max([Role3]), max([Role4]), max([Role5])
    from #t
    group by [Name]
    having count([Name]) > 1

    drop table #t
    Last edited by HanafiH; 04-29-04 at 08:38.

  3. #3
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    23

    Just another solution

    I had done it this way, because I can see also the Names with more than one 1 in a Role Column.

    Create View AName
    As

    Select a.Name, a.Sum(Role1) a.Sum(Role2) a.Sum(Role3) a.Sum(Role4) a.Sum(Role5)
    From Access_table a
    Group By a.Name
    Having Count(a.Name) > 1

    Go

Posting Permissions

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