Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2008
    Posts
    49

    Unanswered: Rolling-up Report

    Please give me an idea on how to do this. I need to roll-up data from a certain table. Here's how it goes

    SAMPLE TABLE
    ID COMPANY TELNO ORDERITEMS
    -----------------------------------------
    1 company a 111 111-1111 computer
    2 company b 222 222-2222 computer
    3 company a 111 222-2222 computer
    4 company a 111 333-3333 furnitures

    Now this is suppose to be the result

    COMPANY TELNO MOST_ORDERITEMS ALTERNATE_PHONE
    ----------------------------------------------------------
    company a 111 111-1111 computer 111 222-2222, 111 333-3333
    company b 222 222-2222 computer null

    Basically, I have to distinct the company's name. Their shouldn't be any duplicate company names in the final table output. If ever a certain company have multiple phone numbers (like company a), I must show them all in the certain field (Alternate Phone field for example) If theirs no any other phone then it will stay as null. And then I must also show the most item that the company ordered (company a's most item ordered is computer for example)

    This is only a sample data. I'm actually working on a very long listings of records so its kinda tedious and I don't know how start it. So I just need an idea on how to do this

    Could somebody please help me
    Last edited by BlueGemini; 12-16-08 at 20:53.

  2. #2
    Join Date
    Jun 2008
    Posts
    49

    duplicate shall be put in a field

    ID COMPANY TELNO
    ----------------------
    1 company a 111-1111
    2 company b 111-1111
    3 company c 111-1111

    I wanted to do is if a certain records have the same phone numbers like the sample above, It must only output one record of a company and the other company name will be put in a certain field along with that record. The result must be like this:

    ID COMPANY TELNO OTHER_NAME
    ----------------------------------------------------------
    1 company a 111-1111 company b, company c

    Can somebody please give me an idea on this

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    It's convoluted but it can be done
    Code:
    DECLARE @meh table (
       id               int
     , company          varchar(9)
     , telephone_number varchar(8)
    )
    INSERT INTO @meh (id, company, telephone_number)
          SELECT 1, 'company a', '111-1111'
    UNION SELECT 2, 'company b', '111-1111'
    UNION SELECT 3, 'company c', '111-1111'
    UNION SELECT 4, 'company d', '222-2222'
    
    SELECT m.id
         , m.company
         , m.telephone_number
    FROM   @meh m
     INNER
      JOIN (
            SELECT Min(id) As [min_id]
                 , telephone_number
            FROM   @meh
            GROUP
                BY telephone_number
           ) As [dupes]
        ON m.id = dupes.min_id
    
    ; WITH rank AS (
      SELECT id
           , company
           , telephone_number
           , Row_Number() OVER (PARTITION BY telephone_number ORDER BY id ASC) As [n]
      FROM   @meh
    )
    , cte AS (
      SELECT id
           , n
           , company
           , telephone_number
           , Convert(varchar(max), '') As [other_companies]
      FROM   rank
      WHERE  n = 1
        UNION ALL
          SELECT c.id
               , r.n
               , c.company
               , c.telephone_number
               , c.other_companies + r.company + ','
          FROM   cte c
           INNER
            JOIN rank r
              ON c.telephone_number = r.telephone_number
             AND c.n + 1 = r.n
    )
    
    SELECT c.id
         , c.company
         , c.telephone_number
         , Left(c.other_companies, NullIf(Len(c.other_companies), 0) - 1) As [other_companies]
    FROM   cte c
     INNER
      JOIN (
            SELECT telephone_number
                 , Max(n) As [n]
            FROM   cte
            GROUP
                BY telephone_number
           ) As [x]
        ON c.telephone_number = x.telephone_number
       AND c.n = x.n
    George
    Home | Blog

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Or...
    Code:
    CREATE TABLE dbo.meh (
       id               int
     , company          varchar(9)
     , telephone_number varchar(8)
    )
    INSERT INTO dbo.meh (id, company, telephone_number)
          SELECT 1, 'company a', '111-1111'
    UNION SELECT 2, 'company b', '111-1111'
    UNION SELECT 3, 'company c', '111-1111'
    UNION SELECT 4, 'company d', '222-2222'
    GO
    
    CREATE FUNCTION dbo.concat_companies (
       @telephone_number varchar(8)
     , @delimiter        varchar(5)
    )
    RETURNS varchar(max)
    AS
      BEGIN
        DECLARE @companies varchar(max)
    
        SELECT @companies = Coalesce(@companies, '') + company + @delimiter
        FROM   dbo.meh
        WHERE  telephone_number = @telephone_number
        GROUP
            BY company
        
        SET @companies = Left(@companies, Len(@companies) - DataLength(@delimiter))
        
        RETURN @companies
      END
    GO
    
    SELECT *
         , dbo.concat_companies(telephone_number, ', ')
    FROM   dbo.meh
    
    GO
    DROP FUNCTION dbo.concat_companies
    DROP TABLE dbo.meh
    George
    Home | Blog

  5. #5
    Join Date
    Jun 2008
    Posts
    49
    Sir,

    Its giving an error like this...

    Code:
    Server: Msg 156, Level 15, State 1, Line 15
    Incorrect syntax near the keyword 'WITH'.
    Server: Msg 195, Level 15, State 1, Line 19
    'Row_Number' is not a recognized function name.
    Server: Msg 170, Level 15, State 1, Line 27
    Line 27: Incorrect syntax near 'max'.
    Server: Msg 170, Level 15, State 1, Line 41
    Line 41: Incorrect syntax near ')'.
    I think I don't have a Row_Number function in my MSSQL..

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    See why you should always tell us your SQL Server edition up front?

    The second option should work on 2000
    George
    Home | Blog

  7. #7
    Join Date
    Jun 2008
    Posts
    49
    Quote Originally Posted by georgev

    See why you should always tell us your SQL Server edition up front?

    The second option should work on 2000

    Ah ok... my sql server is 2000 developer's edition...

Posting Permissions

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