James Hughes James Hughes - 3 months ago 10
SQL Question

Turning a field value into a column header - MSAccess

I have a database where one of the tables has the fields:

|Tag|Parameter|Value|


Where each tag can have several parameters but each parameter only has one value.

eg:

|Tag|Parameter|Value|
|t10|HHAPRI |154 |
|t10|LLAPRI |4 |
|t16|HHAPRI |12 |


How would I got about transforming this, ideally with a query, to something like:

|Tag|HHAPRI|LLAPRI|
|T10|154 |4 |
|T16|12 | |


Where each tag appears only once, with all parameters as column headers and the values entered into the fields.

This is the SQL query that generates the table in the first place
Op Parameter
can be treated in the same was as
Parameter
for this purpose.

SELECT tblAlarmPriorities.*, tblOperatingMode.Parameter AS [Op Parameter], tblOperatingMode.Value AS [Op Value], tblOperatingMode.Correct_Operating_Mode, tblOperatingMode.Alarm_Low_Low, tblOperatingMode.Alarm_Low, tblOperatingMode.Alarm_High_High, tblOperatingMode.Alarm_High, tblLookUp.ParameterDesc AS AlarmParamDesc, tblLookUp_1.ParameterDesc AS OpParamDesc, *
FROM ((tblAlarmPriorities LEFT JOIN tblLookUp ON tblAlarmPriorities.Parameter = tblLookUp.ParameterIdent) INNER JOIN tblOperatingMode ON tblAlarmPriorities.CS_Tag = tblOperatingMode.CS_Tag) LEFT JOIN tblLookUp AS tblLookUp_1 ON tblOperatingMode.PARAMETER = tblLookUp_1.ParameterIdent;

Answer

A cross-tab query will give the results you're after:

TRANSFORM SUM(sValue) AS SumOfsValue
SELECT Tag
FROM Table1
GROUP BY Tag
PIVOT Parameter

where Table1 is the name of your original query.

Comments