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 Sno
The 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
September 1, 2020 at 7:25 pm
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]
September 1, 2020 at 7:31 pm
September 1, 2020 at 7:50 pm
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]
September 1, 2020 at 11:11 pm
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.)
September 2, 2020 at 3:31 am
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
Change is inevitable... Change for the better is not.
September 2, 2020 at 5:19 am
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
Change is inevitable... Change for the better is not.
September 2, 2020 at 5:34 am
--===== 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
September 2, 2020 at 6:18 am
So print out the dynamic SQL and post it. It's likely going to be in line 9 of the Dynamic SQL.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 2, 2020 at 6:28 am
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
;
September 2, 2020 at 6:47 am
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]
September 2, 2020 at 8:04 am
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
Change is inevitable... Change for the better is not.
September 2, 2020 at 8:16 am
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]
September 2, 2020 at 9:36 am
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 37 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy