Results 1 to 13 of 13
  1. #1
    Join Date
    Dec 2009
    Posts
    10

    Unanswered: grouping problem

    Hi guys. is there a way to produce the desired recordset via a query from this table? Ideally I'd like to be able to group the data by 'site' I've used up my limited knowledge of sql to no avail. Thanks David.

    Table

    index Site Wk1 Wk2 Wk3 Wk4
    1 MERCHANT SQUARE LONDON Emp1 NULL NULL NULL
    2 MERCHANT SQUARE LONDON Emp2 NULL NULL NULL
    3 MERCHANT SQUARE LONDON Emp3 NULL NULL NULL
    4 MERCHANT SQUARE LONDON Emp4 NULL NULL NULL
    5 COLTON SQUARE LEICESTER Emp5 NULL NULL NULL
    6 COLTON SQUARE LEICESTER Emp6 NULL NULL NULL
    7 COLTON SQUARE LEICESTER Emp7 NULL NULL NULL
    8 MELTON ROAD Emp8 NULL NULL Emp14
    9 MELTON ROAD Emp9 NULL NULL Emp15
    10 MERCHANT SQUARE LONDON NULL Emp10 NULL NULL
    11 MERCHANT SQUARE LONDON NULL Emp11 NULL NULL
    12 MERCHANT SQUARE LONDON NULL NULL Emp12 NULL
    13 MERCHANT SQUARE LONDON NULL NULL Emp13 NULL

    Desired Recordset

    index Site Wk1 Wk2 Wk3 Wk4
    MERCHANT SQUARE LONDON Emp1 Emp10 Emp12
    MERCHANT SQUARE LONDON Emp2 Emp11 Emp13
    MERCHANT SQUARE LONDON Emp3
    MERCHANT SQUARE LONDON Emp4
    COLTON SQUARE LEICESTER Emp5
    COLTON SQUARE LEICESTER Emp6
    COLTON SQUARE LEICESTER Emp7
    MELTON ROAD Emp8 Emp14
    MELTON ROAD Emp9 Emp15

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT Site
         , COALESCE(MAX(Wk1),'') AS Wk1
         , COALESCE(MAX(Wk2),'') AS Wk2
         , COALESCE(MAX(Wk3),'') AS Wk3
         , COALESCE(MAX(Wk4),'') AS Wk4
      FROM daTable
    GROUP
        BY Site
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oh, wait, nope, that won't do it

    you're just moving stuff around into vacant slots, not grouping (at least, not in the traditional GROUP BY sense of grouping)

    i don't think you can do this with SQL at all
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    davidk1, Something like this might work:
    Code:
    SELECT SITE_CONTROL                                                 AS SITE
         , COALESDE(WEEK1.WK1 , WEEK2.WK2 , WEEK3.WK3 , WEEK4.WK4, ' ') AS WK1
         , COALESDE(WEEK1.WK1 , WEEK2.WK2 , WEEK3.WK3 , WEEK4.WK4, ' ') AS WK2
         , COALESDE(WEEK1.WK1 , WEEK2.WK2 , WEEK3.WK3 , WEEK4.WK4, ' ') AS WK3
         , COALESDE(WEEK1.WK1 , WEEK2.WK2 , WEEK3.WK3 , WEEK4.WK4, ' ') AS WK4
    FROM (SELECT DISTINCT SITE
          FROM table-name
          WHERE WK1 IS NOT NULL
             OR WK2 IS NOT NULL
             OR WK3 IS NOT NULL
             OR WK4 IS NOT NULL
         ) AS SITE_CONTROL
           LEFT OUTER JOIN
           (
            SELECT SITE, WK1
            FROM table-name
            WHERE WK1 IS NOT NULL
           ) AS WEEK1
             ON SITE_CONTROL.SITE = WEEK1.SITE
               LEFT OUTER JOIN
               (
                SELECT SITE, WK2
                FROM table-name
                WHERE WK2 IS NOT NULL
               ) AS WEEK2
                 ON SITE_CONTROL.SITE = WEEK2.SITE
                   LEFT OUTER JOIN
                   (
                    SELECT SITE, WK3
                    FROM table-name
                    WHERE WK3 IS NOT NULL
                   ) AS WEEK3
                     ON SITE_CONTROL.SITE = WEEK3.SITE
                       LEFT OUTER JOIN
                       (
                        SELECT SITE, WK4
                        FROM table-name
                        WHERE WK4 IS NOT NULL
                       ) AS WEEK4
                         ON SITE_CONTROL.SITE = WEEK4.SITE

  5. #5
    Join Date
    Dec 2009
    Posts
    10

    Arrow grouping problem

    Hi guys and thankyou for your replys. Stealth I've tried your solution but i'm getting this error message:

    Server: Msg 207, Level 16, State 3, Line 1
    Invalid column name 'SITE_CONTROL'.

    Could you point me in the right direction again.

    Many thanks
    David

  6. #6
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Sorry, I had a couple of typos. Try Changing this part:
    Code:
    SELECT SITE_CONTROL                                                 AS SITE
         , COALESDE(WEEK1.WK1 , WEEK2.WK2 , WEEK3.WK3 , WEEK4.WK4, ' ') AS WK1
         , COALESDE(WEEK1.WK1 , WEEK2.WK2 , WEEK3.WK3 , WEEK4.WK4, ' ') AS WK2
         , COALESDE(WEEK1.WK1 , WEEK2.WK2 , WEEK3.WK3 , WEEK4.WK4, ' ') AS WK3
         , COALESDE(WEEK1.WK1 , WEEK2.WK2 , WEEK3.WK3 , WEEK4.WK4, ' ') AS WK4
    To:
    Code:
    SELECT SITE_CONTROL.SITE
         , COALESCE(WEEK1.WK1 , WEEK2.WK2 , WEEK3.WK3 , WEEK4.WK4, ' ') AS WK1
         , COALESCE(WEEK1.WK1 , WEEK2.WK2 , WEEK3.WK3 , WEEK4.WK4, ' ') AS WK2
         , COALESCE(WEEK1.WK1 , WEEK2.WK2 , WEEK3.WK3 , WEEK4.WK4, ' ') AS WK3
         , COALESCE(WEEK1.WK1 , WEEK2.WK2 , WEEK3.WK3 , WEEK4.WK4, ' ') AS WK4

  7. #7
    Join Date
    Dec 2009
    Posts
    10

    grouping problem reply

    Hi Stealth, many thanks for the update, i've attached the resultset below, could it be grouped from here to achive the result I'm after?

    SITE WK1 WK2 WK3 WK4
    MERCHANT SQUARE LONDON EMP1 EMP1 EMP1 EMP1
    MERCHANT SQUARE LONDON EMP1 EMP1 EMP1 EMP1
    MERCHANT SQUARE LONDON EMP1 EMP1 EMP1 EMP1
    MERCHANT SQUARE LONDON EMP1 EMP1 EMP1 EMP1
    MERCHANT SQUARE LONDON EMP1 EMP1 EMP1 EMP1
    MERCHANT SQUARE LONDON EMP1 EMP1 EMP1 EMP1
    MERCHANT SQUARE LONDON EMP1 EMP1 EMP1 EMP1
    MERCHANT SQUARE LONDON EMP1 EMP1 EMP1 EMP1
    MERCHANT SQUARE LONDON EMP2 EMP2 EMP2 EMP2
    MERCHANT SQUARE LONDON EMP2 EMP2 EMP2 EMP2
    MERCHANT SQUARE LONDON EMP2 EMP2 EMP2 EMP2
    MERCHANT SQUARE LONDON EMP2 EMP2 EMP2 EMP2
    MERCHANT SQUARE LONDON EMP2 EMP2 EMP2 EMP2
    MERCHANT SQUARE LONDON EMP2 EMP2 EMP2 EMP2
    MERCHANT SQUARE LONDON EMP2 EMP2 EMP2 EMP2
    MERCHANT SQUARE LONDON EMP2 EMP2 EMP2 EMP2
    MERCHANT SQUARE LONDON EMP3 EMP3 EMP3 EMP3
    MERCHANT SQUARE LONDON EMP3 EMP3 EMP3 EMP3
    MERCHANT SQUARE LONDON EMP3 EMP3 EMP3 EMP3
    MERCHANT SQUARE LONDON EMP3 EMP3 EMP3 EMP3
    MERCHANT SQUARE LONDON EMP3 EMP3 EMP3 EMP3
    MERCHANT SQUARE LONDON EMP3 EMP3 EMP3 EMP3
    MERCHANT SQUARE LONDON EMP3 EMP3 EMP3 EMP3
    MERCHANT SQUARE LONDON EMP3 EMP3 EMP3 EMP3
    MERCHANT SQUARE LONDON EMP4 EMP4 EMP4 EMP4
    MERCHANT SQUARE LONDON EMP4 EMP4 EMP4 EMP4
    MERCHANT SQUARE LONDON EMP4 EMP4 EMP4 EMP4
    MERCHANT SQUARE LONDON EMP4 EMP4 EMP4 EMP4
    MERCHANT SQUARE LONDON EMP4 EMP4 EMP4 EMP4
    MERCHANT SQUARE LONDON EMP4 EMP4 EMP4 EMP4
    MERCHANT SQUARE LONDON EMP4 EMP4 EMP4 EMP4
    MERCHANT SQUARE LONDON EMP4 EMP4 EMP4 EMP4
    COLTON SQUARE LEICESTER EMP5 EMP5 EMP5 EMP5
    COLTON SQUARE LEICESTER EMP6 EMP6 EMP6 EMP6
    COLTON SQUARE LEICESTER EMP7 EMP7 EMP7 EMP7
    MELTON ROAD EMP8 EMP8 EMP8 EMP8
    MELTON ROAD EMP9 EMP9 EMP9 EMP9

    (37 row(s) affected)

  8. #8
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Davidk1, before I try something else, I have a question. Do you want any of the 'EMPxx' values shifted from the WKx column they are in. If you happened to have
    Code:
    SITE   WK1   WK2   WK3
    SITE-A NULL  EMP1  NULL
    SITE-A NULL  NULL  EMP2
    Do you want:
    Code:
    SITE   WK1   WK2   WK3
    SITE-A NULL  EMP1  EMP2
    
    or
    
    SITE   WK1   WK2   WK3
    SITE-A EMP1  EMP2  NULL
    PS What Version of SQL Server do you have?

  9. #9
    Join Date
    Dec 2009
    Posts
    10

    grouping problem reply

    Hi Stealth, i'm trying to get to your first example, this one.

    SITE WK1 WK2 WK3
    SITE-A NULL EMP1 EMP2

    Thanks for your help so far, i'm using sql server 2005

    Regards
    David

  10. #10
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    This query gets the desired output.

    This is the starting table:
    Code:
    SELECT *
    FROM table-name
     
    SITE                      WK1   WK2   WK3   WK4
    ------------------------- ----- ----- ----- -----
    MERCHANT SQUARE LONDON    EMP1  NULL  NULL  NULL
    MERCHANT SQUARE LONDON    EMP2  NULL  NULL  NULL
    MERCHANT SQUARE LONDON    EMP3  NULL  NULL  NULL
    MERCHANT SQUARE LONDON    EMP4  NULL  NULL  NULL
    COLTON SQUARE LEICESTER   EMP5  NULL  NULL  NULL
    COLTON SQUARE LEICESTER   EMP6  NULL  NULL  NULL
    COLTON SQUARE LEICESTER   EMP7  NULL  NULL  NULL
    MELTON ROAD               EMP8  NULL  NULL  EMP14
    MELTON ROAD               EMP9  NULL  NULL  EMP15
    MERCHANT SQUARE LONDON    NULL  EMP10 NULL  NULL
    MERCHANT SQUARE LONDON    NULL  EMP11 NULL  NULL
    MERCHANT SQUARE LONDON    NULL  NULL  EMP12 NULL
    MERCHANT SQUARE LONDON    NULL  NULL  EMP13 NULL
    Here is the query that should get you what you want:
    Code:
    SELECT COALESCE(WEEK1.SITE, WEEK2.SITE, WEEK3.SITE, WEEK4.SITE) AS SITE
         , COALESCE(WEEK1.WK1, ' ') AS WK1
         , COALESCE(WEEK2.WK2, ' ') AS WK2
         , COALESCE(WEEK3.WK3, ' ') AS WK3
         , COALESCE(WEEK4.WK4, ' ') AS WK4
    FROM (
          SELECT DENSE_RANK() OVER( PARTITION BY SITE ORDER BY WK1) AS ROWNUM
               , SITE
               , WK1
          FROM table-name
          WHERE WK1 IS NOT NULL
          ) AS WEEK1
            FULL OUTER JOIN
            (SELECT DENSE_RANK() OVER( PARTITION BY SITE ORDER BY WK2) AS ROWNUM
                  , SITE
                  , WK2
             FROM table-name
             WHERE WK2 IS NOT NULL
            ) AS WEEK2
              ON    WEEK1.SITE   = WEEK2.SITE
                AND WEEK1.ROWNUM = WEEK2.ROWNUM
              FULL OUTER JOIN
              (SELECT DENSE_RANK() OVER( PARTITION BY SITE ORDER BY WK3) AS ROWNUM
                    , SITE
                    , WK3
               FROM table-name
               WHERE WK3 IS NOT NULL
              ) AS WEEK3
                ON    WEEK1.SITE   = WEEK3.SITE
                  AND WEEK1.ROWNUM = WEEK3.ROWNUM
                FULL OUTER JOIN
                (SELECT DENSE_RANK() OVER( PARTITION BY SITE ORDER BY WK4) AS ROWNUM
                      , SITE
                      , WK4
                 FROM table-name
                 WHERE WK4 IS NOT NULL
                ) AS WEEK4
                  ON    WEEK1.SITE   = WEEK4.SITE
                    AND WEEK1.ROWNUM = WEEK4.ROWNUM
    And the output from the above query:
    Code:
    SITE                      WK1   WK2   WK3   WK4
    ------------------------- ----- ----- ----- -----
    MERCHANT SQUARE LONDON    EMP1  EMP10 EMP12 
    MERCHANT SQUARE LONDON    EMP2  EMP11 EMP13 
    MERCHANT SQUARE LONDON    EMP3              
    MERCHANT SQUARE LONDON    EMP4               
    COLTON SQUARE LEICESTER   EMP5              
    COLTON SQUARE LEICESTER   EMP6              
    COLTON SQUARE LEICESTER   EMP7              
    MELTON ROAD               EMP8              EMP14
    MELTON ROAD               EMP9              EMP15
    What I needed was a incrementing unique sequence number to identify each duplicate site row for each week. This was needed to allow first non-NULL row from one derived table to join with the first non-NULL row from another derived table (for the same site). This incrementing unique sequence number needed to reset to 1 for each different site. A Full Outer Join is needed because the amount of rows per site each week could be more or less than another week.

    To accomplish this the DENSE_RANK function was used and each week was separated into individual derived tables. The output from each of the derived tables that are then Joined are:
    Code:
    WEEK1
                  ROWNUM SITE_LOC                  WK1
    -------------------- ------------------------- -----
                       1 COLTON SQUARE LEICESTER   EMP5
                       2 COLTON SQUARE LEICESTER   EMP6
                       3 COLTON SQUARE LEICESTER   EMP7
                       1 MELTON ROAD               EMP8
                       2 MELTON ROAD               EMP9
                       1 MERCHANT SQUARE LONDON    EMP1
                       2 MERCHANT SQUARE LONDON    EMP2
                       3 MERCHANT SQUARE LONDON    EMP3
                       4 MERCHANT SQUARE LONDON    EMP4
     
    WEEK2 
                  ROWNUM SITE_LOC                  WK2
    -------------------- ------------------------- -----
                       1 MERCHANT SQUARE LONDON    EMP10
                       2 MERCHANT SQUARE LONDON    EMP11
    
    WEEK3
                   ROWNUM SITE_LOC                  WK3
    -------------------- ------------------------- -----
                       1 MERCHANT SQUARE LONDON    EMP12
                       2 MERCHANT SQUARE LONDON    EMP13
    
    WEEK4 
                  ROWNUM SITE_LOC                  WK4
    -------------------- ------------------------- -----
                       1 MELTON ROAD               EMP14
                       2 MELTON ROAD               EMP15

  11. #11
    Join Date
    Dec 2009
    Posts
    10

    grouping problem reply

    Thank you Stealth for your solution which works perfectly, I may be testing your patience here but it's occurred to me that I will have to run the application on a sql server 2003 platform as well as 2005 and the DENSE_RANK function is I believe unavailable in the 2003 version. Is there an easy alternative to DENSE_RANK that would work in both versions?

    Regards
    David

  12. #12
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    I assume you mean SQL Server 2000 as there is no 2003 (I think there is a Windows Server 2003 but that is not the same thing).

    You are correct, Dense_Rank is not available in 2000. Check out this link. I think it deals with how to get the same results of Dense_Rank in SQL Server 2000:

    SQL Server Code,Tips and Tricks, Performance Tuning: Ranking In SQL Server 2000

  13. #13
    Join Date
    Dec 2009
    Posts
    10
    Thanks Stealth, I'll take a look at the link and take it from there. I appreciate the time and effort you have taken.

    Regards
    David

Posting Permissions

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