how to get Pivot result set using 3 tables
-
You have ast.quantity all over the query. Is there a quantity column in the table tblAssets? In such case, why is the CTE taking quantity from a different table?
As I said before, you should work with a static query to get that working before you play with the dynamic stuff. Else it will be too much for you at once. Or better, abandon the SQL solution and to this in the client.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
- Erland Sommarskog wrote:
Is there a quantity column in the table tblAssets?
No, quantity belongs to tblLocationAssets
- gaurav wrote:
No, quantity belongs to tblLocationAssets
Well, then you know what to fix in the query.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
- Erland Sommarskog wrote:
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).
Erland - As written, you are absolutely correct. That said, it is, however, easily corrected by by simply adding TOP(N) and ORDER BY clauses. See test below...
SET NOCOUNT ON;
IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL
BEGIN DROP TABLE #TestData; END;
CREATE TABLE #TestData (
rid int NOT NULL PRIMARY KEY CLUSTERED,
first_name varchar(20) NOT NULL
);
INSERT #TestData(rid, first_name) VALUES (1, 'Erland'), (2, 'Jeff'), (3, 'Jason');
GO
--==============================================================================
--------------------------------------------------------------------------------
DECLARE @d_sql_1 nvarchar(4000) = N'';
SELECT
@d_sql_1 = @d_sql_1 + '; ' + td.first_name
FROM
#TestData td;
SELECT ex_num = 1, name_array = STUFF(@d_sql_1, 1, 2, N'');
--------------------------------------------------------------------------------
DECLARE @d_sql_2 nvarchar(4000) = N'';
SELECT TOP (10000)
@d_sql_2 = @d_sql_2 + '; ' + td.first_name
FROM
#TestData td
ORDER BY
td.rid DESC;
SELECT ex_num = 2, name_array = STUFF(@d_sql_2, 1, 2, N'');
--------------------------------------------------------------------------------
DECLARE @d_sql_3 nvarchar(4000) = N'';
SELECT TOP (10000)
@d_sql_3 = @d_sql_3 + '; ' + td.first_name
FROM
#TestData td
ORDER BY
td.first_name ASC;
SELECT ex_num = 3, name_array = STUFF(@d_sql_3, 1, 2, '');
--------------------------------------------------------------------------------
DECLARE @d_sql_4 nvarchar(4000) = N'';
SELECT TOP (10000)
@d_sql_4 = @d_sql_4 + '; ' + td.first_name
FROM
#TestData td
ORDER BY
td.first_name DESC;
SELECT ex_num = 4, name_array = STUFF(@d_sql_4, 1, 2, '');
--------------------------------------------------------------------------------Results...
ex_num name_array
----------- -------------------------
1 Erland; Jeff; Jason
ex_num name_array
----------- -------------------------
2 Jason; Jeff; Erland
ex_num name_array
----------- -------------------------
3 Erland; Jason; Jeff
ex_num name_array
----------- -------------------------
4 Jeff; Jason; Erland - Jason A. Long wrote:
Erland - As written, you are absolutely correct. That said, it is, however, easily corrected by by simply adding TOP(N) and ORDER BY clauses. See test below...
No, I maintain that there is still no defined correct result. Just because a quick test gives the expected result, does not mean that you will always get that.
There is both string_agg (from SQL 2017 and on) and the FOR XML PATH (since SQL 2005), which both comes with warranties. No need to use something else.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
- Erland Sommarskog wrote:Jason A. Long wrote:
Erland - As written, you are absolutely correct. That said, it is, however, easily corrected by by simply adding TOP(N) and ORDER BY clauses. See test below...
No, I maintain that there is still no defined correct result. Just because a quick test gives the expected result, does not mean that you will always get that.
There is both string_agg (from SQL 2017 and on) and the FOR XML PATH (since SQL 2005), which both comes with warranties. No need to use something else.
Erland, Your depth of knowledge, when it comes to SQL Server internals, surpasses my own by a wide margin. So my followup here is to improve my own knowledge, not an attempt to refute yours...
- When STRING_AGG() is an available option, I agree, that it is the preferable option.
2. FOR XML PATH, however, does have some downsides of its own. "Special XML characters" almost always dictate that we use "TYPE'd XML" when dealing with text data and that carries a sometimes significant performance penalty compared to the non-typed version. Plus, the syntax is anything but intuitive...
3. What would cause or allow SQL Server to ignore an explicit ORDER BY clause when a specific row goal has been established?
- gaurav wrote:
Please suggest where I need to modify or where I have done wrong. Thank you
gaurav wrote:No, quantity belongs to tblLocationAssets
Hang on... I'm checking. Heh... this is what I get for posting so late at night with no test data... 😀
--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) -
Damn... I forgot the GROUP BY. Serves me right for posting so late without any test data to check with. That's no good excuse but those are the reasons.
On the GROUP BY you added, you can't include an aggregated column in the GROUP BY. Since we're using SUM() to aggregate the "quantity", we just need to remove that from the GROUP BY.
For the other new problem, the "quantity" comes from the ctePreAgg, which has an alias of "agg", not "ast". We needed to tweak that bit of dynamic SQL.
So... incorporating all the changes that we had to put in to fix the errors we've come across including the original tweak we did on @SQL2, try this and let us know if it works. If it works correctly, don't go away, there's still some to learn. We also need to know which version of SQL Server you're using. Hopefully, it's at least SQL Server 2017.
--===== 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
GROUP BY asset_id, location_id
)
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 agg.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)
;--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) - Erland Sommarskog wrote: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.
For a majority of people, I agree. They just want a solution and there’s no learning no matter how hard you try. It’s also one of the reasons why I generally don’t post on forums like StackOverflow where they really only want you to post answers and strongly discourage any discussions.
But, if you assume that everyone is like that, you can miss the grand opportunity to teach someone that actually will take the time to learn more so they don’t have to ask the same question in the future. Usually, you just can’t tell from their posts. Also, this person is fairly new to this forum and hasn’t established such a sot-like reputation. While they also didn’t post some readily consumable test data, they might not have known the benefits there and, even if they did, might not even know how to do that, which is why I posted the link for the article that explain one way to provide it.
Erland Sommarskog wrote: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).
Heh… patience, man… I’m not done teaching, yet. 😉
--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) -
p.s. The good part about this is that you reminded me that I needed to update my old Dynamic Cross Tab article. I edited the first post in the discussion to give folks a heads up and I added an identical note at the end so those that have subscribed will get a notification of a new post.
--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:
If it works correctly, don't go away, there's still some to learn. We also need to know which version of SQL Server you're using. Hopefully, it's at least SQL Server 2017.
--===== 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
GROUP BY asset_id, location_id
)
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 agg.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)
;My Sql version is Microsoft SQL Server 2012 - 11.0.2100.60 (X64) Feb 10 2012 19:39:15 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
now, it shows
Msg 8120, Level 16, State 1, Line 7
Column 'dbo.tblAssets.asset_name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. - Jeff Moden wrote:
Damn... I forgot the GROUP BY. Serves me right for posting so late without any test data to check with. That's no good excuse but those are the reasons.
Thank you so much for your help.
Sir, now I have added " group by ast.asset_name" and " LEFT JOIN ctePreAgg " it works fine.
--===== 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
GROUP BY asset_id, location_id
)
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 agg.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
LEFT JOIN ctePreAgg agg ON ast.asset_id = agg.asset_id group by ast.asset_name
;'
;
--===== Combine the 3 parts of the Dynamic SQL and execute it all as a single query.
exec (@SQL1+@SQL2+@SQL3)
;and the output as below
Thank you, thanks a lot 😀
-
But here I still have a thing that is missing.
The list of all location is coming that's fine, but also I need the full list of Assets also (I marked with yellow)
I think I am very new to my solution.
Generally when you post a question like this, you do yourself a service if you include CREATE TABLE statements for your tables + INSERT statements with sample data and finally the expected result given the sample. This makes it easy to copy and paste into a query window to develop a tested solution.
Then again, if you spend some time on Jeff's solution, I think you should be able to figure what to change to add the asset name.
I would also recommend you to read my text on http://www.sommarskog.se/dynamic_sql.html#pivot. As Jeff noted, his article is a little outdated. That is, if you still insist on doing this in T-SQL, which I'm still is not convinced is the way to go for you.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
- Jason A. Long wrote:
3. What would cause or allow SQL Server to ignore an explicit ORDER BY clause when a specific row goal has been established?
How would an ORDER BY or TOP guarantee that all values will be added to @sql? SQL Server may still opt to only assign the variable once. It may not be likely, but as I said there are no guarantees.
Yes, I agree that the FOR XML syntax is ugly and in no way intuitive, and because of the way you have to use it, it may not always give the best performance. But it is guaranteed to produce the expected result.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
- Jeff Moden wrote:
For a majority of people, I agree. They just want a solution and there’s no learning no matter how hard you try. It’s also one of the reasons why I generally don’t post on forums like StackOverflow where they really only want you to post answers and strongly discourage any discussions.
But, if you assume that everyone is like that, you can miss the grand opportunity to teach someone that actually will take the time to learn more so they don’t have to ask the same question in the future. Usually, you just can’t tell from their posts. Also, this person is fairly new to this forum and hasn’t established such a sot-like reputation. While they also didn’t post some readily consumable test data, they might not have known the benefits there and, even if they did, might not even know how to do that, which is why I posted the link for the article that explain one way to provide it.
It's hard to approach it like Jeff says here but I try though. You never know what level of instruction the OP has a tolerance for. For a lot of people, many many people, they want/need an example that they can see working. Some people want to see some effort on the part of the OP'er to try something, but why? They are trying by asking other people for a working example because that's how they learn. They don't want to be pointed at the documentation either.
StackOverflow requires a lot of attention to get points. Most SQL questions are answered in less than 5 minutes. Sometimes in less than 1 minutes. So unless you're sitting there hitting the refresh button like a monkey (which I've been doing lately) the others on there are reading the questions before you are. Good luck getting any points that way. I need 1,500 points on StackOverflow in order to create a tag for JsonAutoService so that's my goal and it's tricky to do. In 3 weeks I have 1,288 points. Only 212 to go.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply