November 24, 2009 at 2:39 pm
Could just be that my brain is fried from looking at this for so long, so, I thought that I would post this to see if anyone has any other ideas. The area, I want help with is, how to get rid of the temp table but still have the table valued output. This is used in a dynamic Pivot query.
See the Code below and thanks!
DECLARE @ColumnList VARCHAR(2000)
DECLARE @CountryList VARCHAR(2000)
DECLARE @query VARCHAR(4000)
-- Populate @ColumnList.
SELECT
@ColumnList =
STUFF((SELECT DISTINCT
'],[' + cg.[Name]
FROM
[dbo].[tblCountryGroup] As cg
WHERE
cg.[CountryGroupType] = @CountryGroupType
ORDER BY
'],[' + cg.[Name]
FOR XML PATH('')), 1, 2, '') + ']'
print @ColumnList -- Example [2005], [2006], [2007]
-- but could be any length and almost any number of rows.
-- Populate @CountryList.
SELECT
@CountryList = COALESCE(@CountryList + ', ', '') + CAST(clcg.[CountryID] AS nvarchar(256))
FROM
([dbo].[tblCountryGroup] AS cg INNER JOIN [dbo].[tblCountryLinkCountryGroup] AS clcg ON
clcg.[CountryGroupID] = cg.[CountryGroupID])
WHERE
cg.[CountryGroupType] = @CountryGroupType
print @CountryList
---------------------HERE IS THE ISSUE. I WOULD LIKE TO NOT HAVE TO MAKE A TEMP TABLE HERE!---------------------
------------------------------------------------------------------------------------------------------
-- Create a temporary table and populate it with rows that contain a country group name and a corrosponding SSRS field name (Column0, Column1, etc.)
CREATE TABLE #tmpSSRSFieldNames
(
CountryGroupName NVARCHAR(256),
SSRSFieldName NVARCHAR(256)
)
DECLARE @SSRSFieldNameList NVARCHAR(2000)
DECLARE @SSRSFieldName NVARCHAR(256)
DECLARE @tmpColumnList NVARCHAR(2000)
DECLARE @CountryGroupName NVARCHAR(256)
DECLARE @Pos int
DECLARE @ColNum int
DECLARE @CommaValue VARCHAR(1)
-- Use @tmpColumnList in order to not change the value stored in @ColumnList.
SET @tmpColumnList = LTRIM(RTRIM(@ColumnList))+ ','
SET @Pos = CHARINDEX(',', @tmpColumnList, 1)
SET @ColNum = 0
SET @CommaValue = ''
SET @SSRSFieldName = ''
SET @SSRSFieldNameList = ''
IF REPLACE(@tmpColumnList, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @CountryGroupName = LTRIM(RTRIM(LEFT(@tmpColumnList, @Pos - 1)))
IF @CountryGroupName <> ''
BEGIN
-- Strip the brackets from @CountryGroupName
SET @CountryGroupName = REPLACE(@CountryGroupName, '[', '')
SET @CountryGroupName = REPLACE(@CountryGroupName, ']', '')
SET @SSRSFieldName = 'Column' + CAST(@ColNum AS NVARCHAR(2))
INSERT INTO #tmpSSRSFieldNames (CountryGroupName, SSRSFieldName) VALUES (@CountryGroupName, @SSRSFieldName)
-- Build the list of SSRS field names that will be used in the "IN" clause of the pivot query below.
IF @ColNum > 0
BEGIN
SET @CommaValue = ','
END
SET @SSRSFieldNameList = @SSRSFieldNameList + @CommaValue + '[' + @SSRSFieldName + ']'
-- increment the column counter.
SET @ColNum = @ColNum + 1
END
SET @tmpColumnList = RIGHT(@tmpColumnList, LEN(@tmpColumnList) - @Pos)
SET @Pos = CHARINDEX(',', @tmpColumnList, 1)
END
END
THE OUTPUT SHOULD LOOK LIKE THIS:
2005 COLUMN1
2006 COLUMN2
2007 COLUMN3
--.....................................
November 24, 2009 at 4:10 pm
Why do you want to get rid of the temp table? Or do you mean you'd like to get rid of the while loop that relies on the temp table? That seems like a better goal.
November 24, 2009 at 7:09 pm
Well both would be nice, but I would settle for getting rid of the while loop first. From there I could probably add it to a function that could be joined on in the query that comes after.
Thanks,
November 25, 2009 at 10:28 am
I'm confused -- you seem to be building this "column list" as a concatenated string with bracketed names from a table where the values are in individual rows, then you turn around and strip off the brackets and parse out the data into the temp table in individual rows. Why not go directly from the original table to the temp table and skip all of the building and unbuilding strings?
- Les
November 25, 2009 at 9:01 pm
See the following article...
http://www.sqlservercentral.com/articles/Crosstab/65048/
--Jeff Moden
Change is inevitable... Change for the better is not.
December 1, 2009 at 7:33 am
Hey Everyone,
Thanks for all of the assistance and advice with this particular query. I was able to clean it up myself without the use of the temp table. I do have some additional comments pertaining to this particular question that I asked. The first part is directly for Jeff.
Jeff, that was a cool article, however, for this particular question, it had absolutely nothing that was useful inside of it. I think that I can speak to most of the posters on forums. We need help with the problem that we have, and making it work, we do not have the time before implementation to reinvent the wheel.
For other posters. Yep the process does some pretty stupid things. I did not write it, I am just trying to improve a little at a time and make it usable for the application. As it is, I worked most of the holiday trying to shore up the lack of indexing, poor t-sql techniques etc that were employed within the database. NOT TO MENTION THE RIDICULOUS DESIGN that was implemented at the start of the project. I came in 18 months later after they already had fired 4 other dba's.
I do appreciate everyones willingness to help, that means something in todays world. You know, most of the time I feel like I am trying to eat a hotdog, (Lips and A$$.....) when I want to communicate with a colleague these days.
For All of you that would like to see the finished product. Here is how I made it work. First I created this Function:>
CREATE FUNCTION [dbo].[udf_SSRSForecastYearFieldNames] (@ColumnList NVarchar(256))
-- Declares the table that is to be returned.
RETURNS @tmpSSRSFieldNames TABLE
(
CountryGroupName NVARCHAR(256),
SSRSFieldName NVARCHAR(256)
)
AS
/*========================================================================================
CREATED ON:NOV 30, 2009
MODIFIED BY:
MODIFIED ON:
PURPOSE:The purpose of this function is to replace the temp table that was being used with the output
from this function. This will lead to faster development as the temp table will not have to be
put into every query.
-------------------------------------------------------------------------------------------------------------------
EXAMPLE:SELECT * FROM DBO.UDF_SSRSFIELDNAMES(@COLUMNLIST)
========================================================================================*/
BEGIN
DECLARE @SSRSFieldNameList NVARCHAR(2000)
DECLARE @SSRSFieldName NVARCHAR(256)
DECLARE @tmpColumnList NVARCHAR(2000)
DECLARE @CountryGroupName NVARCHAR(256)
DECLARE @Pos int
DECLARE @ColNum int
DECLARE @CommaValue VARCHAR(1)
-- Use @tmpColumnList in order to not change the value stored in @ColumnList.
SET @tmpColumnList = LTRIM(RTRIM(@ColumnList))+ ','
SET @Pos = CHARINDEX(',', @tmpColumnList, 1)
SET @ColNum = 0
SET @CommaValue = ''
SET @SSRSFieldName = ''
SET @SSRSFieldNameList = ''
IF REPLACE(@tmpColumnList, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @CountryGroupName = LTRIM(RTRIM(LEFT(@tmpColumnList, @Pos - 1)))
IF @CountryGroupName <> ''
BEGIN
-- Strip the brackets from @CountryGroupName
SET @CountryGroupName = REPLACE(@CountryGroupName, '[', '')
SET @CountryGroupName = REPLACE(@CountryGroupName, ']', '')
SET @SSRSFieldName = 'Column' + CAST(@ColNum AS NVARCHAR(2))
INSERT INTO @tmpSSRSFieldNames (CountryGroupName, SSRSFieldName) VALUES (@CountryGroupName, @SSRSFieldName)
-- Build the list of SSRS field names that will be used in the "IN" clause of the pivot query below.
IF @ColNum > 0
BEGIN
SET @CommaValue = ','
END
SET @SSRSFieldNameList = @SSRSFieldNameList + @CommaValue + '[' + @SSRSFieldName + ']'
-- increment the column counter.
SET @ColNum = @ColNum + 1
END
SET @tmpColumnList = RIGHT(@tmpColumnList, LEN(@tmpColumnList) - @Pos)
SET @Pos = CHARINDEX(',', @tmpColumnList, 1)
END
END
RETURN;
END
Next I altered the stored procedure as follows:>
ALTER PROCEDURE [dbo].[sp_REPORT_SalesHistory_GetStrengthSalesbyCountryGroupType]
@PortalID int,
@CountryGroupType nvarchar(256),
@ToValidCurrencyID int,
@ToEffectiveDate datetime,
@user-id int,
@PrivCode nvarchar( 10)
AS
/*============================================================================================
CREATED ON:NOV 30, 2009
MODIFIED BY:
MODIFIED ON:
PURPOSE:The purpose of this procedure is to get the sales history by Strength Sales and Country Group Type
------------------------------------------------------------------------------------------------------------------------
EXAMPLE:[sp_REPORT_SalesHistory_GetStrengthSalesbyCountryGroupType] @PORTALID = 1,
@CountryGroupType = 'KeyMkt', @ToValidCurrencyID = 240, @ToEffectiveDate = '2009-05-31'
@user-id = 4, @PrivCode = 'ADM01'
============================================================================================*/
BEGIN TRY
DECLARE @ColumnList NVARCHAR(2000)
DECLARE @CountryList NVARCHAR(2000)
DECLARE @query NVARCHAR(4000)
DECLARE @PivotColumns NVARCHAR(2000)
SELECT
@ColumnList =
STUFF((SELECT DISTINCT
'],[' + cg.[Name]
FROM
[dbo].[tblCountryGroup] As cg
WHERE
cg.[CountryGroupType] = @CountryGroupType
ORDER BY
'],[' + cg.[Name]
FOR XML PATH('')), 1, 2, '') + ']'
SELECT
@PivotColumns =
STUFF((SELECT DISTINCT
'],[' + SSRSFieldName
From
dbo.udf_SSRSFieldNames(@ColumnList)
ORDER BY
'],[' + SSRSFieldName
FOR XML PATH('')), 1, 2, '') + ']'
SELECT
@CountryList = COALESCE(@CountryList + ', ', '') + CAST(clcg.[CountryID] AS nvarchar(256))
FROM
([dbo].[tblCountryGroup] AS cg INNER JOIN [dbo].[tblCountryLinkCountryGroup] AS clcg ON
clcg.[CountryGroupID] = cg.[CountryGroupID])
WHERE
cg.[CountryGroupType] = @CountryGroupType
-- Generate query syntax.
SET @query =
'SELECT ProductID, ProductName, FormID, FormName, StrengthNameList, StrengthName, ColumnNames, ' + @PivotColumns + CHAR(10) +
'FROM
(
SELECT
f.[ProductID],
p.[Name] AS ProductName,
f.[FormID],
vf.[Name] AS FormName,
[dbo].[udf_StrengthListByFormID](f.[FormID]) AS StrengthNameList,
st.[Name] AS StrengthName,
-- Next column is used in the SSRS report to derive the actual name of the columns on the report.
-- The entries in @ColumnList are matched up with the result set column names of "Column0", "Column1", etc. using a function in the SSRS report.
ColumnNames = ''' + @ColumnList + ''',
tmp.[SSRSFieldName] AS SSRSFieldName,
[dbo].[udf_ConvertCurrency](s.[TotalAmt], s.[ValidCurrencyID], ' + CAST(@ToValidCurrencyID AS NVARCHAR(256)) + ', ''' + CAST(@ToEffectiveDate AS NVARCHAR(256)) + ''') AS TotalAmt
FROM
tblProduct p
LEFT OUTER JOIN tblForm f ON p.[ProductID] = f.[ProductID]
LEFT OUTER JOIN tblValidForm vf ON f.ValidFormID = vf.ValidFormID
INNER JOIN tblStrength st ON f.FormID = st.FormID
INNER JOIN tblSales s ON s.StrengthID = st.StrengthID
INNER JOIN tblCountryLinkCountryGroup clcg ON s.CountryID = clcg.CountryID
INNER JOIN tblCountryGroup cg ON clcg.CountryGroupID = cg.CountryGroupID
INNER JOIN dbo.udf_SSRSFieldNames(''' + @ColumnList + ''') tmp ON cg.Name = tmp.CountryGroupName
WHERE
s.[PortalID] = ' + CAST(@PortalID AS NVARCHAR(256)) + ' AND
cg.[CountryGroupType] = ''' + @CountryGroupType + ''' AND
s.[CountryID] IN (' + @CountryList + ') AND
[dbo].[udf_UserHasPrivForCountryGroup]( '
+ LTRIM(RTRIM(@UserID)) + ', ''' + LTRIM(RTRIM(@PrivCode))
+ ''', cg.[CountryGroupID]) = ''true''
) AS SQLString
PIVOT
(SUM([TotalAmt]) FOR SSRSFieldName
IN (' + @PivotColumns + ')
) AS PivotQuery'
EXECUTE (@Query)
END TRY
BEGIN CATCH
EXECUTE sp_RollAndRaise
END CATCH
Like I said, I am sure that there are better ways to do what is in this query, however, I do not have the time to reinvent the wheel or any of that, I just need to make it work and make it manageable at this point. After a bit we will see where it goes.
December 1, 2009 at 8:01 am
Glad you got it working ezlikesundaymorning-573799, but I'll have to follow up with a few comments as well. I fully understand your point of view that you are coming here for immediate help, not coming here to have to learn all new techniques before you can rewrite something. But the flip side of that is that we're here to help people learn, to give advice, and to share our knowledge with the community. We're not necessarily here to do other people's jobs for them. I say that not to be harsh, but just to put things in perspective. We can't simply rewrite code all day long for other people and never try to teach them the techniques we're using to do so, or we'd never get anything of our own done.
When you do have some time to slow things down, please read the first article in my signature on how to ask a question on the forums. We have no problem helping people with re-writes, but just copying and pasting a chunk of code doesn't really help us much. If you look around a bit, you'll see hundreds(or in Jeff's case, thousands) of posts where the same people that gave you one line responses have completely re-written code for people or given way more in depth explanations, but in most of those cases, it is because they asked the question in a way that made it easy for us to help them. You provided a sparsely documented chunk of code with no sample data, no information about the underlying tables, no expected results and missing pieces all over the place; and asked for us to help you rewrite it.
For myself, I'll admit that my comment wasn't particularly helpful in this case, I just found it extremely humorous that you were trying to get rid of a temp table instead of focusing on your while loop as a means of improving performance. I definitely could have done a better job of expressing that, and for that I apologize.
While you asked the question wrong, you did provide a final solution and give feedback afterwards, which we definitely appreciate here, so I thank you for that.
December 1, 2009 at 8:24 am
Seth,
Thanks so much for the reply back to my message. Having done this about 15 years now, I really was only looking for a suggestion, which I should have mentioned. I couldn't have gotten to the position I am in without having something learned. I do appreciate the suggestions that were given, even if they were not of immediate help. Not a problem. Honestly, I do not want anyone doing my work for me, therefore the sparse information in the description. I should have stated, "Am, I missing the obvious here?" Anyway, I could go on and on and waste more valuable time here with the same information.
I do appreciate everyones contributions! I had my reasons for not sharing completely. Wasn't looking for this to be a puzzle.
Thanks again for the reply Seth. Points taken, I greatly appreciate the feedback and do continue to write and provide feedback to the user community. WE do read them!
Thanks,
Scott Dexter
December 1, 2009 at 8:36 am
I see this
dbo].[udf_UserHasPrivForCountryGroup]( '
+ LTRIM(RTRIM(@UserID)) + ', ''' + LTRIM(RTRIM(@PrivCode))
+ ''', cg.[CountryGroupID]) = ''true''
as a cardinal sin. Microsoft have invested a lot of time and money in creating a SQL optimizer, and this completely destroys all that hard work.
In a properly formed setbased statement , i see this as RBar, the optimizer may well find it more efficient to process only those CountryGroupId's for the @User @PrivCode combination.
December 1, 2009 at 9:27 am
Dear Mr. Ballantyne,
Thank you for your contribution. Yes, I did not write this crap. Yes it is embedded EVERYWHERE, (NOTE) Previous DB Gods are not a part of the project any longer... Thank you for your concern, I do not like it any more than you do.
Scott
December 1, 2009 at 11:15 am
ezlikesundaymorning-573799 (11/24/2009)
Could just be that my brain is fried from looking at this for so long, so, I thought that I would post this to see if anyone has any other ideas. The area, I want help with is, how to get rid of the temp table but still have the table valued output. [font="Arial Black"]This is used in a dynamic Pivot query.[/font]
To be honest, Scott... I didn't even look at your code. I went only by what you stated, the important part being bolded above.
Having done this about 15 years now, I really was only looking for a suggestion...
No problem... Based on what you wrote, that's all you got from me... a link to an article to resolve dynamic pivots. 🙂
I do appreciate everyones willingness to help, that means something in todays world. You know, most of the time I feel like I am trying to eat a hotdog, (Lips and A$$.....) when I want to communicate with a colleague these days.
Very cool. How about I send you a box of nice pork chops to show my appreciation for the gratious way you did that?
I think that I can speak to most of the posters on forums.
I know a good number of folks on this forum that would probably take pretty good offense at your assumption. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
December 1, 2009 at 1:08 pm
Jeff,
Thank you for your addition to the forum, which I did state was resolved, and you were so gracious to point out that you did not even look at the post. Very Helpful by the way.
Personally, Jeff, I could give a good rats patute whether you took offense or not. I was trying to make a point. I knew that you did not read it and you openly admitted it. What good are forums if people can not read what the author is needing assistance with. Why waste their time with a useless post to a current problem?
You see Jeff over the course of the years, I have learned to have very thick skin. When someone INSISTS that we do something a particular way that I have no control over, rather than sticking my chin up and saying NO. Of which, has had me looking for way too many jobs, I just do it right from the beginning without asking permission and what not. I do not give them the option to say no now! That being said, I did not write the piece of crap code that is currently in the environment I happen to be dealing with, I am only here to enhance and architect it properly, this however, means that the business still has to be able to use it without too many interruptions and with descent performance.
I do however, appreciate the topic you did send Jeff. It was informative and a good read. I just did not have the time to re-write, we have deadlines here unfortunately and with the last 4 getting the axe before me, I feel like I have done fairly well now that they have signed me on for another budgeted year.
If I had been here from the beginning, perhaps there would not be the need for the dynamic sql, lack of Clustered Indexes, Lack of Indexes and Statistics, poor db design, Cursors in Production Code, ANSI 89 Standard SQL etc. I mean, are there actually people out there getting hired that are allowed to do this? Yep, rhetorical question for sure, I get to come in and fix this like many others as well.
I guess the long and short of it was that I was pissed that you would send me that article, and the minute I saw it, knew you had read NOTHING. Sorry if this gets under your skin, it just got under mine.
Be Kewl,
SSDD
December 1, 2009 at 1:15 pm
ezlikesundaymorning-573799 (12/1/2009)
I was trying to make a point.
Heh... me too, Luther. Hit the showers and cool down. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
December 1, 2009 at 1:27 pm
Never know, we may meet up somewhere sometime, and swap the best stories over a frosty mug. 🙂
December 1, 2009 at 1:49 pm
Now you're talking... Beer fixes everything especially when war stories about work are involved. :w00t: Add a pool table and some good company and there are no problems that can't be solved. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply