Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2010
    Posts
    8

    Unanswered: Transform resultset from vertical to horizontal view

    Hi,

    I need help from gurus here on how to transform a table with parent and child column to a horizontal result. Please refer to example below:

    Code:
    Hierarchy Parent	  Child
    SUNACCT	         *        1100000
    SUNACCT	  1100000	  1110000
    SUNACCT	  1110000	  1111000
    SUNACCT	  1111000	  1111100
    SUNACCT	  1111100	  1111110
    SUNACCT	  1111100	  1111120
    SUNACCT	  1111100	  1111130
    SUNACCT	  1111000	  1111200
    SUNACCT	  1111200	  1111210
    SUNACCT	  1111200	  1111220
    SUNACCT	  1111200	  1111230
    to a horizontal result set below:

    Code:
    Hierarchy  Lvl 1       Lvl 2       Lvl 3       Lvl 4       Lvl 5
    SUNACCT	   1100000     1110000     1111000   1111100	 1111110
    SUNACCT	   1100000     1110000     1111000   1111100	 1111120
    SUNACCT	   1100000     1110000     1111000   1111100	 1111130
    SUNACCT	   1100000     1110000     1111000   1111200	 1111210
    SUNACCT	   1100000     1110000     1111000   1111200	 1111220
    SUNACCT	   1100000     1110000     1111000   1111200	 1111230
    *note: the first line are headers.

    Kindly advise how to transform the result set as shown above. I plan to save the result set to a view so that the structure can be read from an import tool.

    Thanks!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT lvl1.hierarchy
         , lvl1.child AS lvl1
         , lvl2.child AS lvl2
         , lvl3.child AS lvl3
         , lvl4.child AS lvl4
         , lvl5.child AS lvl5
      FROM daTable AS lvl1
    LEFT OUTER
      JOIN daTable AS lvl2
        ON lvl2.parent = lvl1.child
    LEFT OUTER
      JOIN daTable AS lvl3
        ON lvl3.parent = lvl2.child
    LEFT OUTER
      JOIN daTable AS lvl4
        ON lvl4.parent = lvl3.child
    LEFT OUTER
      JOIN daTable AS lvl5
        ON lvl5.parent = lvl4.child
     WHERE lvl1.parent IS NULL
    ORDER 
        BY lvl1.child 
         , lvl2.child 
         , lvl3.child 
         , lvl4.child
         , lvl5.child
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2010
    Posts
    8
    Hi r937,

    Your sql query really works. Thanks!

Posting Permissions

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