Refactoring Series of UNIONs in single table (SQL/SYBASE)

Question

I am trying to query a database and reformat the rows/columns that I am interested in. Each row in the current table (EQ) represents a field in a form. In reality, there are several hundred FieldNum values; each EntityID "has" every FieldNum. Each FieldNum has a corresponding MemoText value. In my example below, FieldNum 1,2,3 are related, as are 4,5,6.

EQ and the resulting table should look like:

I need to create a single row out of multiple FieldNum values, but I am unsure how to do this without using UNION ALL. It seems incredibly inefficient to use seven or eight UNION statements, considering all the data I am interested in is located in a single table. Alas, I am unsure how to refactor the code to make it more efficient. A sample of my code is below.

Select En.EntityNum , En.EntityID, 
Test.Date, Test.Name1, Test.Name2

FROM En
JOIN
(SELECT EntityNum,
Max(Case WHEN FieldNum = 1 Then Memotext End) as Date,
Max(Case WHEN FieldNum = 2 Then Memotext End) as Name1,
Max(Case WHEN FieldNum = 3 Then Memotext End) as Name2
FROM EQ  WHERE Group by EntityNum
UNION ALL
(SELECT EntityNum,
Max(Case WHEN FieldNum = 4 Then Memotext End) as Date,
Max(Case WHEN FieldNum = 5 Then Memotext End) as Name1,
Max(Case WHEN FieldNum = 6 Then Memotext End) as Name2
FROM EQ  WHERE 
Group by EntityNum
FROM EQ Group by EntityNum
) as Test ON test.EntityNum = En.EntityNum

I tried to generalize this question as much as possible, but if it is still too specific, a generalized answer would be completely satisfactory.

Answer 1

If you subtract 1 from FieldNum and divide the result by 3 using integral division, you will get the following results for FieldNum values of 1 till 6:

FieldNum | (FieldNum - 1) / 3
---------+-------------------
    1    |         0
    2    |         0
    3    |         0
    4    |         1
    5    |         1
    6    |         1

And if you replace / with % (the modulo operator), the results will be like below:

FieldNum | (FieldNum - 1) % 3
---------+-------------------
    1    |         0
    2    |         1
    3    |         2
    4    |         0
    5    |         1
    6    |         2

Now, in case the idea isn't yet clear, the (FieldNum - 1) / 3 expression can be used as a grouping criterion in addition to EntityID (for some reason called EntityNum in your query). And the results of (FieldNum - 1) % 3 could be used to determine particular rows (would-be columns) within the subgroups defined by (FieldNum - 1) / 3.

Finally, if my attempts at verbal description hasn't really succeeded, here's a query that uses the above two expressions to produce the results you are after:

SELECT
  EntityID,
  MAX(CASE (FieldNum - 1) % 3 WHEN 0 THEN Memotext END) AS Date,
  MAX(CASE (FieldNum - 1) % 3 WHEN 1 THEN Memotext END) AS Name1,
  MAX(CASE (FieldNum - 1) % 3 WHEN 2 THEN Memotext END) AS Name2
FROM EQ
WHERE FieldNum BETWEEN 1 AND 6
GROUP BY
  EntityID,
  (FieldNum - 1) / 3
;

And here you can find a "live" SQL Fiddle demonstration. To be honest, the demo uses SQL Server to run the query, but I would expect Sybase to produce the same results. (I'm assuming that FieldNum is an integer-typed column and that Sybase, like SQL Server, automatically applies integral division when both operands of / are integers.)

Answer 2

Assuming that the rows come in three, with same EntityID and consecutive FieldNum values, and the first row has a date in the MemoText, this will work, too:

SELECT 
    eq0.EntityID
  , eq0.Memotext AS [Date]
  , eq1.Memotext AS Name1
  , eq2.Memotext AS Name2
FROM 
    EQ AS eq0
  JOIN 
    EQ AS eq1 ON  eq1.EntityID = eq0.EntityID
              AND eq1.FieldNum = eq0.FieldNum + 1
  JOIN 
    EQ AS eq2 ON  eq2.EntityID = eq0.EntityID
              AND eq2.FieldNum = eq0.FieldNum + 2
WHERE ISDATE(eq0.Memotext) = 1
ORDER BY 
    EntityId
  , [Date] ;

Test in: SQL-Fiddle

If the FieldNum values are not consecutive, it can solved with ROW_NUMBER() function.

Answer 3

If I understand you correctly. The entity ID is the same for each row and the FieldNum is the column where the data goes. Is that correct?

If so, use a crosstab query.

TRANSFORM FIRST(MemoText) SELECT EntityID FROM EQ PIVOT FieldNum