{"id":1161,"date":"2010-09-29T11:52:33","date_gmt":"2010-09-29T06:22:33","guid":{"rendered":"http:\/\/JitendraZaa.com\/blog\/?p=1161"},"modified":"2010-09-29T11:52:33","modified_gmt":"2010-09-29T06:22:33","slug":"sql-server-pivot-table-example","status":"publish","type":"post","link":"https:\/\/www.jitendrazaa.com\/blog\/sql\/sqlserver\/sql-server-pivot-table-example\/","title":{"rendered":"SQL Server Pivot Table Example"},"content":{"rendered":"

A pivot table<\/strong> is a frequently used method of summarizing and displaying especially report data by means of\u00a0grouping<\/strong> and\u00a0aggregating<\/strong> values.
\nPivot tables<\/strong> are easily created by office users using Microsoft Excel or MS Access.
\nSince\u00a0pivot table<\/strong> enables report builders and BI (Business Intelligence) specialists empower their presentation of reports and increase the visibility and\u00a0understandability\u00a0of mined data, pivot tables are common and preferred widely.<\/p>\n

Pivot tables<\/strong> display data in\u00a0tabular form<\/strong>. The\u00a0pivot table formatting<\/strong> is not different than a tabular report formatting.
\nBut the table columns are formed by the report data itself.<\/p>\n

Microsoft SQL Server<\/strong> has introduced the\u00a0PIVOT<\/strong> and\u00a0UNPIVOT<\/strong> commands as enhancements to t-sql with the release of MS SQL Server 2005.<\/p>\n

<\/p>\n

In MS SQL Server 2008, we can still use the PIVOT command and UNPIVOT command to build and use pivot tables in sql.
\nT-SQL Pivot and Unpivot statements will\u00a0transform<\/strong> and\u00a0rotate<\/strong> a tabular data into an other table value data in sql .
\nSince Pivot \/ Unpivot are SQL2005 t-sql enhancements, databases which you want to execute pivot and unpivot commands should be at least at compatibility level 90 (SQL2005) or 100 (SQL2008).<\/p>\n

T-SQL Pivot Syntax<\/strong><\/p>\n

\nSELECT\n  [non-pivoted column], -- optional\n  [additional non-pivoted columns], -- optional\n  [first pivoted column],-- optional\n [additional pivoted columns]\nFROM (\n SELECT query producing sql data for pivot\n -- select pivot columns as dimensions and\n -- value columns as measures from sql tables\n) AS TableAlias\nPIVOT\n(\n <aggregation function>(column for aggregation or measure column) -- MIN,MAX,SUM,etc\n FOR [<column name containing values for pivot table columns>]\n IN (\n [first pivoted column], ..., [last pivoted column]\n )\n) AS PivotTableAlias\nORDER BY clause -- optional\n<\/pre>\n
 Example SQL server 2005:<\/strong><\/p>\n
\ncreate table order_rep (iteNag varchar(10), mnth int, year int, ordermade int)\n\ninsert into order_rep values('Shiva',1,2009, 5)\ninsert into order_rep values('Shiva',2,2009, 6)\ninsert into order_rep values('Shiva',1,2009, 10)\ninsert into order_rep values('Shiva',4,2009, 5)\ninsert into order_rep values('Shiva',5,2009, 7)\ninsert into order_rep values('Shiva',2,2009, 5)\ninsert into order_rep values('Shiva',1,2009, 4)\ninsert into order_rep values('Shiva',6,2009, 15)\ninsert into order_rep values('Shiva',8,2009, 8 )\ninsert into order_rep values('Shiva',3,2010, 5)\ninsert into order_rep values('Shiva',5,2010, 7)\ninsert into order_rep values('Shiva',12,2010, 5)\ninsert into order_rep values('Shiva',11,2010, 4)\ninsert into order_rep values('Shiva',1,2010, 7)\ninsert into order_rep values('Shiva',5,2010, 5)\n\ninsert into order_rep values('Soft',2,2009, 6)\ninsert into order_rep values('Soft',4,2009, 7)\ninsert into order_rep values('Soft',2,2009, 4)\ninsert into order_rep values('Soft',3,2009, 5)\ninsert into order_rep values('Soft',5,2009, 7)\ninsert into order_rep values('Soft',12,2009, 12)\ninsert into order_rep values('Soft',11,2009, 4)\ninsert into order_rep values('Soft',1,2009, 9)\ninsert into order_rep values('Soft',5,2009, 4)\ninsert into order_rep values('Soft',3,2009, 5)\ninsert into order_rep values('Soft',4,2010, 7)\ninsert into order_rep values('Soft',1,2010, 1)\ninsert into order_rep values('Soft',4,2010, 4)\ninsert into order_rep values('Soft',2,2010, 9)\ninsert into order_rep values('Soft',5,2010, 4)\n\ninsert into order_rep values('Nag',1,2009, 5)\ninsert into order_rep values('Nag',3,2009, 6)\ninsert into order_rep values('Nag',5,2009, 8 )\ninsert into order_rep values('Nag',12,2009, 23)\ninsert into order_rep values('Nag',9,2009, 45)\ninsert into order_rep values('Nag',5,2009, 3)\ninsert into order_rep values('Nag',1,2009, 5)\ninsert into order_rep values('Nag',4,2009, 3)\ninsert into order_rep values('Nag',3,2009, 9)\ninsert into order_rep values('Nag',3,2010, 5)\ninsert into order_rep values('Nag',5,2010, 7)\ninsert into order_rep values('Nag',12,2010, 12)\ninsert into order_rep values('Nag',11,2010, 4)\ninsert into order_rep values('Nag',1,2010, 9)\ninsert into order_rep values('Nag',5,2010, 4)\n<\/pre>\n
Now the script using Pivot table is:<\/strong><\/p>\n
\nSELECT *\nFROM (\nSELECT IteNag\n        , cast(Year as varchar(4)) + ' ' + CONVERT(varchar(3), dateadd(m, mnth, -1), 107) as MnthName\n        , OrderMade\nFROM Order_rep\n) P\nPIVOT (\nSUM(OrderMade)\nFOR MnthName IN ([2009 Apr], [2009 May], [2009 Jun], [2009 Jul], [2009 Aug])\n) AS PVT\ndrop table order_rep\n<\/pre>\n
Output:<\/strong><\/p>\n
\"SQL<\/a>
SQL Server Pivot Table Output<\/figcaption><\/figure>\n","protected":false},"excerpt":{"rendered":"
Example and tutorial of using PIVOT in SQL Server<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"advanced_seo_description":"","jetpack_seo_html_title":"","jetpack_seo_noindex":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"jz_research_post":"","_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_post_was_ever_published":false},"categories":[14],"tags":[292],"class_list":["post-1161","post","type-post","status-publish","format-standard","hentry","category-sqlserver","tag-sql-server"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack-related-posts":[{"id":448,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/search-for-table-in-sql-server\/","url_meta":{"origin":1161,"position":0},"title":"Search for Table in SQL Server","author":"Jitendra","date":"June 30, 2010","format":false,"excerpt":"In\u00a0organization, where huge number of tables present in database. its very hard to search the particular table. Here few SQL queries to search the table name by pattern. Search For Table whose name starts with A [sourcecode lang=\"sql\"] SELECT DISTINCT [Table] = OBJECT_NAME(OBJECT_ID) FROM SYS.INDEXES WHERE OBJECTPROPERTY(OBJECT_ID,'IsUserTable') = 1 AND\u2026","rel":"","context":"In "SQL"","block_context":{"text":"SQL","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/"},"img":{"alt_text":"List of Non clustered Tables in SQL Server 2005","src":"https:\/\/i0.wp.com\/jitendrazaa.com\/blog\/wp-content\/uploads\/2010\/06\/Non-clustered-Tables.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":1125,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/sqlserver\/get-table-information-column-name-data-type-in-sql-server\/","url_meta":{"origin":1161,"position":1},"title":"Get Table information (Column Name, Data Type) in SQL Server","author":"Jitendra","date":"September 18, 2010","format":false,"excerpt":"Get Table information like Column Name, Data Type, Character length, Default Values etc in SQL Server","rel":"","context":"In "SQL Server"","block_context":{"text":"SQL Server","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/sqlserver\/"},"img":{"alt_text":"SQL Server INFORMATION_SCHEMA.COLUMNS","src":"https:\/\/i0.wp.com\/jitendrazaa.com\/blog\/wp-content\/uploads\/2010\/09\/SQL-Server-INFORMATION_SCHEMA.COLUMNS-300x44.jpg?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":656,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/sqlserver\/difference-in-var-char-and-nvar-char-in-sql-server\/","url_meta":{"origin":1161,"position":2},"title":"Difference in “varchar” and “nvarchar” in SQL Server","author":"Jitendra","date":"July 15, 2010","format":false,"excerpt":"What is the difference between varchar and nvarchar in MS SQL Server.","rel":"","context":"In "SQL Server"","block_context":{"text":"SQL Server","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/sqlserver\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":5873,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/sqlserver\/export-documents-saved-as-blob-binary-from-sql-server\/","url_meta":{"origin":1161,"position":3},"title":"Export Documents saved as Blob \/ Binary from SQL Server","author":"Jitendra","date":"December 24, 2016","format":false,"excerpt":"T-SQL Scripts to Export Blob or Binary data stored in SQL Server","rel":"","context":"In "SQL Server"","block_context":{"text":"SQL Server","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/sqlserver\/"},"img":{"alt_text":"SQL Server Export Blob as File","src":"https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/12\/SQL-Server-Export-Blob.gif?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/12\/SQL-Server-Export-Blob.gif?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/12\/SQL-Server-Export-Blob.gif?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/12\/SQL-Server-Export-Blob.gif?resize=700%2C400&ssl=1 2x"},"classes":[]},{"id":665,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/execute-dynamic-query-in-sql-server\/","url_meta":{"origin":1161,"position":4},"title":"Execute dynamic Query in SQL Server","author":"Jitendra","date":"July 16, 2010","format":false,"excerpt":"How to execute the dynamic query in microsoft SQL Server 2005.","rel":"","context":"In "SQL"","block_context":{"text":"SQL","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1376,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/sqlserver\/use-of-openxml-in-sql-server\/","url_meta":{"origin":1161,"position":5},"title":"Use of OPENXML in SQL Server","author":"Jitendra","date":"December 4, 2010","format":false,"excerpt":"In SQL Server, OpenXML is very powerful method for the XML data manipulation. This article shows that how to use the OpenXML for XML string.","rel":"","context":"In "SQL Server"","block_context":{"text":"SQL Server","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/sqlserver\/"},"img":{"alt_text":"OpenXML in SQL Server","src":"https:\/\/i0.wp.com\/jitendrazaa.com\/blog\/wp-content\/uploads\/2010\/12\/OpenXML-in-SQL-Server.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/posts\/1161","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/comments?post=1161"}],"version-history":[{"count":0,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/posts\/1161\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/media?parent=1161"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/categories?post=1161"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/tags?post=1161"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}