I want to pivot two tables using SQL Server and PHP.
table 1:
accountname amount dateposted
ab01 100 jan 1, 2022
ab02 100 jan 1, 2022
ab03 100 jan 1, 2023
table 2:
accountname target
ab01 100
ab02 100
ab03 100
output table or pivot table:
accountname jan2022 jan2023 total target percentage
ab01 100 100 100 100%
ab02 100 100 100 100%
ab03 100 100 100 100%
This is tried so far:
$stmt = $database->prepare("SELECT DISTINCT FORMAT(tis_posting_date, 'MMM yyyy') AS month_year
FROM tis_temp_dsr
WHERE tis_posting_date BETWEEN '2022-11-29' AND '2023-03-29'");
$stmt->execute();
$columns_result = $stmt->fetchAll();
$columns = [];
foreach ($columns_result as $row) {
$columns[] = "[" . $row['month_year'] . "]";
}
$column_list = implode(", ", $columns);
$start_date = '2024-12-01';
$end_date = '2025-03-31';
$sql_columns = "
SELECT DISTINCT FORMAT(tis_posting_date, 'MMM yyyy') AS month_year
FROM tis_temp_dsr
WHERE tis_posting_date BETWEEN '$start_date' AND '$end_date'
";
$columns_result = $database->query($sql_columns);
$columns = [];
foreach ($columns_result as $row) {
$columns[] = "[" . $row['month_year'] . "]";
}
$column_list = implode(", ", $columns);
$sql = "
SELECT tis_account_name, $column_list
FROM (
SELECT
tis_account_name,
FORMAT(tis_posting_date, 'MMM yyyy') AS month_year,
tis_amount_with_vat
FROM tis_temp_dsr
WHERE tis_posting_date BETWEEN '$start_date' AND '$end_date'
) AS SourceTable
PIVOT (
SUM(tis_amount_with_vat)
FOR month_year IN ($column_list)
) AS PivotTable
";
$result = $database->query($sql);
I just comment the Total, because there's an error on it.
Thanks in advance.
It worked when using one table, and there's no date range.
I want to pivot two tables using SQL Server and PHP.
table 1:
accountname amount dateposted
ab01 100 jan 1, 2022
ab02 100 jan 1, 2022
ab03 100 jan 1, 2023
table 2:
accountname target
ab01 100
ab02 100
ab03 100
output table or pivot table:
accountname jan2022 jan2023 total target percentage
ab01 100 100 100 100%
ab02 100 100 100 100%
ab03 100 100 100 100%
This is tried so far:
$stmt = $database->prepare("SELECT DISTINCT FORMAT(tis_posting_date, 'MMM yyyy') AS month_year
FROM tis_temp_dsr
WHERE tis_posting_date BETWEEN '2022-11-29' AND '2023-03-29'");
$stmt->execute();
$columns_result = $stmt->fetchAll();
$columns = [];
foreach ($columns_result as $row) {
$columns[] = "[" . $row['month_year'] . "]";
}
$column_list = implode(", ", $columns);
$start_date = '2024-12-01';
$end_date = '2025-03-31';
$sql_columns = "
SELECT DISTINCT FORMAT(tis_posting_date, 'MMM yyyy') AS month_year
FROM tis_temp_dsr
WHERE tis_posting_date BETWEEN '$start_date' AND '$end_date'
";
$columns_result = $database->query($sql_columns);
$columns = [];
foreach ($columns_result as $row) {
$columns[] = "[" . $row['month_year'] . "]";
}
$column_list = implode(", ", $columns);
$sql = "
SELECT tis_account_name, $column_list
FROM (
SELECT
tis_account_name,
FORMAT(tis_posting_date, 'MMM yyyy') AS month_year,
tis_amount_with_vat
FROM tis_temp_dsr
WHERE tis_posting_date BETWEEN '$start_date' AND '$end_date'
) AS SourceTable
PIVOT (
SUM(tis_amount_with_vat)
FOR month_year IN ($column_list)
) AS PivotTable
";
$result = $database->query($sql);
I just comment the Total, because there's an error on it.
Thanks in advance.
It worked when using one table, and there's no date range.
Share Improve this question edited Mar 20 at 8:30 Zhorov 30.1k6 gold badges30 silver badges54 bronze badges asked Mar 20 at 7:10 bricas30bricas30 357 bronze badges 6- it's now edited – bricas30 Commented Mar 20 at 7:29
- This is a bit tricky to get right. What I'd do is instead of pivot the raw table, you instead calculate the Totals and percentage columns using a window function, then it's pivot time where the Totals just become grouped by columns. Also, you can probably just use pure Sql solution, no need to involve php in building the column list – siggemannen Commented Mar 20 at 7:34
- agreed @siggemannen – bricas30 Commented Mar 20 at 7:48
- @Zhorov sql2019,php8, everything is fine, just my query is mess – bricas30 Commented Mar 20 at 7:58
- Do you have to pivot with SQL? – shingo Commented Mar 20 at 8:57
1 Answer
Reset to default 2It looks like a dynamic PIVOT, so a possible approach here is a dynamic statement. A simplified example, without the WHERE clause and based on your test data, is the following statement:
-- Column list
DECLARE @col nvarchar(MAX)
SELECT @col = STUFF(
(
SELECT CONCAT(N',[', FORMAT(month_year, 'MMM yyyy'), N']')
FROM (
SELECT DISTINCT EOMONTH(dateposted) AS month_year
FROM table1
) t
ORDER BY month_year
FOR XML PATH(''), TYPE
).value('.', 'nvarchar(max)'), 1, 1, ''
)
-- Dynamic PIVOT
DECLARE @stm nvarchar(MAX)
DECLARE @err int
SET @stm =
N'SELECT accountname, ' + @col + N', target ' +
N'FROM ('+
N'SELECT t1.accountname, t1.amount, t2.target, FORMAT(dateposted, ''MMM yyyy'') AS month_year ' +
N'FROM table1 t1 ' +
N'LEFT JOIN table2 t2 ON t1.accountname = t2.accountname ' +
N') t ' +
N'PIVOT (SUM(amount) FOR month_year IN (' + @col + ')) p '
EXEC @err = sp_executesql @stm
IF @err <> 0 SELECT 'Error' AS Result
The next step is to execute this statement with PHP. I'm not sure which driver do you use to connect to SQL Server, but the following example demonstrates the solution with PHP Driver for SQL Server. As an additional note, always try to use parameterized statements to prevent possible SQL injection issues.
<?php
$server = 'server\instance,port';
$database = 'database';
$uid = 'uid';
$pwd = 'pwd';
try {
$conn = new PDO("sqlsrv:server=$server;Database=$database", $uid, $pwd);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch( PDOException $e ) {
die( "Error connecting to SQL Server".$e->getMessage());
}
try {
$tsql = "
SET NOCOUNT ON
-- Dates
DECLARE @date1 datetime
DECLARE @date2 datetime
SET @date1 = ?
SET @date2 = ?
-- Column list
DECLARE @col nvarchar(MAX)
SELECT @col = STUFF(
(
SELECT CONCAT(N',[', FORMAT(month_year, 'MMM yyyy'), N']')
FROM (
SELECT DISTINCT EOMONTH(dateposted) AS month_year
FROM table1
WHERE dateposted BETWEEN @date1 AND @date2
) t
ORDER BY month_year
FOR XML PATH(''), TYPE
).value('.', 'nvarchar(max)'), 1, 1, ''
)
-- Dynamic PIVOT
DECLARE @stm nvarchar(MAX)
DECLARE @prm nvarchar(MAX)
DECLARE @err int
SET @stm =
N'SELECT accountname, ' + @col + N', target ' +
N'FROM ('+
N'SELECT t1.accountname, t1.amount, t2.target, FORMAT(dateposted, ''MMM yyyy'') AS month_year ' +
N'FROM table1 t1 ' +
N'LEFT JOIN table2 t2 ON t1.accountname = t2.accountname ' +
N'WHERE dateposted BETWEEN @date1 AND @date2 ' +
N') t ' +
N'PIVOT (SUM(amount) FOR month_year IN (' + @col + ')) p '
SET @prm = N'@date1 datetime, @date2 datetime'
EXEC @err = sp_executesql @stm, @prm, @date1, @date2
IF @err <> 0 SELECT 'Error' AS Result
";
$date1 = '20221201';
$date2 = '20230331';
$stmt = $conn->prepare($tsql);
$stmt->bindParam(1, $date1, PDO::PARAM_STR);
$stmt->bindParam(2, $date2, PDO::PARAM_STR);
$stmt->execute();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
foreach ($row as $name => $value) {
echo $name. ": " . $value . "<br>";
}
echo "<br>";
}
} catch (PDOException $e) {
die ("Error executing query. ".$e->getMessage());
}
$stmt = null;
$conn = null;
?>