Transform resultset from vertical to horizontal view

yingchai · #1 (**permalink**) 04-26-11, 10:52

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!

r937 · #2 (**permalink**) 04-26-11, 13:44

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

yingchai · #3 (**permalink**) 04-30-11, 09:08

Hi r937,

Your sql query really works. Thanks!

Internet Services

Web Development

Digital Marketing

Consumer Tech