StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Do you have an error in your sample data? I can't see how the hierarchy mapping connects to the hierarchy table to get the results you want, unless the hierarchy mapping is teamid 1 => hierid 2 and teamid 2 => hierid 4.</p> <p>SSIS may not be able to do it (easily), so it may be better to create a OLEDB Source that executes SQL of the following format. Note this does assume you're using SQL Server 2008 as the 'PIVOT' function was introduced there...</p> <pre><code>WITH hier AS ( SELECT parentseqid, sequenceid, hiername as parentname, hiername FROM TeamHierarchy UNION ALL SELECT hier.parentseqid, TH.sequenceid, hier.parentname, TH.hiername FROM hier INNER JOIN TeamHierarchy TH ON TH.parentseqid = hier.sequenceid ), teamhier AS ( SELECT T.*, THM.hierarchyid FROM Teams T INNER JOIN TeamHierarchyMapping THM ON T.teamid = THM.teamid ) SELECT * FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY teamname ORDER BY teamname, sequenceid, parentseqid) AS 'Depth', hier.parentname, teamhier.teamname FROM hier INNER JOIN teamhier ON hier.sequenceid = teamhier.hierarchyid ) as t1 PIVOT (MAX(parentname) FOR Depth IN ([1],[2],[3],[4],[5],[6],[7],[8],[9])) AS pvtTable ORDER BY teamname; </code></pre> <p>There's a few different elements to this, and there <em>may</em> be a better way to do it, but for flattening hierarchies, CTE's are ideal.</p> <p>Two CTEs are created: 'hier' which takes care of flattening the hierarchy and 'teamhier' which is just a helper "view" to make the joins later on simpler. IF you just take the hier CTE and run it, you'll get your flattened view:</p> <pre><code>WITH hier AS ( SELECT parentseqid, sequenceid, hiername as parentname, hiername FROM TeamHierarchy UNION ALL SELECT hier.parentseqid, TH.sequenceid, hier.parentname, TH.hiername FROM hier INNER JOIN TeamHierarchy TH ON TH.parentseqid = hier.sequenceid ) SELECT * FROM hier ORDER BY parentseqid, sequenceid </code></pre> <p>The next part of it basically takes this flattened view, joins it to your team tables (to get the team name) and uses SQL Server's PIVOT to rotate it round and get everything aligned as you want it. More information on PIVOT <a href="http://msdn.microsoft.com/en-us/library/ms177410.aspx" rel="nofollow">is available on the MSDN</a>.</p> <p>If you're using SQL Server 2005, then you can just take the hierarchy flattening bit and you should be able to use SSIS's native 'PIVOT' transformation block to hopefully do the dirty pivoting work.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload