Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Transpose rows into one column Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, July 8, 2014 5:34 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Saturday, May 14, 2016 10:32 AM Points: 102, Visits: 290
 yes, ChrisM's query is simple aswell it is more efficient and Optimized too..`--Estimated Subtree Cost = 0.0034222 (to calculate 21.6 rows)select Field from #tempunpivot UNPIVOT (Field for ColumnName IN ([unic],[nrc],[nr_tel],[id_stud])) unpvt--Estimated Subtree Cost = 0.0034131 (to Calculate 24 rows)SELECT NewColumn FROM #tempunpivot tCROSS APPLY (VALUES (unic), (nrc), (nr_tel), (id_stud)) d (NewColumn)`I would like to stick with "Cross Apply", Query thanks to ChrisM & Mouris..Regards,Prabhu
Post #1590305
 Posted Tuesday, July 8, 2014 5:41 AM
 SSCrazy Eights Group: General Forum Members Last Login: Yesterday @ 9:02 AM Points: 8,553, Visits: 18,612
 prabhu.st (7/8/2014)yes, ChrisM's query is simple aswell it is more efficient and Optimized too..`--Estimated Subtree Cost = 0.0034222 (to calculate 21.6 rows)select Field from #tempunpivot UNPIVOT (Field for ColumnName IN ([unic],[nrc],[nr_tel],[id_stud])) unpvt--Estimated Subtree Cost = 0.0034131 (to Calculate 24 rows)SELECT NewColumn FROM #tempunpivot tCROSS APPLY (VALUES (unic), (nrc), (nr_tel), (id_stud)) d (NewColumn)`I would like to stick with "Cross Apply", Query thanks to ChrisM & Mouris..Regards,PrabhuCROSS APPLY VALUES may or may not be quicker than UNPIVOT. There's usually not much in it - and CAV is usually easier to write and test. Dwain Camps has more details of the two methods in this excellent article. “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.Understanding and using APPLY, (I) and (II) Paul White Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff ModenExploring Recursive CTEs by Example Dwain Camps
Post #1590307

 Permissions