[RESOLVED] Converting a table of SQL results to a more horizonal layout

[phpSimon]

Hi

I'm creating a CSV file from a table of data produced by a SQL query.

The table of data looks a bit like this:

ID	Client	        Initi	mth	yr	revenue
12345	BIKESUNLIMITED	STA	2	2014	501
12345	BIKESUNLIMITED	STA	3	2014	501
12345	BIKESUNLIMITED	STA	4	2014	501
54321	GARDENERS	FET	3	2014	450
54321	GARDENERS	FET	4	2014	450
54321	GARDENERS	FET	5	2014	450
54321	GARDENERS	FET	6	2014	450
24680	MOTORDEALER	MPA	5	2014	435
24680	MOTORDEALER	MPA	6	2014	435
24680	MOTORDEALER	MPA	7	2014	435
24680	MOTORDEALER	MPA	8	2014	435
24680	MOTORDEALER	MPA	9	2014	435
24680	MOTORDEALER	MPA	10	2014	435
24680	MOTORDEALER	MPA	11	2014	435
24680	MOTORDEALER	MPA	12	2014	435

The resulting CSV file I'm trying to generate needs to show the monthly revenue horizontally, like this:

Client	        Init	02-2014	03-2014	04-2014	05-2014	06-2014	07-2014	08-2014	09-2014	10-2014	11-2014	12-2014
BIKESUNLIMITED	STA	501	501	501								
GARDENERS	FET		450	450	450	450						
MOTORDEALER	MPA				435	435	435	435	435	435	435	435

At the moment I'm writing the SQL output into an array like this:

Array
(
    [0] => Array
        (
            [ID] => 12345
            [ClientID] => BIKESUNLIMITED
            [AccntMgrID] => STA
            [Month] => 2-2014
            [Revenue] => 501
        )

[1] => Array
    (
        [ID] => 12345
        [ClientID] => BIKESUNLIMITED
        [AccntMgrID] => STA
        [Month] => 3-2014
        [Revenue] => 501
    )

[2] => Array
    (
        [ID] => 12345
        [ClientID] => BIKESUNLIMITED
        [AccntMgrID] => STA
        [Month] => 4-2014
        [Revenue] => 501
    )

[3] => Array
    (
        [ID] => 54321
        [ClientID] => GARDENERS
        [AccntMgrID] => FET
        [Month] => 3-2014
        [Revenue] => 450
    )
)

I'm now generating the CSV with a start and end month pre-defined, scanning the array and inserting the revenue value into the appropriate column.

There must be a MUCH easier way of doing this, but I can't see it at the moment. Any suggestions very gratefully received. Part of the difficulty is that the whole process starts with a query that has a start and end month, typically 18 months. The query doesn't return any rows if there's zero revenue in that particular month, so handling that doesn't make it easier.

[dalecosp]

Client	        Init	02-2014	03-2014	04-2014	05-2014	06-2014	07-2014	08-2014	09-2014	10-2014	11-2014	12-2014
BIKESUNLIMITED	STA	501	501	501								
GARDENERS	FET		450	450	450	450						
MOTORDEALER	MPA				435	435	435	435	435	435	435	435

That's not Comma-Separated. Are you sure you don't need to play with tabs and things like [man]str_pad/man? 😕

phpSimon wrote:

There must be a MUCH easier way

Sometimes there is, but in some cases you just buck it up and hope the CPU is big enough 😉

[dalecosp]

Incidentally, as far as the "simplicity" issue, that often has to do with the way the data is structured before you hand it to the report processor.

For example, would it be possible to have the revenue data be keyed to the month?

[0] => Array
        (
            [ID] => 12345
            [ClientID] => BIKESUNLIMITED
            [AccntMgrID] => STA
            [2-2014]=> 501
            [3-2014]=> 501
            [4-2014]=> 501
        )

[phpSimon]

Sometimes there is, but in some cases you just buck it up and hope the CPU is big enough 😉

I'm really glad you said that. Sometimes I sit and stare at something just willing it to be easier, wondering if I'm just not clever enough.

Between posting this and getting back to the board, I made some changes which are really similar to your suggestion (but not quite as good as your layout!)

Here's what I'm doing now:

while(list($ID, $Client, $Exec, $mth, $yr, $revenue) = mssql_fetch_array($result)){

if ($ID == $lastID){ // add to the last ID revenue array
	$list[$ID][Revenue]["$mth-$yr"]=$revenue;

}
else{
$list[$ID] = array("Client" => "$Client",
		        "Exec" => "$Exec",
			"Revenue" => array("$mth-$yr"=>"$revenue"));				
}

$lastID = $ID;	
}

Thanks so much for your help. By the way, that tabbed format was just ASCII art showing the resulting CSV as it would look in Excel... next time I'll show commas!

[johanafm]

Sounds like the standard problem of turning groups of rows into row columns. I believe you'd be able to find plenty of code showing you how to do so, both on this forum and elsewhere. There is no need to modify the initial result set (with revenue gaps).

In short, as you loop over the result set:
1. Compare the "current client" (from the previous result set row) with the current row. If they are not the same, you are about to start a new row. The edge case here is the very first time (current client is null) where you do not have a current row to terminate before starting the new one.

2. output the revenue data on the same line (until the check in 1. triggers a new row)

3. After the last iteration of the loop, you will need to terminate the last row

The only "extra thing" in your problem is the months without revenue. Those are easily handled by

str_repeat(',', 12*(startYear - endYear) + (startMonth - endMonth));

Hints:
* When you start a new row, set currentMonth to minMonth - 1 and currentYear to minYear (where minMonth and minYear are the very first month/year from your report range)
When you output revenue, store its month as currentMonth and its year as currentYear
To terminate a row, compare currentMonth and currentYear vs maxMonth and maxYear (where maxMonth / maxYear is the last date in your report range
- On each iteration, call str_repeat with these values

[Weedpacket]

while(list($ID, $Client, $Exec, $mth, $yr, $revenue) = mssql_fetch_array($result)){

if ($ID == $lastID){ // add to the last ID revenue array
	$list[$ID][Revenue]["$mth-$yr"]=$revenue;

}
else{
$list[$ID] = array("Client" => "$Client",
		        "Exec" => "$Exec",
			"Revenue" => array("$mth-$yr"=>"$revenue"));				
}

$lastID = $ID;	
}

I'd do something similar, I guess, but I would make the revenue entry an array itself (keyed by month):

$daterange = [];
while(list($ID, $Client, $Exec, $mth, $yr, $revenue) = mssql_fetch_array($result))
{
	$daterange[] = "$yr-$mth"; // Note order for sorting
	if(!isset($list[$ID]))
	{
		$list[$ID] = ['Client' => $Client, 'Exec' => $Exec, 'Revenue' => []];
	}
	$list[$ID]['Revenue']["$mth-$yr"] = $revenue;
}
sort($daterange);
$first_date = $daterange[0];
$last_date = $daterange[count($daterange)-1];

Where the stuff involving [font]$daterange[/font] is only there if you don't have a better way of determining the range that the date columns are supposed to span. If you've got that some other way, then said stuff isn't needed.

Then each record in [font=monospace]$list[/font] will contain all the details for a given row of the output. Going through each of those rows, any missing dates in the Revenue array means a blank for that date in the output row.

[phpSimon]

Thank you again, everyone, for some great pointers.

Very much appreciated, as always!