August 3, 2012 at 5:07 am
Hi,
I am a newbie in sql server.I have came across various article on internet which shows how to implement pivot and un-pivot table. Since there are various gurus of sql server in this forum, i would like to know from them wheather they had used this feature in any of there project. And even though if you had used the above feature what would have being the alternate solution to the problem if you had being not using this feature.
August 3, 2012 at 5:22 am
When you asking about alternate solution you, do mean funtctional equivalent? you may use case+group by instead of pivot, and cross apply instead of unpivot. You may also check out these article about unpivot[/url].
August 3, 2012 at 6:33 am
yeah i was expecting functional requriment. But again the new question that comes to mind is if we do have an alternate solution then why unpivot and pivot.
Since i am new to SQL Server just curios to know it
August 3, 2012 at 6:45 am
In the past I have used PIVOT/ UNPIVOT along side dynamic SQL mainly in ETL scripts.
For example I was importing data that was stored in columns and needed it in a single row in the target DB.
August 4, 2012 at 4:13 am
Using Pivot/unpivot could be more than just a matter of choice. You can definitely avoid the Pivot with Case+Group but then you come across situations where you can't use Case+Group. This could be because the columns are not Static and you need to get the Columns Dynamically.
In these situations you can't avoid the Pivot and have to go with a Dynamic CrossTab.
For other situations it is a matter of choice and can be avoided.
August 6, 2012 at 7:04 pm
SomewhereSomehow (8/3/2012)
When you asking about alternate solution you, do mean funtctional equivalent? you may use case+group by instead of pivot, and cross apply instead of unpivot. You may also check out these article about unpivot[/url].
Wow! You quoted me!
Thanks for taking the time to read my article. I was wondering who that was. 😛
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 6, 2012 at 10:21 pm
dwain.c (8/6/2012)
SomewhereSomehow (8/3/2012)
When you asking about alternate solution you, do mean funtctional equivalent? you may use case+group by instead of pivot, and cross apply instead of unpivot. You may also check out these article about unpivot[/url].Wow! You quoted me!
Thanks for taking the time to read my article. I was wondering who that was. 😛
Wow!!....Don't forget me Dwain now that you have become all famous after writing that article.....lol 😀
August 6, 2012 at 10:25 pm
vinu512 (8/6/2012)
dwain.c (8/6/2012)
SomewhereSomehow (8/3/2012)
When you asking about alternate solution you, do mean funtctional equivalent? you may use case+group by instead of pivot, and cross apply instead of unpivot. You may also check out these article about unpivot[/url].Wow! You quoted me!
Thanks for taking the time to read my article. I was wondering who that was. 😛
Wow!!....Don't forget me Dwain now that you have become all famous after writing that article.....lol 😀
How could I forget you Vinu, despite the fame and glory of being published! Now, all that awaits is the fortune coming my way and I'll be set. 😀
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 6, 2012 at 10:35 pm
dwain.c (8/6/2012)
vinu512 (8/6/2012)
dwain.c (8/6/2012)
SomewhereSomehow (8/3/2012)
When you asking about alternate solution you, do mean funtctional equivalent? you may use case+group by instead of pivot, and cross apply instead of unpivot. You may also check out these article about unpivot[/url].Wow! You quoted me!
Thanks for taking the time to read my article. I was wondering who that was. 😛
Wow!!....Don't forget me Dwain now that you have become all famous after writing that article.....lol 😀
How could I forget you Vinu, despite the fame and glory of being published! Now, all that awaits is the fortune coming my way and I'll be set. 😀
All my wishes are with you Dwain....keep learning and keep writing stuff like that so that Learners like me can learn from your Experiences....All the Best!!
August 6, 2012 at 10:38 pm
What? You mean you read it too, Vinu?
An interesting use case just appeared here: http://www.sqlservercentral.com/Forums/Topic1339633-392-3.aspx#bm1340977
The discussion thread also sheds further light on its performance, just in case you didn't go through it. There's some more research to do, but it looks pretty good overall on performance.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 6, 2012 at 10:57 pm
vinu512 (8/4/2012)
Using Pivot/unpivot could be more than just a matter of choice. You can definitely avoid the Pivot with Case+Group but then you come across situations where you can't use Case+Group. This could be because the columns are not Static and you need to get the Columns Dynamically.In these situations you can't avoid the Pivot and have to go with a Dynamic CrossTab.
For other situations it is a matter of choice and can be avoided.
Here's a different take on how to make dynamic cross tabs, Vinu.
http://www.sqlservercentral.com/articles/Crosstab/65048/
--Jeff Moden
Change is inevitable... Change for the better is not.
August 8, 2012 at 1:18 am
Jeff Moden (8/6/2012)
vinu512 (8/4/2012)
Using Pivot/unpivot could be more than just a matter of choice. You can definitely avoid the Pivot with Case+Group but then you come across situations where you can't use Case+Group. This could be because the columns are not Static and you need to get the Columns Dynamically.In these situations you can't avoid the Pivot and have to go with a Dynamic CrossTab.
For other situations it is a matter of choice and can be avoided.
Here's a different take on how to make dynamic cross tabs, Vinu.
Yes Jeff, I had read that article before. It is very good. Infact, i think it is the ultimate CrossTab Solution. But I referred the other one as I thought it was simpler for the OP.
I thought for a start the OP needs something easy and then if he spends enough time on this forum someone would definitely give him the link to your solution.
But, like I always say, Very well done Mr. Jeff Moden. 🙂
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply