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.