topic-30600

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Advance SQL-query question
Posted: Sun Apr 26, 2015 09:38 AM

Hi,

I now this is not a SQL-forum, but since a lott off users use SQL, mayme somone know the solution.

I have table like this

Code (fw): Select all Collapse

Name    Parameter   Value
----    ---------   -----
Name1   Parmeter1   Value11
Name1   Parmeter2   Value21
Name1   Parmeter3   Value31
Name1   Parmeter4   Value41
Name1   Parmeter5   Value51
Name1   Parmeter6   Value61
...
...
Name1   Parmeter100 Value1001
Name2   Parmeter1   Value12
Name2   Parmeter2   Value22
Name2   Parmeter3   Value32
Name2   Parmeter4   Value42
Name2   Parmeter5   Value52
Name2   Parmeter6   Value62
...
...
Name2   Parmeter100 Value1002
Name3   Parmeter1   Value13
Name3   Parmeter2   Value23
Name3   Parmeter3   Value33
Name3   Parmeter4   Value43
Name3   Parmeter5   Value53
Name3   Parmeter6   Value63
...
...
Name3   Parmeter100 Value1003
....
....

Now I want to create a browse where I can show for example all parameter1,parameter2,parameter5 of all names like this.

Code (fw): Select all Collapse

Name    Parameter1  Parameter2  Parameter5
----    ----------  ----------  ----------
Name1   Value11     Value21     Value51 
Name2   Value12     Value22     Value52 
Name3   Value13     Value23     Value53
...
...

Is is possible with SQL to do this in one SELECT command?

Now I use a function that loop all parameters for each name, and put it in a temperory table.
Then I show this table.
This is taking a little time when I have a very big table.

Thanks

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Advance SQL-query question
Posted: Sun Apr 26, 2015 11:05 AM

viewtopic.php?f=3t=30530

&

Regards

G. N. Rao.

Hyderabad, India

Marc Vanzegbroeck

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: Advance SQL-query question
Posted: Sun Apr 26, 2015 12:06 PM

Rao,

What is the query that used for the pivot view?
I don't want to calculate the sum om the fields. For each name there is one time a field with parameter1, and I want to show the field in Value1 for that name.

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Advance SQL-query question
Posted: Sun Apr 26, 2015 12:21 PM

I don't want to calculate the sum om the fields. For each name there is one time a field with parameter1, and I want to show the field in Value1

The functionality is not limited to SUM alone. SUM, AVG, MAX, MIN,STD etc
MAX of a single value is the same value. Isn't it?
Source of this function is in adofuncs.prg

Code (fw): Select all Collapse

SELECT NAME,
( CASE WHEN PARAMETER = 'Parameter1' THEN PARAMETER ELSE NULL END ) AS PARAMETER1,
( CASE WHEN PARAMETER = 'Parameter2' THEN PARAMETER ELSE NULL END ) AS PARAMETER2,
( CASE WHEN PARAMETER = 'Parameter3' THEN PARAMETER ELSE NULL END ) AS PARAMETER3,
( CASE WHEN PARAMETER = 'Parameter4' THEN PARAMETER ELSE NULL END ) AS PARAMETER4,
( CASE WHEN PARAMETER = 'Parameter5' THEN PARAMETER ELSE NULL END ) AS PARAMETER5,
 .......... ETC ..........
( CASE WHEN PARAMETER = 'ParameterN' THEN PARAMETER ELSE NULL END ) AS PARAMETERN
FROM MYTABLE

Regards

G. N. Rao.

Hyderabad, India

Marc Vanzegbroeck

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: Advance SQL-query question
Posted: Sun Apr 26, 2015 01:06 PM

Roa,

Thank you, it's working now

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Advance SQL-query question
Posted: Sun Apr 26, 2015 01:10 PM

Is pivot table working for you?

Regards

G. N. Rao.

Hyderabad, India

Marc Vanzegbroeck

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: Advance SQL-query question
Posted: Sun Apr 26, 2015 01:25 PM

Yes it's working! Thank you very much!

I use

Code (fw): Select all Collapse

SELECT NAME,
( max(if(PARAMETER = 'Parameter1',VALUE,NULL)) AS PARAMETER1,

 .......... ETC ..........

FROM MYTABLE GROUP BY name

The case when doesn't work in MySQL

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Advance SQL-query question
Posted: Sun Apr 26, 2015 01:42 PM

CASE works in MySql too.
It is not working in ADO for DBase and MsAccess only.

Regards

G. N. Rao.

Hyderabad, India

Marc Vanzegbroeck

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: Advance SQL-query question
Posted: Sun Apr 26, 2015 02:24 PM

I have tried the query in HeidiSQL with MariaDB, and get an error. I will take a look at it again.

FiveTech Support Forums

Continue the discussion