0

I'm trying to do a query that is joining two tables. This join is for a CSV output and requires the values of one table to be the column headings of the CSV with the actual value as values showing 1 or 0.

I can do this by looping through the first table with PHP and doing queries with each iteration of the loop.. but that will be way too hard on the server since we're talking thousands of records. I'm hoping it would be possible with a single query.

As an example, we have two tables. tblSchedule and tblResults

tblSchedule has the following columns..

SID (unique)
NameVal (name of the speaker varchar(250))

tblResults has the following columns..

RID (unique)
SID (foreign key)
FirstName (name of participant varchar(250)))
LastName (name of participant varchar(250)))

Before, all I needed to provide for a CSV output was tblResults and joining tblSchedule.. So participant and speaker name were output.

NOW.. which sucks, is that the want a CSV output with column names for EVERY speaker. The records are items in tblResults, and if that particular participant is seeing that speaker in tblSchedule, to show a 1 in that column, else shows 0.

I have 160 entries in tblSchedule, so that would be 160 columns, and I have about 14,000 entries in tblResults.

I maybe overthinking this a bit.

So the CSV would look something like this...

FirstName, LastName, Speaker1, Speaker2, Speaker3,..... Speaker160
Joe, Schmoe, 0, 0, 1,.....0
Shadow
  • 33,525
  • 10
  • 51
  • 64
hanji
  • 315
  • 2
  • 20
  • All you have to do is alias the column names to get the column headings to change. Get the column names from one query, then build your SQL statement for the CSV output using those values. – Sloan Thrasher Apr 03 '18 at 18:42
  • Add your table schema and a sample query so we can see how you pivot the data. – Sloan Thrasher Apr 03 '18 at 18:43
  • This transformation is called pivoting and has been asked and answered here on SO sooo many times. The linked duplicate topic shows you how to build dynamic pivot tables, where you use an sql statement to construct the pivoting sql statement dynamically. The point is: if you want to do this dynamically, then you cannot do it in a single sql statement. While the duplicate topic shows how to do this transformation within MySQL, it may be a lot more efficient to perform this transformation in the application logic. – Shadow Apr 03 '18 at 18:49
  • Ah.. thanks. Never heard of the pivot before. – hanji Apr 03 '18 at 18:59

0 Answers0