James Hughes James Hughes - 1 year ago 72
SQL Question

Turning a field value into a column header - MSAccess

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


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


|t10|HHAPRI |154 |
|t10|LLAPRI |4 |
|t16|HHAPRI |12 |

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

|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
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 Source

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

TRANSFORM SUM(sValue) AS SumOfsValue
FROM Table1
PIVOT Parameter

where Table1 is the name of your original query.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download