Advenced select

Advenced select

• Gideon Schlen

  Hi,

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

  Thank's
  Alexei

  
  
  

• srollins

  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

  
  

• BillyJHart

  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.
  
  

• dgrothe

  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

  
  
  

• Patrick Darragh

  Hi,

  I really nead the data like that.

  Thank's
  Alexei

  
  
  

• .Damage Inc.

  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.

  
  
  

• adtjr51

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

• Abulafia

  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

  
  
  

• Brent Yokota

  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.

  
  
  

• Stefan Barthel

  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)

   

  
  
  

• Johnfegkmx36k

  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

  
  

• George Gombo?

  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.
  
  

• slickj

  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

  
  
  

• TrevorL

  Hi,

  I don't want to calculate the average.

  Thank's
  Alexei

  
  
  
• Advenced select