Take the 2-minute tour ×
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. It's 100% free, no registration required.

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.

share|improve this question

migrated from superuser.com Aug 2 '12 at 19:27

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

add comment

3 Answers 3

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:

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

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
add comment

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.

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
add comment

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
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
add comment

Your Answer

 
discard

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.