Advenced select

Hi,

I have 3 tables.

1. TableValues with MainID, FieldID and value
2. TableFields with FieldID, FieldName, FieldTypeID
3. TableFieldsTypes with FieldTypeID, TypeName

I want to create select that returns me columns as fields names and values taken from TableValues and types taken from TableFieldsTypes.

How can I do this?

Thank's
Alexei

Select tf.FieldName
tft.fieldType,
tv.[value] AS Value
FROM TableValues tv
INNER JOIN TableFields tf ON (tf.fieldID = tv.fieldID)
INNER JOIN TableFieldsTypes tft ON (tf.FieldTypeID = tft.FieldTypeID)

|||

Hi,

I mean that:
1. In table of fields I have 144 fields.
2. Not to all fields I have the value.
3. In the summary I need table with 144 columns based on fields.

Thank's
Alexei

|||

Switch so you select from the table that contain the rows you must have in the output, left outer join any that might not exist.

Modifying RPaulo's answer a bit, highlighted my changes with bold.

SELECT tf.FieldName
tft.fieldType,
tv.[value] AS Value
FROM TableFields tf
INNER JOIN TableFieldsTypes tft ON (tf.FieldTypeID = tft.FieldTypeID)
LEFT OUTER JOIN TableValues tv ON (tf.fieldID = tv.fieldID)

If TableFieldTypes also does not contain a record for each TableFields record you should change it to an outer join.

|||

Hi,

Try to read the third note. I want 144 columns and that for all of them I will get the values.

For example:

MainID | Column1 | Column2 | .... | Column144
1 | Value 1 | Value 2 | .... | Value 144
2 | Value 1 | Value 2 | .... | Value 144
3 | Value 1 | Value 2 | .... | Value 144
.....

Values doesn't same.

Thank's
Alexei

|||

Do you really need to get the data like that from SQL? Depending on how you are presenting the data this method could also work and is much simpler than creating a query that gives you the results your last post suggests.

Divide it up into 2 queries. 1 to get the column titles with ID and another to get the data sorted by TableValues.MainID, TableValues.FieldID

This sorting will make sure the data appears in the correct orders.

select FieldID, FieldName from TableFields order by FieldID

From this query I would be able to build up the columnheaders

select MainID, FieldID, [Value] from TableValues order by MainID, FieldID

A new MainID creates a new row and add the [Value] to the column that has the same FieldID.

|||

Hi,

I really nead the data like that.

Thank's
Alexei

|||I think you need a crosstab query. You are trying to effectively build schema based on data, which usually involves a crosstab.|||

Something like this:

SELECT MainID, TypeName, f1 AS f1, f2 AS f2, f3 AS f3,f4 AS f4, f5 AS f5, f6 AS f6, f7 AS f7, f8 AS f8, f9 AS f9, f10 AS f10, f11 AS f11, f12 AS f12, f13 AS f13, f14 AS f14
FROM (Select tv.MainID, tft.TypeName, tf.FieldName, tv.[value] AS Value
FROM TableValues tv
LEFT JOIN TableFields tf ON (tf.fieldID = tv.fieldID)
LEFT JOIN TableFieldsType tft ON (tf.FieldTypeID = tft.FieldTypeID)) p
PIVOT (AVG(value) for fieldName IN ([f1], [f2], [f3], [f4], [f5], [f6], [f7], [f8], [f9], [f10], [f11], [f12], [f13], [f14]))
AS PVT
ORDER BY MainID

|||

Hi,

I need something like this. But in T-SQL I don't have the pivot tables.

Thank's
Alexei

|||

Use CASE instead.

SELECT MainID, TypeName,
AVG(CASE WHEN FieldName ='f1' THEN value END) as f1,
AVG(CASE WHEN FieldName ='f2' THEN value END) as f2,
AVG(CASE WHEN FieldName ='f3' THEN value END) as f3,
AVG(CASE WHEN FieldName ='f4' THEN value END) as f4,
AVG(CASE WHEN FieldName ='f5' THEN value END) as f5,
AVG(CASE WHEN FieldName ='f6' THEN value END) as f6,
AVG(CASE WHEN FieldName ='f7' THEN value END) as f7,
AVG(CASE WHEN FieldName ='f8' THEN value END) as f8,
AVG(CASE WHEN FieldName ='f9' THEN value END) as f9,
AVG(CASE WHEN FieldName ='f10' THEN value END) as f10,
AVG(CASE WHEN FieldName ='f11' THEN value END) as f11,
AVG(CASE WHEN FieldName ='f12' THEN value END) as f12,
AVG(CASE WHEN FieldName ='f13' THEN value END) as f13,

AVG(CASE WHEN FieldName ='f14' THEN value END) as f14
FROM (Select tv.MainID, tft.TypeName, tf.FieldName, tv.[value] AS Value
FROM TableValues tv
LEFT JOIN TableFields tf ON (tf.fieldID = tv.fieldID)
LEFT JOIN TableFieldsType tft ON (tf.FieldTypeID = tft.FieldTypeID)) p
WHERE fieldName IN ('f1', 'f2', 'f3', 'f4', 'f5', 'f6', 'f7', 'f8', 'f9', 'f10', 'f11', 'f12', 'f13', 'f14')
GROUP BY MainID, TypeName
ORDER BY MainID

|||

Hi,

I don't want to calculate the average.

Thank's
Alexei

|||Average doesn't really do anything since there will be only one row per group (based on the CASE expression). It is needed for generating the multiple columns within each group. You can use MIN or MAX or any other aggregate function depending on the CASE expression.|||

Hi,

I have tried this but it doesn't work:

SELECT ElevatorID, FieldType,
AVG(CASE WHEN FieldID =1 THEN FieldValue END) as f1,
AVG(CASE WHEN FieldID =2 THEN FieldValue END) as f2,
AVG(CASE WHEN FieldID =3 THEN FieldValue END) as f3,
AVG(CASE WHEN FieldID =4 THEN FieldValue END) as f4
FROM (Select tv.ElevatorID, tft.FieldType, tf.FieldID, tv.FieldValue
FROM TechnicalPage tv
LEFT JOIN TechnicalPageFields tf ON (tf.fieldID = tv.fieldID)
LEFT JOIN TechnicalPageFieldsTypes tft ON (tf.FieldTypeID = tft.FieldTypeID)) p
WHERE FieldType IN (1,2,3,4)
GROUP BY ElevatorID, FieldType
ORDER BY ElevatorID

I get errors:

Server: Msg 409, Level 16, State 2, Line 1
The average aggregate operation cannot take a char data type as an argument.
Server: Msg 409, Level 16, State 1, Line 1
The average aggregate operation cannot take a char data type as an argument.
Server: Msg 409, Level 16, State 1, Line 1
The average aggregate operation cannot take a char data type as an argument.
Server: Msg 409, Level 16, State 1, Line 1
The average aggregate operation cannot take a char data type as an argument.

Thank's
Alexei

|||If FieldValue is character data type then use MIN/MAX aggregate. Usually it is best to post some sample schema and data in your post so that we don't have to guess lot of things like data types, columns, join conditions etc.