title: How Can I "flatten" Out My TSQL Results?
body: <p><em>Preface: I have looked all over stackoverflow.com and google for this.  I have found hundreds of possible answers but either its not the correct SQL Server version or its not for SQL Server at all and I am not that adept to be able to adapt a query to TSQL for SQL Server 2000.  Most of the examples assume that I would want to do some sort of aggregation - which I do not need to do.  Also, many of the examples assume a fixed number of rows (an in yearly quarters transposing to 4 columns or at the lease a known number.  I will have varying numbers of columns per row.</em></p>
  
  <p>I have a table in SQL Server 2000 that stores words.  It looks like this:</p>
  
  <pre><code>MAPENTRY_ID int NOT NULL
  PARENT_ID int NOT NULL
  ENCODED varchar(10) NOT NULL
  </code></pre>
  
  <p>MAPENTRY_ID is the primary key.  PARENT_ID stores the MAPENTRY_ID of the "parent" word. Its a list of words and since these words are CaSe sensitive, there may be more than one "child" word for a given "parent".  If only one word/case exists (example below is "ABC" then the MAPENTRY_ID will be equal to the PARENT_ID.</p>
  
  <p>Some example data looks like this:</p>
  
  <pre><code>MAPENTRY_ID PARENT_ID ENCODED
  ----------- --------- -------
  8274302     8274302   abaco
  8274306     8274302   Abaco
  8274308     8274302   ABACO
  5217985     5217985   abbynormal
  5217987     5217985   Abbynormal
  5217986     5217985   AbbyNormal
  5217990     5217985   ABBYNORMAL
  9285        9285      ABC
  1144839     1144839   abc123
  1144864     1144839   ABC123
  5129019     5129019   abcapp
  5129020     5129019   AbcApp
  5129021     5129019   ABCAPP
  8329759     8329759   abdominals
  8329757     8329759   Abdominals
  8329761     8329759   ABDOMINALS
  8278875     8278875   abmill
  8278878     8278875   abMill
  8278879     8278875   abMILL
  8278876     8278875   Abmill
  8278877     8278875   AbMill
  8278880     8278875   ABMILL
  5217983     5217983   abnormal
  5217993     5217983   Abnormal
  5217994     5217983   ABNORMAL
  8199838     8199838   aboutcopd
  8199839     8199838   Aboutcopd
  8199841     8199838   AboutCopd
  8199840     8199838   AboutCOPD
  8199845     8199838   ABOUTCOPD
  8199733     8199733   aboutpad
  8199756     8199733   Aboutpad
  8199744     8199733   AboutPad
  8199735     8199733   AboutPAD
  8199765     8199733   ABOUTPAD
  8199767     8199767   aboutrls
  8199768     8199767   Aboutrls
  8199770     8199767   AboutRls
  8199772     8199767   AboutRLS
  8199789     8199767   ABOUTRLS
  8672422     8672422   abroad
  8672423     8672422   Abroad
  8672424     8672422   ABROAD
  8478426     8478426   absecon
  8478525     8478426   Absecon
  8478582     8478426   ABSECON
  8427765     8427765   absinthe
  8427767     8427765   ABSINTHE
  8690704     8690704   absolutely
  8690705     8690704   Absolutely
  8690706     8690704   ABSOLUTELY
  </code></pre>
  
  <p>After reading much material, I tried the following SQL:</p>
  
  <pre><code>SELECT MAPENTRY_ID, PARENT_ID, ENCODED
  FROM XCO_MASTER
  ORDER BY ENCODED, MAPENTRY_ID, PARENT_ID 
  </code></pre>
  
  <p>What it produced was logically correct but looks more like a tree structure. It really doesn't look like more than a good "sort"... A small example is:</p>
  
  <pre><code>MAPENTRY_ID PARENT_ID ENCODED
  ----------- --------- -------
  8274302     8274302   abaco
  8274306     8274302   Abaco
  8274308     8274302   ABACO
  5217985     5217985   abbynormal
  5217987     5217985   Abbynormal
  5217986     5217985   AbbyNormal
  5217990     5217985   ABBYNORMAL
  9285        9285      ABC
  1144839     1144839   abc123
  1144864     1144839   ABC123
  </code></pre>
  
  <p>What I need to see is a result like:</p>
  
  <pre><code>MAPENTRY_ID ENCODED    ENCODED    ENCODED    ENCODED
  ----------- ---------- ---------- ---------- ----------
  8274302     abaco      Abaco      ABACO      
  5217985     abbynormal Abbynormal AbbyNormal ABBYNORMAL
  9285        ABC
  1144839     abc123     ABC123
  </code></pre>
  
  <p>And the reason that I would like it in that order is because I will want to display this information as an HTML table on a web site.  I will not be displaying the entire table, only results from a keyword query that the user will be able to search for.  </p>
  
  <p>Is there any way to do this in SQL Server 2000 using TSQL?   I would rather not parse the results of my query, above, on the client, in order to produce the desired results if it can be done with a specific type of query.</p>