Reduce row duplication when multiple values exist in child records

Question

I have a database and need to generate a report from the tables that I have been provided with.

However, the data source query that I am using -while in essence it works fine- is producing duplicates due to the "Parameter" and "Op Parameter" fields (each can be one of two values for each unique tag).

What I would like to do is take each Parameter, each Op Parameter, and their values and tag them on as new fields for the unique tag (e.g., 'AI17611A') so that each tag is only shown once with all parameters.

Is this possible? If so, how would I go about it?

Does [this question](http://stackoverflow.com/q/13278590/2144390) address your issue? — Gord Thompson, Aug 08 '16 at 13:57
Not quite, but it is similar, in that link the code is concatenated into a string. What I would need is for it to be entered into new fields — ScottishTapWater, Aug 08 '16 at 14:01

Gord Thompson · Answer 1 · 2016-08-08T14:29:08.240

If you want a new field for each possible value of [Op Parameter] then you could use an aggregation (GROUP BY) query, e.g., for sample table

[so38830066]

ID  Tag       Parameter  Op Parameter
--  --------  ---------  ------------
 1  AI17611A  HLPR       HLOP        
 2  AI17611A  HLPR       HHAOPT      
 3  AI17611B  HLPR       HLOP        
 4  AI17611C  HLPR       HHAOPT

the query

SELECT 
    so38830066.Tag, so38830066.Parameter, 
    Min([Op Parameter]='HLOP') AS isHLOP, 
    Min([Op Parameter]='HHAOPT') AS isHHAOPT
FROM so38830066
GROUP BY so38830066.Tag, so38830066.Parameter;

produces

Tag       Parameter  isHLOP  isHHAOPT
--------  ---------  ------  --------
AI17611A  HLPR           -1        -1
AI17611B  HLPR           -1         0
AI17611C  HLPR            0        -1

You could do the same thing for multilple values of [Parameter] (e.g., create an [isHLPR] field) and then just GROUP BY [Tag].

Reduce row duplication when multiple values exist in child records

1 Answers1