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.
Best Answer
If you subtract 1 from
FieldNum
and divide the result by 3 using integral division, you will get the following results forFieldNum
values of 1 till 6:And if you replace
/
with%
(the modulo operator), the results will be like below:Now, in case the idea isn't yet clear, the
(FieldNum - 1) / 3
expression can be used as a grouping criterion in addition toEntityID
(for some reason calledEntityNum
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:
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.)