August 5, 2014 at 9:39 am
Hi Everyone,
I need some help, I'm new to SQL and learning fast, but coming up short. There are some formatting issues with my result example but I hope it can be understood.
This query:
SELECT [CustomerNumber]
,[Rev1]
,[Rev2]
,[Rev3]
,[ProductArea]
,[monthseq]
,[yearseq]
FROM [Data1].[dbo].[AllRev]
Where CustomerNumber ='7474' and monthseq = '1' and YearSeq = '2014'
Returns:
CustomerNumber Rev1 Rev2 Rev3
7474 66 202 0.00
7474 46 NULL 0.00
I need to change the query so that result changes to:
CustomerNumber Rev1 Rev2 Rev3 Rev1A Rev2A Rev3A
7474 66 202 0.00 46 Null 0.00
Please tell me if this is possable, its kicking my butt, I cannot figure it out
August 5, 2014 at 10:19 am
To have a good explanation on how to do it, read the following article: http://www.sqlservercentral.com/articles/T-SQL/63681/
And remember that MAX() is an aggregate function that you can use on strings.
August 5, 2014 at 11:25 am
Thanks for the article, I did some reading and some more and its semi clear, but not 100% I also didn't paste the full query I am using. So I will post that and maybe you can show me an example of a query of the max fucntion of a pivot.
declare @startmonth as varchar(25)
declare @startyear as varchar(25)
--set date and year
set @startmonth = '1'
set @startyear = '2014'
SELECT
cust.FS As Dept,
Prev.CustomerNumber as Customer#,
prev.product,
isnull(Prev.CIRev,0) as Product1,
isnull(Prev.EMCRev,0) as Product2,
isnull(Prev.IBMRev,0) as Product3,
isnull(Prev.SORev,0) as Product4,
isnull(Prev.NetAppRev,0) as Product5
FROM PRev2 Prev WITH (NOLOCK)
INNER JOIN Customers Cust WITH (NOLOCK) on Prev.customernumber = cust.customernumber
Where prev.Yearseq = @startyear and prev.Monthseq = @startmonth
This is the result, I've been trying to figure out the pivot but I can't put it together. Please give me another tip!
Customer#||Product||Product1||Product2||Product3||Product4||Product5
724797 Fastitems 6617.71 0.00 0.00 202.94 0.00
724797 Slowitems 4608.04 0.00 0.00 0.00 0.00
August 5, 2014 at 12:55 pm
powerofsound (8/5/2014)
Thanks for the article, I did some reading and some more and its semi clear, but not 100% I also didn't paste the full query I am using. So I will post that and maybe you can show me an example of a query of the max fucntion of a pivot.
declare @startmonth as varchar(25)declare @startyear as varchar(25)
--set date and year
set @startmonth = '1'
set @startyear = '2014'
SELECT
cust.FS As Dept,
Prev.CustomerNumber as Customer#,
prev.product,
isnull(Prev.CIRev,0) as Product1,
isnull(Prev.EMCRev,0) as Product2,
isnull(Prev.IBMRev,0) as Product3,
isnull(Prev.SORev,0) as Product4,
isnull(Prev.NetAppRev,0) as Product5
FROM PRev2 Prev WITH (NOLOCK)
INNER JOIN Customers Cust WITH (NOLOCK) on Prev.customernumber = cust.customernumber
Where prev.Yearseq = @startyear and prev.Monthseq = @startmonth
This is the result, I've been trying to figure out the pivot but I can't put it together. Please give me another tip!
Customer#||Product||Product1||Product2||Product3||Product4||Product5
724797 Fastitems 6617.71 0.00 0.00 202.94 0.00
724797 Slowitems 4608.04 0.00 0.00 0.00 0.00
Two things are important here. First is the results you want. You can find a couple of articles in my signature that explain how to do cross tabs. This is exactly what you need here. I am not actually sure you need a crosstab here as it looks like your table is denormalized. We probably need to see some ddl and sample data to help much. Please see the first article in my signature.
Second is you littering your database with NOLOCK hints. Are you familiar with that hint? Do you know all the ramifications? Are you ok with inaccurate results because your query has duplicate and/or missing data?
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/%5B/url%5D
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 5, 2014 at 2:10 pm
Thanks for the heads up Sean, I do know the issues with using Nolock, but unfortunately it’s my company’s policy to use it on all queries. Also the data base I am using was built by a finance guy and an IT guy who, I think knows enough SQL to get by, but be dangerous.
Would I be wrong in thinking that what I am asking for is not possible since you say the database is "denormalized"?
I'm a guy who loves to learn new things, and I was just thrown in this ocean of SQL and then given some wood and nails and told to build a boat. I did and I'm floating, but now they want a nicer boat with a waterslide, and I fear is going to only get worse.
Thanks for your time!
August 5, 2014 at 2:14 pm
powerofsound (8/5/2014)
Thanks for the heads up Sean, I do know the issues with using Nolock, but unfortunately it’s my company’s policy to use it on all queries. Also the data base I am using was built by a finance guy and an IT guy who, I think knows enough SQL to get by, but be dangerous.Would I be wrong in thinking that what I am asking for is not possible since you say the database is "denormalized"?
I'm a guy who loves to learn new things, and I was just thrown in this ocean of SQL and then given some wood and nails and told to build a boat. I did and I'm floating, but now they want a nicer boat with a waterslide, and I fear is going to only get worse.
Thanks for your time!
For the sake of everything your company does, please please please ask them to review this stupid mandate. A financial system is probably the absolute worst type of system where NOLOCK should be used. If your "SQL Guy" is the one who mandated such a foolish practice you are correct that they know enough to be dangerous. Why do they have such a mandate? I have a feeling it is for "performance". Sadly this is not a magic go fast button. In a financial application it is a recipe for disaster.
As to your current problem. Yes we can help but as I stated previously we need ddl and sample data.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 5, 2014 at 2:44 pm
powerofsound (8/5/2014)
Thanks for the heads up Sean, I do know the issues with using Nolock, but unfortunately it’s my company’s policy to use it on all queries.
Quick thought (no sarcasm or pun intended) if you want to progress with SQL, look for another job!
😎
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply