StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>here is a T_SQL, assuming this:</p> <ul> <li>You do not know how many columns may appear in the results.</li> <li>The Pivot elements can vary (thats why the first assumption).</li> <li>You need the specific order 'ChildId1, ChilDesc1, ChildId2, ChildDesc2... asd so ever'</li> </ul> <p>DECLARE @MaxCountOfChild int</p> <pre><code>-- Obtaining Maximum times a Master is used by its children SELECT TOP 1 @MaxCountOfChild= count(*) FROM ChildTable GROUP BY MasterID order by count(*) DESC --With that number, create a string for the Pivot elements --if you want them in the order Id1-Desc1-Id2-Desc2 DECLARE @AuxforReplacing nvarchar(MAX), @ChildIdsandDescs nvarchar(MAX), @PivotElements nvarchar(MAX), @Counter int, @sql nvarchar(MAX) SET @Counter=0 SET @AuxforReplacing='' SET @ChildIdsandDescs='' SET @PivotElements='' WHILE (@Counter < @MaxCountOfChild) begin SET @Counter=@Counter +1 SET @PivotElements=@PivotElements + '[' +convert(varchar, @Counter)+ '],' SET @AuxforReplacing=@AuxforReplacing + '[' +convert(varchar, @Counter)+ '] as ' + convert(varchar, @Counter) + ',' SET @ChildIdsandDescs=@ChildIdsandDescs + '[ChildID' + convert(varchar, @Counter)+ '],[ChildDesc' + convert(varchar, @Counter) +'],' end SET @PivotElements=LEFT(@PivotElements, len(@PivotElements)-1) SET @ChildIdsandDescs=LEFT(@ChildIdsandDescs, len(@ChildIdsandDescs)-1) SET @AuxforReplacing=LEFT(@AuxforReplacing, len(@AuxforReplacing)-1) --print REPLACE(@AuxforReplacing, 'as ', 'as ChildId') --print @ChildIds --print @PivotElements SET @sql = N' WITH AuxTable (Masterdesc,ChildId, MasterId,ChildDesc, NumeroenMaster) AS ( SELECT M.Description as MasterDesc, C.*, RANK() OVER (PARTITION BY M.MasterId ORDER BY M.MasterId, ChildId) FROM MasterTable M INNER JOIN ChildTable C ON M.MasterId=C.MasterId ) SELECT TablaMaster.MasterId,' + @ChildIdsandDescs + ' FROM ( SELECT MasterId, ' + REPLACE(@AuxforReplacing, 'as ', 'as ChildId') + ' FROM ( SELECT MasterId, NumeroenMaster, ChildId FROM AuxTable) P PIVOT ( MAX (ChildId) FOR NumeroenMaster IN (' + @PivotElements +') ) AS pvt) As TablaMaster INNER JOIN ( SELECT MasterId, ' + REPLACE(@AuxforReplacing, 'as ', 'as ChildDesc') + ' FROM ( SELECT MasterId, NumeroenMaster, ChildDesc FROM AuxTable) P PIVOT ( MAX (ChildDesc) FOR NumeroenMaster IN (' + @PivotElements +') ) AS pvt) As TablaChild ON TablaMaster.MasterId= TablaChild.MasterId' EXEC sp_executesql @sql </code></pre> <p>EDIT: The result is this: </p> <pre><code>MasterId ChildID1 ChildDesc1 ChildID2 ChildDesc2 ChildID3 ChildDesc3 ChildID4 ChildDesc4 -------- -------- ---------- -------- ----------- -------- ---------- -------- --------- 1 1 Child1 2 Child2 NULL NULL NULL NULL 2 3 Child3 4 Child4 7 Child7 8 Child8 3 5 Child5 6 Child5 NULL NULL NULL NULL Asumming this in the table ChildTable: ChildId MasterId ChildDesc ------- -------- --------- 1 1 Child1 2 1 Child2 3 2 Child3 4 2 Child4 5 3 Child5 6 3 Child5 7 2 Child7 8 2 Child8 </code></pre>

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