Transform result from vertical to horizontal view

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Transform result from vertical to horizontal view

Author

Topic

yingchai
Starting Member

33 Posts

Posted - 2011-04-26 : 10:56:43

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!

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-04-26 : 11:04:21

Use PIVOT. Have you actually tried anything yourself?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-26 : 11:10:12

something like

with cte as
(
select lvl = 1, Hierarchy, parent, child from tbl where parent = '*'
union all
select lvl = lvl+1 , Hierarchy, t1.parent, t1.child from cte join tbl t1 on t1.parent = cte.child and t1.Hierarchy = cte.Hierarchy and cte.lvl < 5
)
select l1.Hierarchy, l1.child, l2.child, l3.child,l4.child, l5.child
(select Hierarchy, parent, child from cte where lvl = 1) l1
join (select Hierarchy, parent, child from cte where lvl = 2) l2
on l1.Hierarchy = l2.Hierarchy and l1.chile = l2.parent
join (select Hierarchy, parent, child from cte where lvl = 3) l3
on l1.Hierarchy = l3.Hierarchy and l2.chile = l3.parent
join (select Hierarchy, parent, child from cte where lvl = 4) l4
on l1.Hierarchy = l4.Hierarchy and l3.chile = l4.parent
join (select Hierarchy, parent, child from cte where lvl = 5) l5
on l1.Hierarchy = l5.Hierarchy and l4.chile = l5.parent

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

yingchai
Starting Member

33 Posts

Posted - 2011-04-30 : 09:12:58

Hi RickD,

I did try use PIVOT table but I can't get the results that I want as from the real table, the parent and child can go to many levels and some parents got only one child.

Hi nigelrivett,

I didn't have the chance to test your query but I had found the answer from another forum. Just to share with you guys :)
http://www.dbforums.com/ansi-sql/1666008-transform-resultset-vertical-horizontal-view.html#post6502138

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources