Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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

share|improve this question

migrated from Aug 2 '12 at 19:27

This question came from our site for computer enthusiasts and power users.

up vote 2 down vote accepted

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:

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

share|improve this answer
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 Aug 9 '12 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 Aug 9 '12 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 Aug 9 '12 at 18:11
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 Aug 10 '12 at 4:53

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:

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

Test in: SQL-Fiddle

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

share|improve this answer
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 Aug 9 '12 at 14:21
Efficiency depends on the indexes and the size of your table. Get a few working queries and then test. – ypercubeᵀᴹ Aug 9 '12 at 14:25

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.

share|improve this answer
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 Jul 31 '12 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 Aug 1 '12 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 Aug 2 '12 at 19:35

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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