April 26, 2011 at 8:47 am
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:
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:
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!
April 27, 2011 at 3:01 am
Hi Yingchai,
It isn't clear at all what the rules of your transformation are. Why do you have 5 columns of output? What does * mean in column 2 of the input? How do you decide what value goes where in the output? Unless you can provide the logic, it will be impossible for anyone to help here.
Also, you'll find people are much more likely to help if you provide sample data in an easily useable format, i.e. providing a CREATE TABLE statement, then a set of INSERT statements to populate the sample data into this table.
Regards, Iain
April 27, 2011 at 3:04 am
Whats wrong with the answers you have already been given ?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=159924
http://www.dbforums.com/ansi-sql/1666008-transform-resultset-vertical-horizontal-view.html
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply