Questions tagged [pivot]

This tag is for questions that deal with PIVOT syntax which converts row data into columnar data. This technique is commonplace when collecting one-to-many rows from tables such as Joomla's `#__fields_values`.

Filter by
Sorted by
Tagged with
1
vote
2answers
47 views

How to count aggregate data from RSForm submission values with Joomla's query building methods?

user SubmissionId FormId FieldName FieldValue bob 2 16 state mystate1 bob 2 16 qda myqad1 bob 2 16 city mycity1 rob 3 16 state mystate2 rob 3 16 qda myqad1 rob 3 16 city mycity1 jac 4 16 state ...
1
vote
1answer
70 views

RSFORM combine 2 submission form results in one Plotalot view

After requesting help from local db-masters with no result, I would like to ask this question here. The output required is this for example: Shelter ClientID Year Month Fees Amount ...
2
votes
1answer
73 views

PlotALOT RSForm Query showing only one result

After being helped very well by Stack community and learning a bit I created a query to show the sum of payments per client. A client can pay multiple times. I would like to know the sum of payments ...
1
vote
1answer
133 views

Charts/Graphs for RSFORM using mysql for VChart and/or PlotaLot

I am working for a homeless organisation in Capetown. We are using RSForms to register our clients (about 3500 per year). We have a sizable database, but it's very hard to create a dashboard. We are ...
1
vote
1answer
78 views

How to collect multi-row data from #__fields_values and assign specific aliases for each value in the result set?

I have the following (working) query to get name, username and the value of a custom field: // Get a db connection. $db = JFactory::getDbo(); // Create a new query object. $query = $db->getQuery(...
1
vote
2answers
242 views

How to refactor two select queries on RSForm submission data?

I was wondering if anyone could help me JOIN multiple queries into one query. Currently, these 2 blocks of code retrieve information from the DB. I am using the "Sourcerer" plugin to add custom PHP ...
1
vote
2answers
89 views

How to re-write sql for joomla JDatabase

I am new to custom Joomla coding so excuse me for this, I want to execute the following statement: "SELECT MAX(CASE WHEN v.FieldName = 'Team_Name' THEN v.FieldValue END) 'Team_Name' , MAX(...
2
votes
2answers
358 views

How to retrieve RSForm submission data grouped by SubmissionId?

I have a table with 3 columns: SubmissionId, FieldName and FieldValue. 1) SubmissionId keeps ID submission. 2) FieldName stores field name of a form. 3) FieldValue stores field value of form field. ...
2
votes
3answers
276 views

Joomla 3.3.6 Union SQL Issue

I am having trouble using the union functions in Joomla. This is the SQL I need: SELECT * FROM (SELECT o.title AS org_name, u1.name AS username, 'president' AS `type` FROM ...