Your content is what I am following from my initial days of learning sql. If possible please make more practise videos on sql with concepts every week/weekend so that our logic buildng can increase week by week. Thanks for the good content.
@Nizam_Ul_Mulk6 ай бұрын
Bro you are one of the best trainers I could ever come across. To the point and the crystal clear. Thanks one again.
@SantanuRoy-oq2nv Жыл бұрын
Taufiq sir, absolutely clear the "without ORDER BY clause in postgre SQL Crosstab"...what ever customer is visible as per the sequence , CROSSTAB is grouping those visible data. That's why ORDER BY is mandatory otherwise this weird type data will be show. Thank you sir
@samadixyachine8422 Жыл бұрын
Although i am not an English speaker, that does not prevent me from following you. It is very well explained with well chosen examples. I learned a lot thanks to you. Believe me you are the best. I copied the exact same query using crosstab and when i added the second argument i got the error : column ("Jan-21") does not exist. I hope you can explain the origin of the problem to me. GREAT THANKS (i replaced ("Jan-21") with (' 'Jan-21' ') and it worked)
@IainMace2 жыл бұрын
Outstanding. worked perfectly (for MySql). Thanks heaps
@techTFQ2 жыл бұрын
Glad to hear that
@mahamuthashameem3250 Жыл бұрын
till i watched this video, i was scared about pivot query, now i can understand it easily. thank yo so much for the video
@pw60482 жыл бұрын
Good video, but for SQL Server i would recommend you look at the GROUPING SETS command. With that you can rewrite your query to below : When using "set statistics time on;" you can see it beeing a lot faster. ;with inner_data as ( select customer_id as Customer ,format(sales_date, 'MMM-yy') as sales_date ,cast(replace(amount, '$', '') as int) as amount from sales_data ) select * from ( select isnull(Customer, 'Total') as Customer, isnull(sales_date, 'Total') as sales_date, case when sign(sum(amount)) = -1 then concat('(', ABS(sum(amount)), ')$') else concat(sum(amount), '$') end as amount from ( SELECT a.Customer, b.sales_date, 0 as amount from inner_data a inner join inner_data b on a.Customer b.Customer and a.sales_date b.sales_date union all select Customer ,sales_date ,amount from inner_data ) a group by GROUPING SETS( (Customer, sales_date), (Customer),(sales_date)) ) as sales pivot ( max(amount) for sales_date in ([Jan-21], [Feb-21], [Mar-21], [Apr-21] ,[May-21], [Jun-21], [Jul-21], [Aug-21] ,[Sep-21], [Oct-21], [Nov-21], [Dec-21],[Total]) ) as pivot_table
@DHARMYOGCOM2 жыл бұрын
this is a great one. thanks. i always wanted to do pivot queries. never really got around to it. now i got to learn it with my current project. very helpful video. thanks
@DHARMYOGCOM2 жыл бұрын
thanks again. I just completed my report. Just have few little things to add but this video was very handy.
@Anonymous_it_is2 жыл бұрын
Everytime you upload a new video, it brings abundance of knowledge in our lives. Thanks for this quality content. Maza aa jata hai aapka explanation sunke😁 Please consider making videos on topics such as Indexing(in depth) and database designing if possible.
@techTFQ2 жыл бұрын
Thank you Rohit.. Very happy to read your positive feedback ❤️
@nataliebear9011 Жыл бұрын
This is the best crosstab explanation I've seen. Thank you!!!
@MOTIVAO Жыл бұрын
Your videos are quite incredible, thank you Thoufiq for your patience and knowledge.
@abhijeetwadkar25642 жыл бұрын
Just wanted to say Thanks!!! Your sql joins video helped me crack my first tech interview. Kep up the good work brother.....
@techTFQ2 жыл бұрын
thank you, Great to hear Abhijeet!
@kancharalaparameshwarreddy38372 жыл бұрын
Good video sir it is very help full and iam alway thankful to you sir ,sir kindly provide every week one imortant video
@techTFQ2 жыл бұрын
Thank you bro.. And sure will try doing it bro
@bobhutchins5572 жыл бұрын
What an outstanding video! Ver well done! Thank you for putting it together. I have a question, though. I'm primarily a MySQL users but also use SQL Server. How could we set this up to be more dynamic? For example, let's say your column table covered 24 months and had 5 customers. Instead of writing out 24 lines of code each time (1 for each column) and then 24 lines multiple times, is there a way to write much fewer lines using variables, loops and a WITH clause? Thanks again - your videos have taught me so, so much!
@olganikitochkina85265 ай бұрын
This is the best explanation! Thank you so much ❤
@pitou00 Жыл бұрын
that was some useful stuff man, thankyou so much. mark my words "im going to get a data analyst job soon " and you are playing very important role in my journey.
@nada-rw6iy6 ай бұрын
I hope you got the job you wanted, any updates? I just started to understand the fundamentals of data engineering and I would love to hear how your journey is going and what helped you the most
@LuLu-in3ix5 ай бұрын
your video is very helpful for me. thank you. 👍
@bala95352 жыл бұрын
Thank you so much... helping multi tools at same time...
@sandeepkhawas33672 жыл бұрын
I m so thankful to you brother....i must say video is very informative which is helping me to increase my skills on SQL...!! 👍
@techTFQ2 жыл бұрын
thank you Sandeep :)
@innominatesoloist15972 жыл бұрын
My God...........so many learnings in just one video
@techTFQ2 жыл бұрын
thanks bro
@Beant19802 жыл бұрын
Thanks for the explanation. But instead of writing the names of Columns like JAN-21, FEB-21 inside PIVOT I use the function STUFF which does it dynamically. So, we can avoid writing the column names.
@muhammedsiraj62472 жыл бұрын
Can you please write the syntax ?
@Beant19802 жыл бұрын
@@muhammedsiraj6247 try this..... 1. SELECT @ColumnNames =STUFF(( SELECT DISTINCT ',' + QUOTENAME(PersonType) FROM databasename FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)'),1,1,'') 2. PRINT @ColumnNames 3. PIVOT ( COUNT(PhoneNumberTypeID) FOR PersonType IN (' + @ColumnNames + ') ) AS PIVOTTABLE' I have above points in a procedure, I can share the entire code with you. Thanks
@yassirnakkach51712 жыл бұрын
@@Beant1980 Hi Beant, can you please share the entire code with us? Many thanks!
@Beant19802 жыл бұрын
@@yassirnakkach5171 Hi, I have the code written in Microsoft SQL Server for AdventureWorks2016 database. I can email it you the text file?
@investshares83932 жыл бұрын
Hi Beant, can you pls also share the entire code with me pls. Thanks so much
@qurdedu40322 жыл бұрын
Tofik, you are legend!
@anandpaulmangalam2192 Жыл бұрын
Tf...awesome and great work. Although I have been writing SQL for several years, i could learn few new tips and tricks from you. I have a suggestion. Can you come up with a video that shows how to calculate cumulative totals and average totals on a rolling window. for e.g i need to calculate a 9 quarter ( 27months) average balance and cumulative losses. I have seen your window function video but that video lacks a real life problem solving component
@sonalivv2 жыл бұрын
This was a great video! I also loved how well you presented it, explaining every little detail. That way it is easy to remember the syntax. Thank you!! This was super helpful. Earlier I used to do sum() or max() to get a pivot output in postgres. crosstab will make my life a lot easier. One question - Is there a way we can dynamically populate the values for "as" and "values". For instance, instead of months, I have a list of products (which is dynamically populated) and I want to pivot customer against products. Is this possible in postgres?
@techTFQ2 жыл бұрын
Thank you 🙏🏼 Instead of values you can provide an sql query as the second argument in crosstab.. But this query should just return 1 column
@sonalivv2 жыл бұрын
@@techTFQ Thank you!! I will try this 🙏🏼
@nishantgupta27472 жыл бұрын
Hi Taufiq Just one word you are the best !!!! when it comes to sql
@raheemaslam67482 жыл бұрын
🤩 you deserve millions subscribers bro
@techTFQ2 жыл бұрын
Thank you so much :)
@arunv19092 жыл бұрын
honestly thank you for the video , but i learnt sub query in a different way , and you adding alias name in front and few things made me confuse a bit tbh , i understood the concept , thank you
@techTFQ2 жыл бұрын
thanks but don't confuse this with subquery. Ive made separate video for subquery.
@arunv19092 жыл бұрын
@@techTFQ but you wont call this a subquery? ohh , i thought the end part as corelated subquery
@techTFQ2 жыл бұрын
This is the syntax for pivot operator..
@sohailahmed35032 жыл бұрын
Garrett Evans Wow thank you Garrett, you've made my day!
@santoshkumarchannel80872 жыл бұрын
I also searching the same....But finally got from you Thank you
@maliktauqeer6902 жыл бұрын
Good video. You can wrap base query in (Double Dollar) $$ SUBQUERY $$ for postgres. Then you don't need to escape single quotes and makes it easy to read and write subquery.
@AliBuKarrar7 ай бұрын
Thank you so much! Just a quick note, in oracle we can use coalesce.
@karunakaranr2473 Жыл бұрын
Very helpful and thank you for your time and effort to make this excellent video.
@Braiam2 жыл бұрын
While useful this video confirms one of my believes: that pivoting on the database has the potential to be messy, and it's best dealt with in the application.
@faranbaig6486 Жыл бұрын
Thanks for the clarity Taufiq! I'm wondering if Crosstab allows date ranges to be selected dynamically. Instead of hard coding the months, is there a way for it to pick up whatever months the data has?
@sharukhbilal57102 жыл бұрын
Bro kindly make video on regular expressions from scratch till advanced level
@techTFQ2 жыл бұрын
Noted bro will consider it
@jarodelaro2 жыл бұрын
Mannnn you are simply the best ever, thanks so much, it helps a lottttttttttt!!!!
@techTFQ2 жыл бұрын
Glad to hear that ☺️
@kushalbaral8551 Жыл бұрын
Great content, helped with exactly what I needed for my current task at work. Thanks a lot !
@amitpandey92222 жыл бұрын
Your every content are amazing and very well explained.Can you please create playlist around SQL query tuning and performance?
@techTFQ2 жыл бұрын
Thank you Amit , glad you like them.. And noted on the suggestion
@amitpandey92222 жыл бұрын
@@techTFQ thank you
@majidrooinparvizi8649 Жыл бұрын
Following and enjoying your videos. But in these cases, isn't it just much easier and faster to do these transformations in Excel and import the results into sequel tables for further analysis if needed?
@martinsenobong924110 ай бұрын
Great recourse for learning SQL
@techTFQ9 ай бұрын
Thank you ☺️
@hanshubassa97352 жыл бұрын
Great explanation sir
@techTFQ2 жыл бұрын
Thank you buddy
@satheeshkumarak67082 жыл бұрын
Brilliant one Bro. Waiting for Unpivot and Regular Expressions 😅
@techTFQ2 жыл бұрын
Thank you and noted bro
@ramakumarguntamadugu12992 жыл бұрын
Great 👍 content as always 👌 thank you
@techTFQ2 жыл бұрын
Glad you liked it buddy
@sunnygawande52832 жыл бұрын
One video on data cleaning in sql
@techTFQ2 жыл бұрын
Sure bro will do it
@ManiKandan-kg5ky2 жыл бұрын
Hi when will your next batch starts?
@techTFQ2 жыл бұрын
Probably in August
@srh10344 ай бұрын
@techtfq: Of all your videos this one is the most confusing and messy! Just jumping from here to there! Wish you could do more organized considering how good you are in explaining complex matters in simple ways!
@crazydot812 жыл бұрын
Awesome explanation! As always. Great thanks!
@techTFQ2 жыл бұрын
Glad you liked it :)
@martinberger3656 ай бұрын
You are a Big treasure, bro. ❤
@iswillia12310 ай бұрын
Thanks for the content you put out, Taufiq. One question: can't you put the coalesce/NVL in the base query? Then you wouldn't have to put 12 such statements after the pivot.
@addhyasumitra902 ай бұрын
mind blowing :)
@匿名-x5m2 жыл бұрын
This video saved me! Thank you sir.
@Manish-h7j8x2 жыл бұрын
Great content tauphik sir, can you please create a separate video like this for unpivot...
@techTFQ2 жыл бұрын
Sure bro will do it
@esteban35556 ай бұрын
in oracle, coalesce is definitely supported! 👍🏽
@Tarunkumar-zw6sd3 ай бұрын
can you make a video on transactions please. Appreciate your work ,Thank you.
@obbubhanuchandra97292 жыл бұрын
Do video to PLSQL real- time querys bro This video you do is help many persons bro
@Mayank-jw9yy3 ай бұрын
Amazing work please also share raw data of this amazing problem
@grow_your_faith Жыл бұрын
Great work techTFQ
@likitha-sneha Жыл бұрын
Thanks for the explanation. Ur explanations are crystal clear. I subscribed to ur channel..☺️
@vablestory2.0 Жыл бұрын
awesome taufiq....you are a star
@sumibits2 жыл бұрын
Thank you Taufiq. This is really helpful. I have one quick question. All period which you have used in example and query are kind of static one (Jan21, Feb21 …). How do we make it more dynamic? Meaning, what if we have updated sales data for year 23 or 24… changing period value in query may not be good idea in long run. Any thoughts on this ?
@alexanderbykovdev73642 жыл бұрын
I haven't tried it yet, but can you try to use a query for the "for column value in ()" select and make some kind of distinct month-year in the same query?
@MiningForPies2 жыл бұрын
In SQL server dynamic pivots are not supported natively. You would need to generate the SQL as a text string and run sp_execute sql on that. It’s a really big missing feature, stops me using pivots at all just not worth the hassle
@jasonbaker743729 күн бұрын
great content as always, just a heads up blog link is broken.
@winstonsmith8597 Жыл бұрын
MMM-YY, Great, some developer in the future will now need to Y3K proof the code. :)
@LogeshRagupathi-g9c Жыл бұрын
Hello Sir, Very good example about transpose method in SQL using PIVOT as well as its equivalent in other RDBMS. Kindly clarify whether there is any method to make IN part in pivot dynamic instead of hardcoding. Thank you
@samiali5833 Жыл бұрын
You have great content
@padmavathisonnaila66002 жыл бұрын
Hi your videos are very helpful to me and I m begginer bit confused what is the difference between mysql,sql server, oracle,posgresql,plsql can u make a video on it please. Thank you in advance
@adzplus12 жыл бұрын
Awesome. Great video! Much helpful
@jawilson61 Жыл бұрын
Really cool stuff
@Christsermons Жыл бұрын
Great one thanks boss
@arefbegh52987 ай бұрын
Tqsm sir ❤️❤️
@rajudasari84822 жыл бұрын
Good Approach, can we have one video on UN Pivot same scenario, and what about if the new month is added it won't be dynamic. Any approach?
@babun4915 Жыл бұрын
Great work! Can we have a content with the comparison of oracle and ms sql ... that helps in migration of entire oracle to ms sql in terms of simple query to stored procedures
@kaushikbhide93872 жыл бұрын
Thank You 👏👏 great content
@techTFQ2 жыл бұрын
Glad you think so!
@abdullahyahya24712 жыл бұрын
Great Video. Love you. #noHomo.
@baburao25 Жыл бұрын
Thanks for the in detail explanation. Can you provide WEBAPI for the same
@cooldwnload Жыл бұрын
good work 👍
@satishdandangi54992 жыл бұрын
Sir, Please mention queries for oracle too
@techTFQ2 жыл бұрын
I have already given Oracle query here. Do check the video
@shreyanshfaye37142 жыл бұрын
@@techTFQ Please try for weekend classes this time🙏🙏
@chocoloaga575 Жыл бұрын
hi Sir, one Query. Can we use the pivot command without an aggregate function? Your Suggestions on this please.
@kancharalaparameshwarreddy38372 жыл бұрын
Good morning sir,next week plz provide on indexes And json
@Anvesh2013 Жыл бұрын
12:00 is there no way to generate those columns with months programatically, (procedurally) instead of hardcoding them
@MubarakAli-qs9qqАй бұрын
U are a good person subhan allah
@jamesopoku80492 жыл бұрын
Hi sir, good work done, please, may we get some videos on TRIGGER in postgresql....? Thanks
@techTFQ2 жыл бұрын
Thank you and sure will do
@Gate_Tricks2 жыл бұрын
sir can we visualize the data through sql... like can we make graphs by using sql......
@techTFQ2 жыл бұрын
Not really,, we generally use visualization tools like power bi or tableau for such things
@kannang72334 ай бұрын
how to provide dynamic values in pivot funcition IN clause instead of constant values
@anburenganathan83692 жыл бұрын
Thanks thoufig.. Is it possible make column names are dynamic.. I felt you are hard coding?
@winattempts98352 жыл бұрын
Hi pls tell if recorded lecture available? and any price benifit compared to live? Please
@Iceeboltt5 ай бұрын
Sir one important doubt can we work in company and also post tech videos and tutorials as a freelancer will this be considered dual employment/moonlighting?
@merkleinc26482 жыл бұрын
It was asked in one of my interview. And I was like yaa allah iske aage bhi kuch hota hai kya
@vijayendrasdm2 жыл бұрын
Hey Taufiq When are you planning to start fresh sql course ? Looking forward to enrol. Thanks.
@mistypokeman32612 жыл бұрын
Hi, your videos are very helpful. Could you please upload the data sets you used in this video? I've checked in discord but I did not find any. Appreciate all your efforts 👍
@sighupcmd Жыл бұрын
PG: passing sql as string argument is just awful solution, can we do better, and not using extensions? Here's the request for your next video :) Thanks!
@brianligat2038 Жыл бұрын
The totals were a bit messy. Could you have added Total values along with the Customers and Months and created that with an initial WITH clause?
@chandrageetham2 жыл бұрын
would you please deliver a class on clustered and nonclustered indices
@celebrationsthecelebschoic5752 жыл бұрын
Can't we use distinct customer in select, add both date and customer in group by, and then use sum(amount)??
@ramanat14242 жыл бұрын
please tell me PL/SQL, POSTGRESQL both same or different?
@AmanKumar-bi4jl2 жыл бұрын
Hey, I have two table one is student table with the field studentID, Name and other details and another is Attendance table with the field studentID and Date. I want to select record for each student for each day, fill record with the latter 'P' as present and 'A' as absent. How can I do??
@baradhwajgudepu7110 Жыл бұрын
Thank you taufiq, i struck in converting columns to rows can you help in doing that please
@sayantankar91412 жыл бұрын
can you expalin that last part where you are finding the total respective of each column. Please explain the query you used in mysql
@amulyaboda31262 жыл бұрын
Sir if you don't mind can you make a video on analytical functions in sql
@techTFQ2 жыл бұрын
I have already made it Amulya.. Analytics functions are also called as window function so you can look for window functions videos on my channel
@solvedit9 ай бұрын
What will be the solution where columns varies based on customer names instead of month names as column. I want to say that month are fixed to 12 columns but customers are dynamic & any time a new customer can be inserted in our record. I need customer names in columns & Month-Year in rows.
@amitsrivastava60938 ай бұрын
Hi Taufiq, thanks for sharing, all your videos are awesome. is there a way to keep month in row wise and custumer name will show region wise in column? awaiting your answer..i have sent an email to you on this, could you please respond?