Home > Net >  How to "roll-up"/aggregate rows from a badly designed table with SQL?
How to "roll-up"/aggregate rows from a badly designed table with SQL?

Time:09-23

So I'm working with this badly designed table, but it's from another department and I need the data so I have no control over it. How can I get in a proper state? I'm working with MS Access so unfortunately I don't have access to "advanced" SQL functions like partition by, or row_number, etc.

This is the bad table:

KeyID PA QL RuleName
1111 X YYY
1111 X ZZZ
1111 X OOO

I want my final table to look like this:

KeyID PA QL RuleNamePA RuleNameQL RuleNameQL2
1111 X X YYY ZZZ OOO

Any help would be appreciated!

CodePudding user response:

Your desired output is not really a 'proper state' any more than the original data structure is - it's worse. Since the PA and QL fields appear to be Yes/No type and mutually exclusive, a properly normalized table would be like:

ID KeyID Cat RuleName
1 1111 PA yyy
2 1111 QL zzz
3 1111 QL ooo

That structure can be achieved with:

SELECT ID, KeyID, IIf(PA, "PA", "QL") AS Cat, RuleName FROM Table2;

Then that query can be used like a table in subsequent queries such as a CROSSTAB:

TRANSFORM First(RuleName) AS FirstOfRuleName
SELECT KeyID
FROM Query1
GROUP BY KeyID
PIVOT "RuleName" & [Cat] & DCount("*","Query1","Cat='" & [Cat] & "' AND ID<" & [ID]) 1;

If there is no unique identifier ID field, change the DCount to:
DCount("*","Query1","Cat='" & [Cat] & "' AND KeyID & Cat & RuleName<'" & [KeyID] & [Cat] & [RuleName] & "'") 1

Here is a version that does not use Query1 and will provide the PA and QL fields which really don't seem necessary. Again, adjust the DCount if there is no ID field.

TRANSFORM First(RuleName) AS FirstOfRuleName
SELECT Table1.KeyID, Max(Abs([PA])) AS P, Max(Abs([QL])) AS Q
FROM Table1
GROUP BY KeyID
PIVOT "RuleName" & IIf([PA],"PA","QL") & DCount("*","Table1","IIf([PA],'PA','QL')='" & IIf([PA],"PA","QL") & "' AND ID<" & [ID]) 1;
  • Related