How can I smooth out the results of my TSQL?

Question

Foreword: I went through all of stackoverflow.com and google for this. I found hundreds of possible answers, but either the wrong version of SQL Server, or it is not for SQL Server at all, and I'm not so good as to be able to adapt the query to TSQL for SQL Server 2000. Most of the examples suggest that I would like to do what a cluster that I don't need to do. In addition, many examples assume a fixed number of rows (per year, in which four columns fit or in leasing a known number. I will have a different number of columns per row.

I have a table in SQL Server 2000 that stores words. It looks like this:

MAPENTRY_ID int NOT NULL PARENT_ID int NOT NULL ENCODED varchar(10) NOT NULL 

MAPENTRY_ID is the primary key. PARENT_ID saves the MAPENTRY_ID of the "parent" word. His list of words, and since these words are sensitive to CaSe, there can be more than one “child” word for a given “parent”. If there is only one word / case (the example below is "ABC", then MAPENTRY_ID will be equal to PARENT_ID.

Some example data is as follows:

 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 

After reading a lot of material, I tried the following SQL:

 SELECT MAPENTRY_ID, PARENT_ID, ENCODED FROM XCO_MASTER ORDER BY ENCODED, MAPENTRY_ID, PARENT_ID 

What it produced was logically correct, but more like a tree structure. It really doesn't look like a good sort ... A small example:

 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 

I need to see the result:

 MAPENTRY_ID ENCODED ENCODED ENCODED ENCODED ----------- ---------- ---------- ---------- ---------- 8274302 abaco Abaco ABACO 5217985 abbynormal Abbynormal AbbyNormal ABBYNORMAL 9285 ABC 1144839 abc123 ABC123 

And the reason I would like in that order is because I want to display this information as an HTML table on a website. I will not display the entire table, only the results of the keyword query that the user can find.

Is there a way to do this in SQL Server 2000 using TSQL? I would prefer not to analyze the results of my request, above, on the client, in order to get the desired results, if this can be done using a certain type of request.

Answer 1

Like some comments, I think doing it outside would be better.

However, in TSQL you can do something like this:

 DECLARE @i int SET @i = 1 DECLARE @sql_alter nvarchar(4000) , @sql_update nvarchar(4000) , @sql_select nvarchar(4000) CREATE TABLE #FLAT_TABLE (FID_0 int, FENCODED_0 varchar(10)) SET @sql_select = N'SELECT FID_0, FENCODED_0' INSERT INTO #FLAT_TABLE (FID_0, FENCODED_0) SELECT MAPENTRY_ID , ENCODED FROM XCO_MASTER WHERE MAPENTRY_ID = PARENT_ID while (@@ROWCOUNT > 0) begin SET @sql_select = @sql_select + ', FID_' + @i SET @sql_alter = N' ALTER TABLE #FLAT_TABLE ADD COLUMN FID_' + @i + N' int ALTER TABLE #FLAT_TABLE ADD COLUMN FENCODED_' + @i + N' varchar(10) ' SET @sql_update = N' UPDATE #FLAT_TABLE SET FID_' + @i + N' = MAPENTRY_ID , FENCODED_' + @i + N' = ENCODED FROM XCO_MASTER WHERE MAPENTRY_ID <> PARENT_ID and MAPENTRY_ID = FID_' + (Cast (@i - 1) as nvarchar(8)) ' SET @i = @i + 1 sp_executesql @sql_alter sp_executesql @sql_update end @sql_select = @sql_select + ' FROM #FLAT_TABLE' SELECT @sql_select 

This is just an idea, you will need to make some corrections (e.g. cast @i).

Warning

Be careful with the condition @@ROWCOUNT > 0 , this can lead to an infinite loop if you make a mistake. You can put something like @@ROWCOUNT > 0 and @i < @MAX_COLUMNS to avoid any problems.