Using PIVOT and JOIN together
WITH T
AS (SELECT [Order Details].OrderID,
c.CategoryName,
c.CategoryID
FROM [Order Details]
INNER JOIN Products p
ON p.ProductID = [Order Details].ProductID
INNER JOIN Categories c
ON c.CategoryID = p.CategoryID)
SELECT *
FROM T PIVOT ( COUNT (CategoryID) FOR CategoryName IN (
[Beverages],
[Condiments],
[Confections],
[Dairy Products],
[Grains/Cereals],
[Meat/Poultry],
[Produce],
[Seafood]) ) AS pvt
ORDER BY OrderID
SQL server join tables and pivot
This should work:
WITH Sales AS (
SELECT
S.SaleID,
S.SoldBy,
S.SalePrice,
S.Margin,
S.Date,
I.SalePrice,
I.Category
FROM
dbo.Sale S
INNER JOIN dbo.SaleItem I
ON S.SaleID = I.SaleID
)
SELECT *
FROM
Sales
PIVOT (Max(SalePrice) FOR Category IN (Books, Printing, DVD)) P
;
Or alternately:
SELECT
S.SaleID,
S.SoldBy,
S.SalePrice,
S.Margin,
S.Date,
I.Books,
I.Printing,
I.DVD
FROM
dbo.Sale S
INNER JOIN (
SELECT *
FROM
(SELECT SaleID, SalePrice, Category FROM dbo.SaleItem) I
PIVOT (Max(SalePrice) FOR Category IN (Books, Printing, DVD)) P
) I ON S.SaleID = I.SaleID
;
These have the same result set and may in fact be treated the same by the query optimizer, but possibly not. The big difference comes into play when you start putting conditions on the Sale table—you should test and see which query works better.
Note: it is crucial when using PIVOT that only the columns that should be part of the resulting output are available. This is why the two above queries have extra derived table subqueries (SELECT ...) so that only specific columns are exposed. All columns that are available to be seen by PIVOT that aren't listed in the pivot expression will implicitly be grouped on and included in the final output. This will likely not be what you want.
May I suggest, however, that you do the pivoting in the presentation layer? If, for example, you are using SSRS it is quite easy to use a matrix control that will do all the pivoting for you. That is best, because then if you add a new Category, you won't have to modify all your SQL code!
There is a way to dynamically find the column names to pivot, but it involves dynamic SQL. I don't really recommend that as the best way, either, though it is possible.
Another way that could work would be to preprocess this query—meaning to set a trigger on the Category table that rewrites a view to contain all the extant categories that exist. This does solve a lot of the other problems I've mentioned, but again, using the presentation layer is best.
Note: If your column names (that were formerly values) have spaces, are numbers or begin with a number, or are otherwise not valid identifiers, you must quote them with square brackets as in PIVOT (Max(Value) FOR CategoryId IN ([1], [2], [3], [4])) P. Alternately, you can modify the values before they get to the PIVOT part of the query to prepend some letters or remove spaces, so that the column list doesn't need escaping. For further reading on this check out the rules for identifiers in SQL Server.
How to use PIVOT and JOIN together in SQL Server?
You can simply perform a join in the source table of the PIVOT:
Select ItemID, ItemPartNumber, ItemDescription, CreatedDate, InitialPrice,
[HP] As HPPrice,
[Apple] As ApplePrice,
[Microsoft] As MicrosoftPrice,
[IBM] As IBMPrice
from (
select v.ItemID, VendorName, VendorPrice,
ItemPartNumber, ItemDescription, CreatedDate, InitialPrice
from VendorItemPricing as v
left join MasterItems as m on v.ItemID = m.ItemID
where v.ItemID = 122)A
PIVOT(
MAX(VendorPrice)
FOR VendorName IN ([HP],[Apple],Microsoft,IBM)
)P
SQL Fiddle Demo
How to join two dynamic pivot(table data after pivot) in SQL Server
Honestly, this should be something for your presentation layer not the SQL layer; especially as you want merged cells (a concept that does not exist in SQL).
I would personally switch over to conditional aggregation, rather than the restrictive PIVOT operator, and then do something like this:
DECLARE @SQL nvarchar(MAX),
@CRLF nchar(2) = NCHAR(13) + NCHAR(10);
DECLARE @Delimiter nchar(3) = N',' + @CRLF;
WITH DateRanges AS(
SELECT DISTINCT
DATEADD(MONTH, DATEDIFF(MONTH,0,CreatedOn),0) AS StartDate,
DATEADD(MONTH, DATEDIFF(MONTH,0,CreatedOn)+1,0) AS EndDate
FROM dbo.TblDemoData)
SELECT @SQL = N'SELECT DD.AgentID,' + @CRLF +
STRING_AGG(N' COUNT(CASE WHEN DD.CreatedOn >= ' + QUOTENAME(CONVERT(varchar(8),DR.StartDate,112),'''') + N' AND DD.CreatedOn < ' + QUOTENAME(CONVERT(varchar(8),DR.EndDate,112),'''') + N' THEN 1 END) AS ' + QUOTENAME(CONCAT(YEAR(DR.StartDate),'-',DATENAME(MONTH,DR.StartDate),N'-Audits')) + N',' + @CRLF +
N' SUM(CASE WHEN DD.CreatedOn >= ' + QUOTENAME(CONVERT(varchar(8),DR.StartDate,112),'''') + N' AND DD.CreatedOn < ' + QUOTENAME(CONVERT(varchar(8),DR.EndDate,112),'''') + N' THEN AchievedScore* 1. ELSE 0 END) / ' + @CRLF +
N' SUM(CASE WHEN DD.CreatedOn >= ' + QUOTENAME(CONVERT(varchar(8),DR.StartDate,112),'''') + N' AND DD.CreatedOn < ' + QUOTENAME(CONVERT(varchar(8),DR.EndDate,112),'''') + N' THEN MaximumScore ELSE 0 END) AS ' + QUOTENAME(CONCAT(YEAR(DR.StartDate),'-',DATENAME(MONTH,DR.StartDate),N'-Score')),@Delimiter) WITHIN GROUP (ORDER BY DR.StartDate) + @CRLF +
N'FROM dbo.TblDemoData DD' + @CRLF +
N'GROUP BY DD.AgentId;'
FROM DateRanges DR;
--PRINT @SQL; Your debugging best friend
EXEC sys.sp_executesql @SQL;
Note this doesn't give the columns in the order you asked, however, the order of the columns is meaningless, and (again) should be controlled in your presentation layer.
SQL combine 2 table and pivot
In order to get your final result, you are going to have to implement a variety of methods including unpivot, pivot, along with the use of a windowing function like row_number().
Since you have multiple columns in Table2 that need to be pivoted, then you will need to unpivot them first. This is the reverse of pivot, which converts your multiple columns into multiple rows. But before you unpivot, you need some value to identify the values of each row using row_number() - sounds complicated, right?
First, query table2 using the windowing function row_number(). This creates a unique identifier for each row and allows you to easily be able to associate the values for id_1 from any of the others.
select serie_id, l_value, lrms, latTmax, Rdc,
rn = cast(row_number() over(partition by serie_id order by serie_id)
as varchar(10))
from table2;
See Demo. Once you've created this unique identifier, then you will unpivot the L_value, lrms, latTmax, and rdc. You can unpivot the data using several different methods, including the unpivot function, CROSS APPLY, or UNION ALL.
select serie_id,
col, value
from
(
select serie_id, l_value, lrms, latTmax, Rdc,
rn = cast(row_number() over(partition by serie_id order by serie_id)
as varchar(10))
from table2
) d
cross apply
(
select 'L_value_'+rn, L_value union all
select 'lrms_'+rn, lrms union all
select 'latTmax_'+rn, latTmax union all
select 'Rdc_'+rn, Rdc
) c (col, value)
See SQL Fiddle with Demo. The data from table2 is not in a completely different format that can be pivoted into the new columns:
| SERIE_ID | COL | VALUE |
|----------|-----------|-------|
| id_1 | L_value_1 | 67 |
| id_1 | lrms_1 | 400 |
| id_1 | latTmax_1 | 400 |
| id_1 | Rdc_1 | 0.25 |
| id_1 | L_value_2 | 90 |
| id_1 | lrms_2 | 330 |
| id_1 | latTmax_2 | 330 |
| id_1 | Rdc_2 | 0.35 |
The final step would be to PIVOT the data above into the final result:
select serie_id, maturity, strategy, lifetime, l_max, w_max, h_max,
L_value_1, lrms_1, latTmax_1, Rdc_1,
L_value_2, lrms_2, latTmax_2, Rdc_2,
L_value_3, lrms_3, latTmax_3, Rdc_3,
L_value_4, lrms_4, latTmax_4, Rdc_4
from
(
select t1.serie_id, t1.maturity, t1.strategy, t1.lifetime,
t1.l_max, t1.w_max, t1.h_max,
t2.col, t2.value
from table1 t1
inner join
(
select serie_id,
col, value
from
(
select serie_id, l_value, lrms, latTmax, Rdc,
rn = cast(row_number() over(partition by serie_id order by serie_id)
as varchar(10))
from table2
) d
cross apply
(
select 'L_value_'+rn, L_value union all
select 'lrms_'+rn, lrms union all
select 'latTmax_'+rn, latTmax union all
select 'Rdc_'+rn, Rdc
) c (col, value)
) t2
on t1.serie_id = t2.serie_id
) d
pivot
(
max(value)
for col in (L_value_1, lrms_1, latTmax_1, Rdc_1,
L_value_2, lrms_2, latTmax_2, Rdc_2,
L_value_3, lrms_3, latTmax_3, Rdc_3,
L_value_4, lrms_4, latTmax_4, Rdc_4)
) p;
See SQL Fiddle with Demo.
If you have an unknown number of values in Table2 then you will need to use dynamic SQL to create a sql string that will be executed. Converting the above code to dynamic sql is pretty easy once you have the logic correct. The code will be:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols
= STUFF((SELECT ',' + QUOTENAME(col+cast(rn as varchar(10)))
from
(
select rn = cast(row_number() over(partition by serie_id order by serie_id)
as varchar(10))
from table2
) d
cross apply
(
select 'L_value_', 0 union all
select 'lrms_', 1 union all
select 'latTmax_', 2 union all
select 'Rdc_', 3
) c (col, so)
group by col, rn, so
order by rn, so
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = N'SELECT serie_id, maturity, strategy, lifetime, l_max,
w_max, h_max,' + @cols + N'
from
(
select t1.serie_id, t1.maturity, t1.strategy, t1.lifetime,
t1.l_max, t1.w_max, t1.h_max,
t2.col, t2.value
from table1 t1
inner join
(
select serie_id,
col, value
from
(
select serie_id, l_value, lrms, latTmax, Rdc,
rn = cast(row_number() over(partition by serie_id order by serie_id)
as varchar(10))
from table2
) d
cross apply
(
select ''L_value_''+rn, L_value union all
select ''lrms_''+rn, lrms union all
select ''latTmax_''+rn, latTmax union all
select ''Rdc_''+rn, Rdc
) c (col, value)
) t2
on t1.serie_id = t2.serie_id
) x
pivot
(
max(value)
for col in (' + @cols + N')
) p '
exec sp_executesql @query
See SQL Fiddle with Demo
Both versions will give a result of:
| SERIE_ID | MATURITY | STRATEGY | LIFETIME | L_MAX | W_MAX | H_MAX | L_VALUE_1 | LRMS_1 | LATTMAX_1 | RDC_1 | L_VALUE_2 | LRMS_2 | LATTMAX_2 | RDC_2 | L_VALUE_3 | LRMS_3 | LATTMAX_3 | RDC_3 | L_VALUE_4 | LRMS_4 | LATTMAX_4 | RDC_4 |
|----------|----------|----------|----------|-------|-------|-------|-----------|--------|-----------|-------|-----------|--------|-----------|--------|-----------|--------|-----------|--------|-----------|--------|-----------|--------|
| id_1 | 3 | 1 | 2 | 2.2 | 1.4 | 1.4 | 67 | 400 | 400 | 0.25 | 90 | 330 | 330 | 0.35 | 120 | 370 | 370 | 0.3 | 180 | 330 | 300 | 0.35 |
| id_2 | 3 | 1 | 2 | 3.4 | 1.8 | 2.1 | 260 | 300 | 300 | 0.4 | 360 | 280 | 280 | 0.45 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
| id_3 | 3 | 1 | (null) | 24.5 | 14.5 | 15 | 90 | 370 | 370 | 0.3 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
| id_4 | 3 | 1 | (null) | 28 | 24.5 | 14 | 160 | 340 | 340 | 0.4 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
How to join two tables with a pivot table using Laravel?
A pivot table is a structure used to join two separate models together with a single relationship. This is called a many-to-many relationship in Eloquent.
From what you've described, this is not the case here. Rather, it looks like a has-many-through relationship.
If I'm understanding correctly, your relationships should look like this:
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class Tbl_Lista_Contactabilidad extends Model {
protected $table = 'tbl_lista_contactabilidad';
public function postventaatc() {
return $this->belongsTo(Tbl_EquipoPostventaatc::class, 'usuarios_id');
}
}
class Tbl_EquipoPostventaatc extends Model {
protected $table = 'tbl_equipo_postventaatc';
public function contactabilidad() {
return $this->hasMany(Tbl_Lista_Contactabilidad::class, 'usuarios_id');
}
}
class User extends Model {
public function postventaatc() {
return $this->belongsTo(Tbl_EquipoPostventaatc::class, 'asesor_id');
}
public function contactabilidad() {
return $this->hasManyThrough(Tbl_Lista_Contactabilidad::class, Tbl_EquipoPostventaatc::class, 'asesor_id', 'usuarios_id');
}
}
Obviously this is easier for a native English speaker, but I cannot stress how much easier this would be if you were following the Laravel rules around naming your models, tables, and columns. Why does usuarios_id column relate to a table called tbl_equipo_postventaatc? Why use asesor_id instead of user_id? ♂️ Those names have nothing to do with each other, and make it hard to figure out what is going on.
SQL - Combining Dynamic SQL with Pivot and Full Join
What you can do:
- Rewrite
@pSQLto not use a WITH clause (it is superfluous) - Generate the PIVOT SQL for table A and B separately, as you have already done for one table:
@pSQLAand@pSQLB - Write a query that combines both, having a FULL JOIN between a derived table for
AandB
Simplified example:
DECLARE @cmd NVARCHAR(MAX);
SET @cmd=N'
SELECT
*
FROM
('+@pSQLA+') AS A
FULL JOIN ('+@pSQLB+') AS B ON
A.PDate=B.PDate;
';
EXECUTE sp_executesql @cmd;
Related Topics
Extra Fields with SQL Min() & Group By
Mysql: How to Sum() a Timediff() on a Group
Good Resources for Relational Database Design
Update Existing Database Values from Spreadsheet
How to Gracefully Include Formatted SQL Strings in an R Script
Oracle SQL Clause Evaluation Order
Calling Stored Procedure from Another Stored Procedure SQL Server
Sql: How to to Sum Two Values from Different Tables
Is There Something Equivalent to Argmax in SQL
Turn Off SQL Logging While Keeping Settings.Debug
How to Update All Columns with Insert ... on Conflict ...
How Does Subquery in Select Statement Work in Oracle
Efficiently Storing 7.300.000.000 Rows