2

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:

enter image description here

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.

3 Answers 3

3

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.)

4
  • I am trying to work through this and translate it to my application right now. The example above was intended to be simplistic and generic, so I am going to have to adapt the logic to my situation. In reality, the FieldNum values aren't sequential, and there is no discernible pattern. I like your idea, though, and might be able to get the front end admin to alter her naming scheme.
    – Pigasus
    Commented Aug 9, 2012 at 14:43
  • Regardless of whether or not I am able to implement your solution, I am voting it the correct answer. Any suggestions if fieldNums are well defined but without a particular pattern?
    – Pigasus
    Commented Aug 9, 2012 at 14:53
  • Eureka! After redesigning the form and swapping the logic, it works perfectly. I didn't explain my situation very well, and I actually need to group by the MOD function and produce columns by the divide function. Thanks for the help!
    – Pigasus
    Commented Aug 9, 2012 at 18:11
  • 1
    Sorry about my not responding. I'm glad you resolved the issue. Not sure if I would be able to suggest anything reasonable if you didn't. I mean, in SQL Server I would try and use ROW_NUMBER(), which is available there since the 2005 version, but as for Sybase, it's probably more recent there and may not be supported by the version you are using. Anyway, it's great you could do without it!
    – Andriy M
    Commented Aug 10, 2012 at 4:53
3

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.

2
  • Does this save any speed? It seems like three joins would take as much computation as three unions. My hope in refactoring is to limit the number of calls to the database. FieldNum values aren't always consecutive, but the values that I'm interesetd in do repeat for each EntityID
    – Pigasus
    Commented Aug 9, 2012 at 14:21
  • 1
    Efficiency depends on the indexes and the size of your table. Get a few working queries and then test. Commented Aug 9, 2012 at 14:25
1

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
3
  • We can think of EntityID as an employee. Each unique EntityID corresponds to a different employee. Each employee owns a form that is comprised of several hundred fields, but each employee's form has the same FieldNums. Each of those FieldNum entries has a corresponding MemoText entry. We can think of FieldNum as the key for calling the MemoText field that I'm interested in. I need the query to create a single row out of specific FieldNum/MemoText pairings, and do that X times for each EntityNum. The resulting table will have X rows for each EntityID and each row will be comprised of MemoText
    – Pigasus
    Commented Jul 31, 2012 at 20:38
  • OK. The crosstab query will display one row for each EntityID. What you are asking to do can only be done using VBA.
    – wbeard52
    Commented Aug 1, 2012 at 1:20
  • The Excel example above is only an exampe. the database is in SQL, and we access it through an ODBC layer. In this case, UNION statements are the only way to combine the data? Won't each UNION call require a new call to the database?
    – Pigasus
    Commented Aug 2, 2012 at 19:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.