Show Multiple rows of data in line line

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Zofia Kow
    New Member
    • May 2012
    • 4

    Show Multiple rows of data in line line

    I have data in 2 tables (master-detail concept) I want to present results in the query with all details in one row. I'm working in SQL 2005, I do not know number of det per master... Please help...
    Data:
    Master Table: Details Table:
    ID FF1 ID FFd1 FFd2
    1 User1 1 DetB DetA
    2 User2 1 DetC DetH
    2 DetA DetC
    2 DetD DetF
    2 DetG DetH
    Expected result:
    1 User1 DetB DetA DetC DetH
    2 User2 DetA DetC DetD DetF DetG DetH

    Note - thanks to CK9663 for answer re CSV file - it works OK..
  • ck9663
    Recognized Expert Specialist
    • Jun 2007
    • 2878

    #2
    Here, read this.

    Happy Coding!!!


    ~~ CK

    Comment

    • Rabbit
      Recognized Expert MVP
      • Jan 2007
      • 12517

      #3
      Use the FOR XML PATH in a subquery to string it together.

      Code:
      SELECT masterID,
         (
            SELECT detailField + ' '
            FROM detailTable AS d
            WHERE d.foreignID = m.masterID
            FOR XML PATH('')
         ) AS strungTogether
      FROM masterTable AS m

      Comment

      • Zofia Kow
        New Member
        • May 2012
        • 4

        #4
        Thanks - it works but it concatenates all details together; I guess I was not explaining the result clearly - it needs to have each det info as a separate column, but in one record:
        Max # of detailes is 8

        ID User F1 F2 F3 F4 F5 F6 F7 F8
        1 User1 DetB DetA DetC DetH null null null null
        2 User2 DetA DetC DetD DetF DetG DetH null null

        Comment

        • Zofia Kow
          New Member
          • May 2012
          • 4

          #5
          Auch!! This looks complex..... Any way without PIVOT? I find out there is max 8 details. I do not want to link the files 8 times either...

          Comment

          • Rabbit
            Recognized Expert MVP
            • Jan 2007
            • 12517

            #6
            I'm not sure why you don't want to use pivot, it's the preferred method.

            You don't have to link it 8 times, just pivot it.

            Union the two fields, run a ROW_NUMBER() on it, and then pivot.

            Comment

            • Zofia Kow
              New Member
              • May 2012
              • 4

              #7
              I guess its time to learn the PIVOT.... Thanks for help to both of you...

              Comment

              Working...