During working with one logic, I got a chance to work with PIVOT operation. Sometime we need do require row data as a column in our custom logic, then we can use some temp table and then populate aggregate data in a temp table.
But With PIVOT we can do it very easily. Let me prepare small example
and explain as how how can we use PIVOT and get row data as a column.
Before going ahead to run the script of Pivot, we will create a database and table objects.
You can review here and see how The PIVOT is working. Let me share your experience with PIVOT operation.
Before going ahead to run the script of Pivot, we will create a database and table objects.
CREATE DATABASE DEMO GO USE DEMO GO -- Creating table for demo IF (object_id('TblPivot','U') > 0) DROP TABLE TblPivot CREATE TABLE TblPivot ( ItemCode int, ItemName varchar(100), ItemColour varchar(50) ) GO -- Inerting some sample records INSERT INTO TblPivot SELECT 1,'Samsung Mobile','Red' UNION ALL SELECT 2,'Nokia Mobile','Blue' UNION ALL SELECT 3,'Nokia Mobile','Green' UNION ALL SELECT 4,'Motorola Mobile','Red' UNION ALL SELECT 5,'Samsung Mobile','Green' UNION ALL SELECT 2,'Nokia Mobile','Blue' UNION ALL SELECT 1,'Samsung Mobile','Red' UNION ALL SELECT 2,'Nokia Mobile','Blue' GONow we will check the original table data and aggregated data using Pivot. So we will run both scripts for the same.
-- Getting table data SELECT ItemCode, ItemName, ItemColour from TblPivot GO -- Getting agreegated data using Pivot and converted rows to column SELECT * FROM ( SELECT ItemCode, ItemName, ItemColour FROM TblPivot ) AS P PIVOT ( Count(ItemName) FOR ItemColour IN (Red, Blue, Green) ) AS pv GO
You can review here and see how The PIVOT is working. Let me share your experience with PIVOT operation.
This works really well on known list of keys in the columns used to pivot. We used dynamic SQL to overcome this problem, it becomes less readable however.
ReplyDeleteMainly, do not let your programmers use the pivoted columns in WHERE clauses, performance drops dramatically.
Hi you are doing a great job. i was looking for this information. i found it on your page its really amazing. I like that information. I am sure that these are your own views. They are really awesome. You are really fantastic. Thanks Why didn’t I think about this? I hear exactly what you’re saying and I’m so happy that I came across your blog. You really know what you’re talking about, and you made me feel like I should learn more about this. Thanks for this; I’m officially a huge fan of your blog.
ReplyDeletecar transportation service
Hi, This is great information. Im a beginner and really need some help. I am trying to produce a view from a table which has a customer, date and balance field. I am trying to extract the month from the date field and use this as a column alias displaying the balance in the relevant month column along with the customer field. I have tried select case statements however these give all months of the year when I am trying to narrow it down to the past 3 months. Using a WHERE clause after the case statements is producing 2 tables of results. Any help would be much appreciated.
ReplyDeleteDionne
i dont know
Delete