how to get Pivot result set using 3 tables
-
Hello, Everyone,
I am very new to this forum.
I have a question.
I have 3 tables in my database. tblAssets contains all assets, tbl_Projects contains all locations and tblLocationAssets contains the location and asset_id and asset quantity on that location.
select asset_id, asset_name from tblAssets order by asset_id
select asset_id, asset_name , location_id ,quantity from tblLocationAssets order by asset_id
select Sno, Location from tbl_Projects order by SnoThe output as below
I want a result set like below i.e. All locations should come as columns (either the location contains any assets or not) and all assets come as rows (either any location contains that asset or not)
Please suggest.
Thank You
-
First of all, you should think twice if this is something you really should do in the database. This is for presentional matters, so the presentation layer is the best venue for this. There are several reporting tools that support a dynamic pivot out of the box.
On the other hand, relational databases typically do not, since a dynamic pivot is a highly unrelational operation. Each column in a result set is supposed to represent a unique attribute of the entity the result set models. And a result set is supposed to have a fixed set of attributes.
That said, I have written about how to do a dynamic pivot here: http://www.sommarskog.se/dynamic_sql.html#pivot
If you have never worked with dynamic SQL before, you should give it a third thought if you really want to do this in the database. Dynamic SQL is not for the newcomer to SQL, but it is an advanced feature. And if you really want to try it, you better start reading the article from the beginning and not the place that I linked you to.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
- Erland Sommarskog wrote:
There are several reporting tools that support a dynamic pivot out of the box.
Sir, I just need it for presentation purposes only. Please suggest any tool which can run on the .net framework.
-
I'm a database guy and don't work on the client side, so my knowledge is thin on exactly what products that are out there. I guess could google around, but wait! So could you...
But they tell me that SSRS has the Tablix report, and PowerBI seems to have it. I am sure that you can find .NET components for the job as well. Not that it seems overly advanced to loop over a DataTable and put data into a grid.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
-
What are your options?
If you can use SSRS, it's stupid easy.
Can you use a matrix in .NET? (Well, SSRS is an ASP.net application, so I would think so.)
- gaurav wrote:Erland Sommarskog wrote:
There are several reporting tools that support a dynamic pivot out of the box.
Sir, I just need it for presentation purposes only. Please suggest any tool which can run on the .net framework.
I generally agree with Erland on this subject and pietLinden is probably correct about SSRS BUT... this is actually fairly simple to pull off in T-SQL.
How many rows do you have in the tbl_Projects table?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden wrote:
How many rows do you have in the tbl_Projects table?
Sir, there are 51 rows.
- gaurav wrote:Jeff Moden wrote:
How many rows do you have in the tbl_Projects table?
Sir, there are 51 rows.
Ok... so we definitely need Dynamic SQL, at least to build the query if you want to make it all static later. With the understanding that I have none of your data and I'm not going to copy data from pretty pictures (which really helped here, though), here's my crack at the code that should do the trick for you provided that I didn't make any error (like I said, no readily consumable data to test with).
--===== Local variables to hold the 3 different sections of Dynamic SQL.
DECLARE @SQL1 VARCHAR(MAX) -- First part, which is static
,@SQL2 VARCHAR(MAX) -- Second part, which is the dynamic column list of the CROSSTAB
,@SQL3 VARCHAR(MAX) -- Third part, which is also static
;
--===== First part, which is static
SELECT @SQL1 = '
WITH ctePreAgg AS
(
SELECT asset_id, location_id, quantity = SUM(quantity)
FROM dbo.tblLocationAssets
)
SELECT AssetName = ast.asset_name
'
;
--===== Second part, which is the dynamic column list of the CROSSTAB
SELECT @SQL2 = ISNULL(@SQL2+' ,',SPACE(9))+REPLACE(REPLACE(REPLACE('
,"<<Location>>" = SUM(CASE WHEN agg.location_id = <<Sno>> THEN ast.quantity ELSE 0 END)'
,'"','''') --This is where the other end of the 3 REPLACEs start
,'<<Location>>',[Location])
,'<<Sno>>',CONVERT(VARCHAR(10),Sno))
FROM dbo.tbl_projects
ORDER BY [Location] --This could cause a problem with building @SQL2 but I had no data to check with.
;
--===== Third part, which is also static.
-- Note that we don't need a join to the tbl_projects table because
-- that's were we got the info from for the Dynamic SQL above.
SELECT @SQL3 = '
FROM dbo.tblAssets ast
RIGHT JOIN ctePreAgg agg ON ast.asset_id = agg.asset_id
;'
;
--===== Combine the 3 parts of the Dynamic SQL and execute it all as a single query.
EXEC (@SQL1+@SQL2+@SQL3)
;Please see the articles at the following links for a much deeper understanding of CROSSTABs, why the PIVOT operator sucks, and how to do Dynamic SQL.
https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs
For future posts where the inclusion of sample data would help a lot (like it would have for this one), please see the article at the following link to help folks help you better and more quickly.
https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden wrote:
--===== Local variables to hold the 3 different sections of Dynamic SQL.
DECLARE @SQL1 VARCHAR(MAX) -- First part, which is static
,@SQL2 VARCHAR(MAX) -- Second part, which is the dynamic column list of the CROSSTAB
,@SQL3 VARCHAR(MAX) -- Third part, which is also static
;
--===== First part, which is static
SELECT @SQL1 = '
WITH ctePreAgg AS
(
SELECT asset_id, location_id, quantity = SUM(quantity)
FROM dbo.tblLocationAssets
)
SELECT AssetName = ast.asset_name
'
;
--===== Second part, which is the dynamic column list of the CROSSTAB
SELECT @SQL2 = ISNULL(@SQL2+' ,',SPACE(9))+REPLACE(REPLACE(REPLACE('
,"<<Location>>" = SUM(CASE WHEN agg.location_id = <<Sno>> THEN ast.quantity ELSE 0 END)'
,'"','''') --This is where the other end of the 3 REPLACEs start
,'<<Location>>',[Location])
,'<<Sno>>',CONVERT(VARCHAR(10),Sno))
FROM dbo.tbl_projects
ORDER BY [Location] --This could cause a problem with building @SQL2 but I had no data to check with.
;
--===== Third part, which is also static.
-- Note that we don't need a join to the tbl_projects table because
-- that's were we got the info from for the Dynamic SQL above.
SELECT @SQL3 = '
FROM dbo.tblAssets ast
RIGHT JOIN ctePreAgg agg ON ast.asset_id = agg.asset_id
;'
;
--===== Combine the 3 parts of the Dynamic SQL and execute it all as a single query.
EXEC (@SQL1+@SQL2+@SQL3)
;Thank you for your prompt reply, but the above script shows error.
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near ','.There is no "," in line 9.
Please sugget
-
So print out the dynamic SQL and post it. It's likely going to be in line 9 of the Dynamic SQL.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden wrote:
So print out the dynamic SQL and post it. It's likely going to be in line 9 of the Dynamic SQL.
WITH ctePreAgg AS
(
SELECT asset_id, location_id, quantity = SUM(quantity)
FROM dbo.tblLocationAssets
)
SELECT AssetName = ast.asset_name
,'Azamgarh ' = SUM(CASE WHEN agg.location_id = 6 THEN ast.quantity ELSE 0 END) ,
,'Barpeta - Sparco' = SUM(CASE WHEN agg.location_id = 34 THEN ast.quantity ELSE 0 END) ,
,'Barpeta - Welspun' = SUM(CASE WHEN agg.location_id = 42 THEN ast.quantity ELSE 0 END) ,
,'Barpeta -Ratnamani' = SUM(CASE WHEN agg.location_id = 47 THEN ast.quantity ELSE 0 END) ,
,'Bolpur ' = SUM(CASE WHEN agg.location_id = 49 THEN ast.quantity ELSE 0 END) ,
,'Bondapalli ' = SUM(CASE WHEN agg.location_id = 21 THEN ast.quantity ELSE 0 END) ,
,'Cooch Behar - Sparco' = SUM(CASE WHEN agg.location_id = 35 THEN ast.quantity ELSE 0 END) ,
,'Cooch Behar-Man' = SUM(CASE WHEN agg.location_id = 48 THEN ast.quantity ELSE 0 END) ,
,'Cooch Behar-Ratnamani' = SUM(CASE WHEN agg.location_id = 45 THEN ast.quantity ELSE 0 END) ,
,'Cooch Behar-Welspun' = SUM(CASE WHEN agg.location_id = 44 THEN ast.quantity ELSE 0 END) ,
,'Cuddapah ' = SUM(CASE WHEN agg.location_id = 38 THEN ast.quantity ELSE 0 END) ,
,'Dasrathpur ' = SUM(CASE WHEN agg.location_id = 13 THEN ast.quantity ELSE 0 END) ,
,'Dasrathpur-2 ' = SUM(CASE WHEN agg.location_id = 14 THEN ast.quantity ELSE 0 END) ,
,'Datia ' = SUM(CASE WHEN agg.location_id = 20 THEN ast.quantity ELSE 0 END) ,
,'Dehri-On- Son ' = SUM(CASE WHEN agg.location_id = 4 THEN ast.quantity ELSE 0 END) ,
,'Delhi-Head-Office' = SUM(CASE WHEN agg.location_id = 29 THEN ast.quantity ELSE 0 END) ,
,'Dharampuri ' = SUM(CASE WHEN agg.location_id = 41 THEN ast.quantity ELSE 0 END) ,
,'Donakonda ' = SUM(CASE WHEN agg.location_id = 36 THEN ast.quantity ELSE 0 END) ,
,'Dumpsite Auditor' = SUM(CASE WHEN agg.location_id = 55 THEN ast.quantity ELSE 0 END) ,
,'Durgapur' = SUM(CASE WHEN agg.location_id = 24 THEN ast.quantity ELSE 0 END) ,
,'Garhshankar ' = SUM(CASE WHEN agg.location_id = 5 THEN ast.quantity ELSE 0 END) ,
,'Gaya ' = SUM(CASE WHEN agg.location_id = 27 THEN ast.quantity ELSE 0 END) ,
,'Hazaribagh ' = SUM(CASE WHEN agg.location_id = 26 THEN ast.quantity ELSE 0 END) ,
,'Jajpur' = SUM(CASE WHEN agg.location_id = 32 THEN ast.quantity ELSE 0 END) ,
,'Kalakada ' = SUM(CASE WHEN agg.location_id = 39 THEN ast.quantity ELSE 0 END) ,
,'Kalol ' = SUM(CASE WHEN agg.location_id = 11 THEN ast.quantity ELSE 0 END) ,
,'Kamakhya Nagar ' = SUM(CASE WHEN agg.location_id = 15 THEN ast.quantity ELSE 0 END) ,
,'Karjan ' = SUM(CASE WHEN agg.location_id = 10 THEN ast.quantity ELSE 0 END) ,
,'Khunti ' = SUM(CASE WHEN agg.location_id = 17 THEN ast.quantity ELSE 0 END) ,
,'Kolar ' = SUM(CASE WHEN agg.location_id = 51 THEN ast.quantity ELSE 0 END) ,
,'Koppur ' = SUM(CASE WHEN agg.location_id = 52 THEN ast.quantity ELSE 0 END) ,
,'Mahu - Ratnamani' = SUM(CASE WHEN agg.location_id = 19 THEN ast.quantity ELSE 0 END) ,
,'Mahu - Welspun' = SUM(CASE WHEN agg.location_id = 18 THEN ast.quantity ELSE 0 END) ,
,'Maranchi ' = SUM(CASE WHEN agg.location_id = 8 THEN ast.quantity ELSE 0 END) ,
,'Mathighatta ' = SUM(CASE WHEN agg.location_id = 54 THEN ast.quantity ELSE 0 END) ,
,'Mogra ' = SUM(CASE WHEN agg.location_id = 53 THEN ast.quantity ELSE 0 END) ,
,'Munger ' = SUM(CASE WHEN agg.location_id = 7 THEN ast.quantity ELSE 0 END) ,
,'Nandigam ' = SUM(CASE WHEN agg.location_id = 22 THEN ast.quantity ELSE 0 END) ,
,'Pormamilla ' = SUM(CASE WHEN agg.location_id = 37 THEN ast.quantity ELSE 0 END) ,
,'Purnia - Ratnamani' = SUM(CASE WHEN agg.location_id = 46 THEN ast.quantity ELSE 0 END) ,
,'Purnia - Sparco' = SUM(CASE WHEN agg.location_id = 33 THEN ast.quantity ELSE 0 END) ,
,'Purnia -Welspun' = SUM(CASE WHEN agg.location_id = 43 THEN ast.quantity ELSE 0 END) ,
,'Purulia - MSL' = SUM(CASE WHEN agg.location_id = 31 THEN ast.quantity ELSE 0 END) ,
,'Purulia - Welspun' = SUM(CASE WHEN agg.location_id = 25 THEN ast.quantity ELSE 0 END) ,
,'Sambalpur ' = SUM(CASE WHEN agg.location_id = 16 THEN ast.quantity ELSE 0 END) ,
,'Sampatchak' = SUM(CASE WHEN agg.location_id = 9 THEN ast.quantity ELSE 0 END) ,
,'Sarath ' = SUM(CASE WHEN agg.location_id = 50 THEN ast.quantity ELSE 0 END) ,
,'Suryapet ' = SUM(CASE WHEN agg.location_id = 23 THEN ast.quantity ELSE 0 END) ,
,'V Kota ' = SUM(CASE WHEN agg.location_id = 40 THEN ast.quantity ELSE 0 END) ,
,'Varanasi ' = SUM(CASE WHEN agg.location_id = 3 THEN ast.quantity ELSE 0 END) ,
,'Vish Nagar ' = SUM(CASE WHEN agg.location_id = 12 THEN ast.quantity ELSE 0 END)
FROM dbo.tblAssets ast
RIGHT JOIN ctePreAgg agg ON ast.asset_id = agg.asset_id
; - Jeff Moden wrote:
I generally agree with Erland on this subject and pietLinden is probably correct about SSRS BUT... this is actually fairly simple to pull off in T-SQL.
Yes, for you and me it is simple. But I've seen so many people in forums who are fairly new to SQL who have been lured down this path, and it only becomes a mess. They don't understand what they are doing, so they don't learn anything. So next time they need a pivot, they ask again. And next time etc. The solution is above their level and they are not able to learn how to leverage from it. We should also not ignore the complications for permissions that dynamic SQL incurs.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
-
Replace the code for @SQL2 and its related comment with this. You might also want to fix the location names in the future... a lot of them have trailing spaces.
--===== Second part, which is the dynamic column list of the CROSSTAB
SELECT @SQL2 = ISNULL(@SQL2,'')+REPLACE(REPLACE('
,[<<Location>>] = SUM(CASE WHEN agg.location_id = <<Sno>> THEN ast.quantity ELSE 0 END)'
,'<<Location>>',RTRIM([Location])) --This is where the other end of the 2 REPLACEs start
,'<<Sno>>',CONVERT(VARCHAR(10),Sno))
FROM dbo.tbl_projects
ORDER BY [Location] --This could cause a problem with building @SQL2 but I had no data to check with.
;--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff, if you are going to suggest people to use dynamic SQL, at least show them code that is safe and robust. The construct
SELECT @sql = @sql + col FROM tbl
has no correct results defined and result whatever SQL Server feels like. Often you get the result you may expect, but not always. There are safe alternative, and I discuss both in my article (to which I linked in the first post).
Gaurav, before you start fiddling with dynamic SQL at all, you should write a perfectly static SQL statement with a few selected locations, so that you understand what you are generating. If you are to do this in SQL at all, which I am even less convinced of by now. This path will only give you more troubles down the road. Learn how do to this client-side, and you have a solution that you can use and reuse.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
- Jeff Moden wrote:
Replace the code for @SQL2 and its related comment with this. You might also want to fix the location names in the future... a lot of them have trailing spaces.
--===== Second part, which is the dynamic column list of the CROSSTAB
SELECT @SQL2 = ISNULL(@SQL2,'')+REPLACE(REPLACE('
,[<<Location>>] = SUM(CASE WHEN agg.location_id = <<Sno>> THEN ast.quantity ELSE 0 END)'
,'<<Location>>',RTRIM([Location])) --This is where the other end of the 2 REPLACEs start
,'<<Sno>>',CONVERT(VARCHAR(10),Sno))
FROM dbo.tbl_projects
ORDER BY [Location] --This could cause a problem with building @SQL2 but I had no data to check with.
;As per your suggestion, I have modified your code as below
--===== Local variables to hold the 3 different sections of Dynamic SQL.
DECLARE @SQL1 VARCHAR(MAX) -- First part, which is static
,@SQL2 VARCHAR(MAX) -- Second part, which is the dynamic column list of the CROSSTAB
,@SQL3 VARCHAR(MAX) -- Third part, which is also static
;
--===== First part, which is static
SELECT @SQL1 = '
WITH ctePreAgg AS
(
SELECT asset_id, location_id, quantity = SUM(quantity)
FROM tblLocationAssets
)
SELECT AssetName = ast.asset_name
'
;
--===== Second part, which is the dynamic column list of the CROSSTAB
SELECT @SQL2 = ISNULL(@SQL2,'')+REPLACE(REPLACE('
,[<<Location>>] = SUM(CASE WHEN agg.location_id = <<Sno>> THEN ast.quantity ELSE 0 END)'
,'<<Location>>',RTRIM([Location])) --This is where the other end of the 2 REPLACEs start
,'<<Sno>>',CONVERT(VARCHAR(10),Sno))
FROM dbo.tbl_projects
ORDER BY [Location] --This could cause a problem with building @SQL2 but I had no data to check with.
;
--===== Third part, which is also static.
-- Note that we don't need a join to the tbl_projects table because
-- that's were we got the info from for the Dynamic SQL above.
SELECT @SQL3 = '
FROM dbo.tblAssets ast
RIGHT JOIN ctePreAgg agg ON ast.asset_id = agg.asset_id
;'
;
--===== Combine the 3 parts of the Dynamic SQL and execute it all as a single query.
EXEC (@SQL1+@SQL2+@SQL3)
;Now it shows error
Msg 8120, Level 16, State 1, Line 4
Column 'tblLocationAssets.asset_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.So I put a group by in @SQL1
SELECT @SQL1 = '
WITH ctePreAgg AS
(
SELECT asset_id, location_id, quantity = SUM(quantity)
FROM tblLocationAssets group by asset_id,location_id
)
SELECT AssetName = ast.asset_name
'
;Then it shows error
Invalid column name 'quantity'.
and for now, the dynamic SQL is as below
WITH ctePreAgg AS
(
SELECT asset_id, location_id, quantity = SUM(quantity)
FROM tblLocationAssets group by asset_id,location_id, quantity
)
SELECT AssetName = ast.asset_name
,[Azamgarh] = SUM(CASE WHEN agg.location_id = 6 THEN ast.quantity ELSE 0 END)
,[Barpeta - Sparco] = SUM(CASE WHEN agg.location_id = 34 THEN ast.quantity ELSE 0 END)
,[Barpeta - Welspun] = SUM(CASE WHEN agg.location_id = 42 THEN ast.quantity ELSE 0 END)
,[Barpeta -Ratnamani] = SUM(CASE WHEN agg.location_id = 47 THEN ast.quantity ELSE 0 END)
,[Bolpur] = SUM(CASE WHEN agg.location_id = 49 THEN ast.quantity ELSE 0 END)
,[Bondapalli] = SUM(CASE WHEN agg.location_id = 21 THEN ast.quantity ELSE 0 END)
,[Cooch Behar - Sparco] = SUM(CASE WHEN agg.location_id = 35 THEN ast.quantity ELSE 0 END)
,[Cooch Behar-Man] = SUM(CASE WHEN agg.location_id = 48 THEN ast.quantity ELSE 0 END)
,[Cooch Behar-Ratnamani] = SUM(CASE WHEN agg.location_id = 45 THEN ast.quantity ELSE 0 END)
,[Cooch Behar-Welspun] = SUM(CASE WHEN agg.location_id = 44 THEN ast.quantity ELSE 0 END)
,[Cuddapah] = SUM(CASE WHEN agg.location_id = 38 THEN ast.quantity ELSE 0 END)
,[Dasrathpur] = SUM(CASE WHEN agg.location_id = 13 THEN ast.quantity ELSE 0 END)
,[Dasrathpur-2] = SUM(CASE WHEN agg.location_id = 14 THEN ast.quantity ELSE 0 END)
,[Datia] = SUM(CASE WHEN agg.location_id = 20 THEN ast.quantity ELSE 0 END)
,[Dehri-On- Son] = SUM(CASE WHEN agg.location_id = 4 THEN ast.quantity ELSE 0 END)
,[Delhi-Head-Office] = SUM(CASE WHEN agg.location_id = 29 THEN ast.quantity ELSE 0 END)
,[Dharampuri] = SUM(CASE WHEN agg.location_id = 41 THEN ast.quantity ELSE 0 END)
,[Donakonda] = SUM(CASE WHEN agg.location_id = 36 THEN ast.quantity ELSE 0 END)
,[Dumpsite Auditor] = SUM(CASE WHEN agg.location_id = 55 THEN ast.quantity ELSE 0 END)
,[Durgapur] = SUM(CASE WHEN agg.location_id = 24 THEN ast.quantity ELSE 0 END)
,[Garhshankar] = SUM(CASE WHEN agg.location_id = 5 THEN ast.quantity ELSE 0 END)
,[Gaya] = SUM(CASE WHEN agg.location_id = 27 THEN ast.quantity ELSE 0 END)
,[Hazaribagh] = SUM(CASE WHEN agg.location_id = 26 THEN ast.quantity ELSE 0 END)
,[Jajpur] = SUM(CASE WHEN agg.location_id = 32 THEN ast.quantity ELSE 0 END)
,[Kalakada] = SUM(CASE WHEN agg.location_id = 39 THEN ast.quantity ELSE 0 END)
,[Kalol] = SUM(CASE WHEN agg.location_id = 11 THEN ast.quantity ELSE 0 END)
,[Kamakhya Nagar] = SUM(CASE WHEN agg.location_id = 15 THEN ast.quantity ELSE 0 END)
,[Karjan] = SUM(CASE WHEN agg.location_id = 10 THEN ast.quantity ELSE 0 END)
,[Khunti] = SUM(CASE WHEN agg.location_id = 17 THEN ast.quantity ELSE 0 END)
,[Kolar] = SUM(CASE WHEN agg.location_id = 51 THEN ast.quantity ELSE 0 END)
,[Koppur] = SUM(CASE WHEN agg.location_id = 52 THEN ast.quantity ELSE 0 END)
,[Mahu - Ratnamani] = SUM(CASE WHEN agg.location_id = 19 THEN ast.quantity ELSE 0 END)
,[Mahu - Welspun] = SUM(CASE WHEN agg.location_id = 18 THEN ast.quantity ELSE 0 END)
,[Maranchi] = SUM(CASE WHEN agg.location_id = 8 THEN ast.quantity ELSE 0 END)
,[Mathighatta] = SUM(CASE WHEN agg.location_id = 54 THEN ast.quantity ELSE 0 END)
,[Mogra] = SUM(CASE WHEN agg.location_id = 53 THEN ast.quantity ELSE 0 END)
,[Munger] = SUM(CASE WHEN agg.location_id = 7 THEN ast.quantity ELSE 0 END)
,[Nandigam] = SUM(CASE WHEN agg.location_id = 22 THEN ast.quantity ELSE 0 END)
,[Pormamilla] = SUM(CASE WHEN agg.location_id = 37 THEN ast.quantity ELSE 0 END)
,[Purnia - Ratnamani] = SUM(CASE WHEN agg.location_id = 46 THEN ast.quantity ELSE 0 END)
,[Purnia - Sparco] = SUM(CASE WHEN agg.location_id = 33 THEN ast.quantity ELSE 0 END)
,[Purnia -Welspun] = SUM(CASE WHEN agg.location_id = 43 THEN ast.quantity ELSE 0 END)
,[Purulia - MSL] = SUM(CASE WHEN agg.location_id = 31 THEN ast.quantity ELSE 0 END)
,[Purulia - Welspun] = SUM(CASE WHEN agg.location_id = 25 THEN ast.quantity ELSE 0 END)
,[Sambalpur] = SUM(CASE WHEN agg.location_id = 16 THEN ast.quantity ELSE 0 END)
,[Sampatchak] = SUM(CASE WHEN agg.location_id = 9 THEN ast.quantity ELSE 0 END)
,[Sarath] = SUM(CASE WHEN agg.location_id = 50 THEN ast.quantity ELSE 0 END)
,[Suryapet] = SUM(CASE WHEN agg.location_id = 23 THEN ast.quantity ELSE 0 END)
,[V Kota] = SUM(CASE WHEN agg.location_id = 40 THEN ast.quantity ELSE 0 END)
,[Varanasi] = SUM(CASE WHEN agg.location_id = 3 THEN ast.quantity ELSE 0 END)
,[Vish Nagar] = SUM(CASE WHEN agg.location_id = 12 THEN ast.quantity ELSE 0 END)
FROM dbo.tblAssets ast
RIGHT JOIN ctePreAgg agg ON ast.asset_id = agg.asset_id
;Please suggest where I need to modify or where I have done wrong.
Thank you
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply