August 21, 2013 at 11:10 am
Hi,
A bit of a general question:
I have a query with 28 LEFT JOINS. 20 of them join one table to the original query in order to break information out into separate columns, so data is distinguishable. The next 8 do the same thing with a third table.
I was thinking about using PIVOT to replace them, but since I've never used it before, I wasn't entirely sure it would be the best route to go. I was also considering a CTE, because I'm more familiar with them. Again though, not sure it would be the best route.
Any suggestions or opinions?
August 21, 2013 at 12:09 pm
erikd (8/21/2013)
Hi,A bit of a general question:
I have a query with 28 LEFT JOINS. 20 of them join one table to the original query in order to break information out into separate columns, so data is distinguishable. The next 8 do the same thing with a third table.
I was thinking about using PIVOT to replace them, but since I've never used it before, I wasn't entirely sure it would be the best route to go. I was also considering a CTE, because I'm more familiar with them. Again though, not sure it would be the best route.
Any suggestions or opinions?
It is absolutely impossible to offer much in the way of assistance based on your post. You will need to post a LOT more details before anybody can offer much advice here.
_______________________________________________________________
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 21, 2013 at 12:10 pm
erikd (8/21/2013)
Hi,A bit of a general question:
I have a query with 28 LEFT JOINS. 20 of them join one table to the original query in order to break information out into separate columns, so data is distinguishable. The next 8 do the same thing with a third table.
I was thinking about using PIVOT to replace them, but since I've never used it before, I wasn't entirely sure it would be the best route to go. I was also considering a CTE, because I'm more familiar with them. Again though, not sure it would be the best route.
Any suggestions or opinions?
CASE statement is likely the win here.
Please provide a 4 or 5 table sample we can use. We will need create table scripts, inserts for each, and expected data outputs.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 21, 2013 at 12:23 pm
Sean - yeah, I sort of figured. I was hoping there was a general rule for a situation like this. Assuming I'm just looking for direction and not a full re-write, would just the query be good, or do you need DDL and sample data as well?
Kevin - I was at your SQL Saturday NYC talk. Really enjoyed it. Guess I should have taken a business card 😉
August 21, 2013 at 1:06 pm
erikd (8/21/2013)
Sean - yeah, I sort of figured. I was hoping there was a general rule for a situation like this. Assuming I'm just looking for direction and not a full re-write, would just the query be good, or do you need DDL and sample data as well?Kevin - I was at your SQL Saturday NYC talk. Really enjoyed it. Guess I should have taken a business card 😉
Glad you enjoyed the talk! I LOVE that session.
Note that I only asked for a few tables so I can point you in the right direction.
Oh, and feel free to PM me if you need a business card!! 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 21, 2013 at 1:29 pm
TheSQLGuru (8/21/2013)
erikd (8/21/2013)
Sean - yeah, I sort of figured. I was hoping there was a general rule for a situation like this. Assuming I'm just looking for direction and not a full re-write, would just the query be good, or do you need DDL and sample data as well?Kevin - I was at your SQL Saturday NYC talk. Really enjoyed it. Guess I should have taken a business card 😉
Glad you enjoyed the talk! I LOVE that session.
Note that I only asked for a few tables so I can point you in the right direction.
Oh, and feel free to PM me if you need a business card!! 😎
I can tell. You wore your party shirt. :w00t:
I'm attaching the query for now. I have a couple things to do, and I need to figure out a way to mask some of the table data. There's personal information in one of them. But let me know if you have any thoughts.
Thanks!
August 21, 2013 at 1:55 pm
I would have appreciated a warning to take a deep breath and be sitting down before I opened up that file and saw the query therein!! :w00t:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 21, 2013 at 1:56 pm
BTW, I don't need (or want) any type of production data. Simple dummy stuff that matches requirements is best...
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 21, 2013 at 1:56 pm
TheSQLGuru (8/21/2013)
I would have appreciated a warning to take a deep breath and be sitting down before I opened up that file and saw the query therein!! :w00t:
EAV FTW!!!!
_______________________________________________________________
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 21, 2013 at 1:58 pm
Looks like some very ugly EAV crap..., er, design. 😀
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 21, 2013 at 1:59 pm
Sean Lange (8/21/2013)
TheSQLGuru (8/21/2013)
I would have appreciated a warning to take a deep breath and be sitting down before I opened up that file and saw the query therein!! :w00t:EAV FTW!!!!
great minds think alike!! our emails passed in the ether! LOL
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 21, 2013 at 2:00 pm
You want this one Sean?!? You are WELCOME to it!! 😛
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 21, 2013 at 2:04 pm
TheSQLGuru (8/21/2013)
You want this one Sean?!? You are WELCOME to it!! 😛
/me slinks back from the anti-design quietly...
This is a BIG can of worms here. There are some band aids that can be done but they will only marginally help anything here. What would be the biggest help is redesign. You really can't get away from all these left joins because of the way these tables are put together. Honestly I think the scope of this is well beyond an online forum. The amount of work needed for this is staggering.
_______________________________________________________________
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 21, 2013 at 2:06 pm
When I see these things I am reminded the story here. https://www.simple-talk.com/opinion/opinion-pieces/bad-carma/[/url]
_______________________________________________________________
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 21, 2013 at 2:08 pm
One question: is the numeric value that indicates each "type" of value fixed? If so I think I can come up with something to get what you need in a single seek on that table.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply