Convert Columns to Rows

[Guest]

I've seen several discussions on converting rows into columns. I would like
to do the opposite - convert columns into rows.

Data as is:

Func 001 020
0005 3 7
0010 2 9

Desired Result:

Func Prod Amount
0005 001 3
0005 020 7
0010 001 2
0010 020 9

Is there a way to do this?

 

[Allen Browne]

Sounds like you are trying to normalize the data.

If there are only a few columns, you could use a UNION query:

SELECT Func, "001" AS Prod, [001] AS Amount
FROM Table1
WHERE [001] Is Not Null
UNION ALL
SELECT Func, "020" AS Prod, [020] AS Amount
FROM Table1
WHERE [020] Is Not Null
UNION ALL
SELECT ...

If there are lots of columns, create the table to hold the results you want.
The run a series of Append queries (one per existing column) to populate the
new table.

 

[Guest]

Ironically if I am reading things properly, the way that you want to see the
data is how it should be stored in the table in the first place. Then you
could use a crosstab query to see it as it is now stored!

In your current table are there only two product fields like 001 and 020 or
many such fields? If only a few, you could create queries to first create a
new table then append the data by function and product one at a time. I've
seen code that will do this for a large number of columns but don't remember
where.

 

[Guest]

The irony is apparent. The way I need to see the data is in a normalized
fashion, which is stored in some corporate database somewhere. However, the
query tool given to us is a multidimensional product that will not allow us
to RETRIEVE the data in a normalized fashion. Essbase retrieves data into
Excel in the typical Excel row and column fashion.

I've got over 130 columns here. Is there anything that can be done?

 

[Allen Browne]

Kirk, this is aircode, but see if it illustrates the concept of appending
records, field by field.

It assumes the source table is as you describe, and the target table exists
with the 3 fields you asked for. It loops through the fields from the 2nd
one (the Fields collection is zero-based), inserting records containing the
Func field, then the name of the field, then the value of the field.
Non-normalized data usually contains lots of Nulls, so I assume you want
these filtered out.

Function FixMyData()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strSql As String
Dim strField As String
Dim i As Integer
Const strcSourceTable = "Table1"
Const strcTargetTable = "Table2"

Set db = CurrentDb()
Set tdf = db.TableDefs(strcSourceTable)

For i = 1 To tdf.Fields.Count - 1
strField = tdf.Fields(i)
strSql = "SELECT INTO " & strcTargetTable & _
" (Func, Prod, Amount) SELECT Func, """ & _
strField & """ AS Prod, [" & strField & _
"] AS Amount FROM " & strcSourceTable & _
" WHERE [" & strField & "] Is Not Null;"

db.Execute strSql, dbFailOnError
Debug.Print db.RecordsAffected & " record(s) for " & strField
Next

Set tdf = Nothing
Set db = Nothing
End Function