Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2011
    Posts
    44

    Unanswered: Pulling data from multiple fields and grouping

    Hi All,

    I have a table of Projects which have multiple Resources.

    PROJ_ID, PROJ_NAME,RESOURCE1,RESOURCE2,RESOURCE3
    01 Project1 001 005 088
    02 Project2 002 004 005


    How can I pull out a list of resources with the projects associated with them?

    ie the above would return

    001 01
    002 02
    004 02
    005 01
    005 02
    008 01

    or



    001 01
    002 02
    004 02
    005 01,02
    008 01

    Thanks for any help in advance

  2. #2
    Join Date
    Sep 2011
    Posts
    44
    Hi All,

    I got this worked out using cursors.

    Fieldnames and tables etc different below than in example

    DECLARE ENG_Cursor CURSOR FOR
    SELECT empref
    FROM ENG_ENG

    OPEN ENG_Cursor;
    FETCH NEXT FROM ENG_Cursor INTO @@ENG_ENG;
    WHILE @@FETCH_STATUS = 0
    BEGIN

    insert into ENG_ENG_PR (ENG_ENG,PROJECTID) select @@ENG_ENG,autokey from ENG_PROJ where RESOURCE01=@@ENG_ENG or RESOURCE02=@@ENG_ENG --etc

    FETCH NEXT FROM ENG_Cursor INTO @@ENG_ENG;
    end
    CLOSE ENG_Cursor;
    DEALLOCATE ENG_Cursor;
    GO

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    First of all, this kind of processing should really be handled by an application or presentation server, not by the database server. If your application ever needs to scale up, this will become a bottleneck that prevents scaling.

    Second, in SQL Server cursors should be a last resort. They perform very poorly compared to set based operations like a SELECT statement.

    Depending on what SQL Server version you are using, the solution varies a bit but there are better answers than using a cursor!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Sep 2011
    Posts
    44
    Thanks Pat,

    It's a relatively small database to I don't expect performance to be an issue.

    When you say there are better options to a cursor what would they be?

    SQL server 2008 is what I am using.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    --  First we set things up
    
    DECLARE @t TABLE (
       PROJ_ID      VARCHAR(9)      NOT NULL
    ,  PROJ_NAME    VARCHAR(9)      NOT NULL
    ,  RESOURCE1    VARCHAR(9)          NULL
    ,  RESOURCE2    VARCHAR(9)          NULL
    ,  RESOURCE3    VARCHAR(9)          NULL
       )
    
    INSERT INTO @t (PROJ_ID, PROJ_NAME
    ,  RESOURCE1, RESOURCE2, RESOURCE3) VALUES
       ('01', 'Project1', '001', '005', '088')
    ,  ('02', 'Project2', '002', '004', '005')
    
    SELECT * FROM @t
    
    --  Then we do the actual task
    
    SELECT RESOURCE1, PROJ_ID FROM @t UNION
    SELECT RESOURCE2, PROJ_ID FROM @t UNION
    SELECT RESOURCE3, PROJ_ID FROM @t
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    This might be another example.

    Code:
    SELECT CASE resource_no
           WHEN 1 THEN resource1
           WHEN 2 THEN resource2
           WHEN 3 THEN resource3
           END  AS resource
         , proj_id
     FROM  @t
     CROSS JOIN
           (VALUES (1) , (2) , (3) ) AS p(resource_no)
    Note:
    The example was not tested.
    But, I thought that the example might conform to syntax of Microsoft SQL Server 2008,
    by looking in FROM (Transact-SQL)
    Last edited by tonkuma; 06-03-14 at 10:53. Reason: Add Note:

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What happens when you need a 4th resource?
    George
    Home | Blog

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by gvee View Post
    What happens when you need a 4th resource?
    The original table only showed three resources. I assume that if another resource was needed that the table would be altered, and if that happened then the conversion SELECT statement would need to be altered to match the new table.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Tonkuma:

    I ran your query and it produced the same rows in the result set, but in a different order than the original specification. In my mind that is close enough, but it might not meet some unstated criteria that depend on order (which could be resolved by an ORDER BY clause).

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by Pat Phelan View Post
    The original table only showed three resources. I assume that if another resource was needed that the table would be altered, and if that happened then the conversion SELECT statement would need to be altered to match the new table.

    -PatP
    My question was more aimed at the OP.
    Their table design is flawed, this is a one-many relationship.
    George
    Home | Blog

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    At least I hope that the reason the OP started this thread was to work on fixing that design problem!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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