Post reply

Query for selecting multiple details values in single row

  • August 27, 2008 at 11:39 pm

    #197589

    Dear All,

    I have a master table called REQUEST. Each request may have been assigned to multiple employees. So I have another detail table where in there will be muliple rows for each Request. (ReqId is the foregin key).

    Now I want to select each Request (from REQUEST Master) with the last column be assigned employee names seperated by commas.

    Eg.

    Req1 12/08/2008 emp1, emp2 ---> From detail table.

    Req2 12/08/2008 emp3, emp4

    Req3 12/08/2008 emp1

    Like this. Now I did it using a function. I passed ReqID to the function which will select empnames from detail table..loop a cursor..make the string of employee names and return.

    Is there a better way to do it..? Can it be done in a single query..?

    Santhu.

  • August 28, 2008 at 1:09 am

    #864160

    nairsanthu1977 (8/27/2008)

    Dear All,

    I have a master table called REQUEST. Each request may have been assigned to multiple employees. So I have another detail table where in there will be muliple rows for each Request. (ReqId is the foregin key).

    Now I want to select each Request (from REQUEST Master) with the last column be assigned employee names seperated by commas.

    Eg.

    Req1 12/08/2008 emp1, emp2 ---> From detail table.

    Req2 12/08/2008 emp3, emp4

    Req3 12/08/2008 emp1

    Like this. Now I did it using a function. I passed ReqID to the function which will select empnames from detail table..loop a cursor..make the string of employee names and return.

    Is there a better way to do it..? Can it be done in a single query..?

    Santhu.

    Hello Santhu

    Yes, it can. This scenario is explored in detail by Jeff Moden in his excellent recent article on cross tabs http://www.sqlservercentral.com/articles/T-SQL/63681/. If you are still unsure about how to proceed after reading the article, then repost with more sample data. This article http://www.sqlservercentral.com/articles/Best+Practices/61537/ will help you set up sample data.

    Cheers

    ChrisM

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply