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

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;