sql select pivot multiple tables
Pivot example.
SELECT
t.ProductName,
[Live] AS LiveCapacity,
[Pending] AS PendingCapacity
FROM
(
SELECT product.ProductName,
performance.Capacity,
plan.Status
FROM product
JOIN performance ON performance.ProductID = product.ProductID
JOIN plan ON performance.PlanID = plan.PlanID
) t
PIVOT (
SUM(Capacity)
FOR Status IN ([Live],[Pending])
) p
Non-Pivot example
SELECT product.ProductName,
SUM(CASE WHEN plan.Status = 'Live' THEN performance.Capacity END) as LiveCapacity,
SUM(CASE WHEN plan.Status = 'Pending' THEN performance.Capacity END) as PendingCapacity
FROM product
JOIN performance ON performance.ProductID = product.ProductID
JOIN plan ON performance.PlanID = plan.PlanID
GROUP BY product.ProductName
SQL Syntax to Pivot multiple tables
I will post a few examples from this model -- because I already have them. Both models are very similar, so you should not have too much trouble adopting this technique.
When it comes to headache, I find that the simplest way is to go step by step, and optimize later.
Step 1.
Create a view to flatten the model; (see the model)
CREATE VIEW dbo.vProperties AS
SELECT m.MachineID AS [Machine ID]
,s.SetupID AS [Setup ID]
,p.PropertyID AS [Property ID]
,t.PropertyTypeID AS [Property Type ID]
,m.Name AS [Machine Name]
,s.Name AS [Setup Name]
,t.Name AS [Property Type Name]
,t.IsTrait AS [Is Trait]
,x.Value AS [Measurement Value]
,x.Unit AS [Unit]
,y.Value AS [Trait]
FROM dbo.Machine AS m
JOIN dbo.Setup AS s ON s.MachineID = m.MachineID
JOIN dbo.Property AS p ON p.SetupID = s.SetupID
JOIN dbo.PropertyType AS t ON t.PropertyTypeID = p.PropertyTypeID
LEFT JOIN dbo.Measurement AS x ON x.PropertyID = p.PropertyID
LEFT JOIN dbo.Trait AS y ON y.PropertyID = p.PropertyID
Step 2.
Create a view to generate only [Setup Name], [Property Type Name], [Value]; note that in this one the measurement value and trait end up in the same column. You would probably use JobName, ParameterTypeName, Value
CREATE VIEW dbo.vSetupValues AS
SELECT [Setup Name]
,[Property Type Name]
,COALESCE(cast([Measurement Value] AS varchar(50)), [Trait]) AS [Val]
FROM dbo.vProperties
Step 3.
Create list of properties (Parameters) with a column to order by
DECLARE @Props TABLE (
id int IDENTITY (1,1)
,PropName varchar(50)
);
INSERT INTO @Props (PropName)
SELECT DISTINCT [Name]
FROM dbo.PropertyType
Step 4.
Now I will dynamically create the query text
DECLARE @qw TABLE(
id int IDENTITY (1,1)
, txt nchar(500)
)
INSERT INTO @qw (txt)
SELECT 'SELECT' UNION
SELECT '[Setup Name]' ;
INSERT INTO @qw (txt)
SELECT ',MAX(CASE [Property Type Name] WHEN ''' + PropName
+ ''' THEN Val ELSE NULL END) AS [' + PropName + ']'
FROM @Props
ORDER BY id;
INSERT INTO @qw (txt)
SELECT 'FROM dbo.vSetupValues' UNION
SELECT 'GROUP BY [Setup Name]' UNION
SELECT 'ORDER BY [Setup Name]';
Step 5.
And here is the text of the query, form this point I can package this into a stored procedure, another view, or into a variable to use as dynamic sql.
SELECT txt FROM @qw
returns
SELECT
[Setup Name]
,MAX(CASE [Property Type Name] WHEN 'Diameter LSL' THEN [Val] ELSE NULL END) AS [Diameter LSL]
,MAX(CASE [Property Type Name] WHEN 'Diameter USL' THEN [Val] ELSE NULL END) AS [Diameter USL]
,MAX(CASE [Property Type Name] WHEN 'Force LSL' THEN [Val] ELSE NULL END) AS [Force LSL]
,MAX(CASE [Property Type Name] WHEN 'Force USL' THEN [Val] ELSE NULL END) AS [Force USL]
,MAX(CASE [Property Type Name] WHEN 'Leak LSL' THEN [Val] ELSE NULL END) AS [Leak LSL]
,MAX(CASE [Property Type Name] WHEN 'Leak USL' THEN [Val] ELSE NULL END) AS [Leak USL]
,MAX(CASE [Property Type Name] WHEN 'Press Travel LSL' THEN [Val] ELSE NULL END) AS [Press Travel LSL]
,MAX(CASE [Property Type Name] WHEN 'Press Travel USL' THEN [Val] ELSE NULL END) AS [Press Travel USL]
,MAX(CASE [Property Type Name] WHEN 'Seal Height LSL' THEN [Val] ELSE NULL END) AS [Seal Height LSL]
,MAX(CASE [Property Type Name] WHEN 'Seal Height USL' THEN [Val] ELSE NULL END) AS [Seal Height USL]
FROM dbo.vSetupValues
GROUP BY [Setup Name]
ORDER BY [Setup Name]
And if I run this:
(source: damirsystems.com)
UPDATE: fixed bug at step 4, was missing max() and added results example.
SQL/TSQL: Using PIVOT on multiple tables
It looks like you're trying to apply the Pivot command against a CTE
try this;
;WITH pvtCTE AS
(SELECT so.empid,
YEAR(so.orderdate) AS SalesYEAR,
--so.orderid AS ORDERS, /** removed from grouping**/
hr.lastname,
hr.firstname,
SUM(ISNULL(s.unitprice,0.00)) as unitprice /** pre grouping adjusted per comment**/
FROM Sales.Orders so
left outer join HR.Employees hr on so.empid = hr.empid
inner join Sales.OrderDetails s on so.orderid = s.orderid
GROUP BY so.empid, YEAR(so,orderDate), hr.lastname, hr.firstname --added for aggregated CTE results
)
SELECT empid, firstname, lastname, [2006], [2007], [2008]
FROM (SELECT * FROM pvtCTE) p
PIVOT (
SUM(unitprice)
FOR SalesYEAR IN ([2006],[2007],[2008])
) as pvt;
Pivot Function on Multiple Tables and Dynamic Columns in SQL
There are a few issues that you need to solve in order to get the result you desire. But before trying a dynamic sql version of a query I'd always recommend that you try get your final result by writing a hard-coded or static version first. This allows you to get the desired result without bugs and then convert it to dynamic sql as your final query.
First, let's get your table structures and sample data into a reusable script. It appears that you only need table2 and table3 to get your end result:
create table #table2
(
id int,
ctypeid int,
columnname varchar(50)
)
insert into #table2
values
(1, 20, 'Account Manager'), (2, 20, 'Channel'),
(3, 20, 'Start Date'), (4, 20, 'End Date'),
(5, 20, 'Gross Annual'), (6, 6, 'Account Manager'),
(7, 6, 'Channel'), (8, 6, 'Start Date'),
(9, 6, 'End Date'), (10, 6, 'Gross Annual');
create table #table3
(
id int,
table2id int,
value varchar(50)
)
insert into #table3
values
(1, 1, 'Jack / Kate'), (2, 2, 'PS'), (3, 3, '06/03/2017'),
(4, 4, '07/03/2017'), (5, 5, '2500'), (6, 6, 'Ollie'),
(7, 7, 'D2D'), (8, 8, '06/03/2017'), (9, 9, '06/03/2017'),
(10, 10, '5232'), (11, 1, 'Jack'), (12, 2, 'PSP'),
(13, 3, '06/03/2017'), (14, 4, '07/03/2017'), (15, 5, '7000'),
(16, 1, 'Jack Sparrow'), (17, 2, 'PS Sparrow'), (1, 3, '06/03/2017'),
(19, 4, '07/03/2017'), (20, 5, '3000'), (21, 6, 'John'),
(22, 7, 'JEDF'), (23, 8, '06/03/2017'), (24, 9, '06/03/2017'),
(25, 10, '5232');
Next, you need to write your PIVOT query. Your final result only includes the values from 3 columns CTypeId, Value, and ColumnName, so the start of your query PIVOT would be:
select
CTypeId,
[Account Manager], [Channel], [Start Date],
[End Date], [Gross Annual]
from
(
select ci.CTypeId, cd.Value, ci.ColumnName
from #Table3 cd
Inner Join #Table2 ci
on ci.Id = cd.Table2Id
) d
pivot
(
max(Value)
for ColumnName in ([Account Manager], [Channel], [Start Date],
[End Date], [Gross Annual])
) piv
Demo. But since you're aggregating string values in the Value column, you will only return one row for each CTypeId:
+---------+-----------------+---------+------------+------------+---------------+
| CTypeId | Account Manager | Channel | Start Date | End Date | Gross Annual |
+---------+-----------------+---------+------------+------------+---------------+
| 6 | Ollie | JEDF | 06/03/2017 | 06/03/2017 | 5232 |
| 20 | Jack Sparrow | PSP | 06/03/2017 | 07/03/2017 | 7000 |
+---------+-----------------+---------+------------+------------+---------------+
which is not what you want, so you need to do something to allow for multiple rows. If you look at a sample of the data that is returned by the subquery:
+---------+-------------+------------------+
| CTypeId | Value | ColumnName |
+---------+-------------+------------------+
| 20 | Jack / Kate | Account Manager |
| 20 | PS | Channel |
| 20 | 06/03/2017 | Start Date |
| 20 | 07/03/2017 | End Date |
| 20 | 2500 | Gross Annual |
| 6 | Ollie | Account Manager |
| 6 | D2D | Channel |
| 6 | 06/03/2017 | Start Date |
| 6 | 06/03/2017 | End Date |
| 6 | 5232 | Gross Annual |
+---------+-------------+------------------+
You'll see that you have unique data over a combination of CTypeId and ColumnName values, so you can create a unique row number using the windowing function row_number in your subquery which can be used to uniquely group the data for a pivot. By changing the above PIVOT code to:
select
CTypeId,
[Account Manager], [Channel], [Start Date],
[End Date], [Gross Annual]
from
(
select ci.CTypeId, cd.Value, ci.ColumnName,
rn = row_number() over(partition by ci.CTypeId, ci.ColumnName order by cd.Value)
from #Table3 cd
Inner Join #Table2 ci
on ci.Id = cd.Table2Id
) d
pivot
(
max(Value)
for ColumnName in ([Account Manager], [Channel], [Start Date],
[End Date], [Gross Annual])
) piv
order by CTypeId
See demo, you get the desired result:
+---------+-----------------+------------+------------+------------+---------------+
| CTypeId | Account Manager | Channel | Start Date | End Date | Gross Annual |
+---------+-----------------+------------+------------+------------+---------------+
| 6 | John | D2D | 06/03/2017 | 06/03/2017 | 5232 |
| 6 | Ollie | JEDF | 06/03/2017 | 06/03/2017 | 5232 |
| 20 | Jack | PS | 06/03/2017 | 07/03/2017 | 2500 |
| 20 | Jack / Kate | PS Sparrow | 06/03/2017 | 07/03/2017 | 3000 |
| 20 | Jack Sparrow | PSP | 06/03/2017 | 07/03/2017 | 7000 |
+---------+-----------------+------------+------------+------------+---------------+
Once you've got your final result you want, it's easy to convert the query to dynamic SQL:
Declare @Columns nvarchar(max)
Declare @a nvarchar(max)
Set @Columns = stuff((select distinct ',' + quotename(ColumnName)
from #table2
for xml path(''), type).value('.', 'nvarchar(max)'), 1, 1, '');
Declare @sql nvarchar(max)
= 'Select CTypeId, '+@Columns+'
from
(
select ci.CTypeId, cd.Value, ci.ColumnName,
rn = row_number() over(partition by ci.CTypeId, ci.ColumnName order by cd.Value)
from #Table3 cd
Inner Join #Table2 ci
on ci.Id = cd.Table2Id
) as s
Pivot(MAX(Value) For ColumnName IN ('+@columns+')) as pvt
order by CTypeId'
execute(@sql);
See Demo. This gives the same result as the hard-coded version with the flexibility of dynamic sql.
SQL Pivot from multiple tables and columns
this is easier way to pivot multiple columns.
;WITH cte AS
(
SELECT [DeptCode],
[Approver],
'' AS [Alternate],
ROW_NUMBER() OVER (PARTITION BY DeptCode ORDER BY b.ProcessID) Rn
FROM TableB b
JOIN TableA a ON a.ProcessID = b.ProcessID
UNION ALL
SELECT [DeptCode],
'',
AlternateApprover,
ROW_NUMBER() OVER (PARTITION BY DeptCode ORDER BY b.ProcessID) Rn
FROM TableB b
JOIN TableC a ON a.ProcessID = b.ProcessID
)
SELECT [DeptCode],
MAX(CASE WHEN Rn = 1 THEN [Approver] END) AS FirstApprover,
MAX(CASE WHEN Rn = 1 THEN [Alternate] END) AS FirstAlternate,
MAX(CASE WHEN Rn = 2 THEN [Approver] END) AS SecondApprover,
MAX(CASE WHEN Rn = 2 THEN [Alternate] END) AS SecondAlternate
FROM cte
GROUP BY [DeptCode]
this assumes that First and Second order is determined by ProcessID ROW_NUMBER() OVER (PARTITION BY DeptCode ORDER BY b.ProcessID) since you have Step in TableA but not TableC I'm not sure what that field represents.
Related Topics
Characters That Must Be Escaped in T-Sql
SQL Statement Joining Oracle and Ms SQL Server
"Ambiguous Column Name" Error on One Particular Server
Oracle SQL - Add Primary Key to Table
Which SQL Command How to Use to See the Structure of a Table on SQL Server
Catching SQL Injection and Other Malicious Web Requests
Oracle: Can You Assign an Alias to the from Clause
Query Featuring Outer Joins Behaves Differently in Oracle 12C
Find Duplicate Records Based on Two Columns
Range Wildcard Pattern Matching Behaviour with Case-Sensitive Collations
Transpose Efficiently with Proc SQL
How to Create Alternative Row Background Colors in Ssrs for Values in a Group
How to Determine Position of Row in SQL Result-Set
How to Create Xml Schema from an Existing Database in SQL Server 2008