{"id":23063,"date":"2021-03-24T19:48:15","date_gmt":"2021-03-24T23:48:15","guid":{"rendered":"http:\/\/bangla.salearningschool.com\/recent-posts\/pivot-examples-sql-root-by-sayed-ahmed\/"},"modified":"2021-03-24T19:48:15","modified_gmt":"2021-03-24T23:48:15","slug":"pivot-examples-sql-root-by-sayed-ahmed","status":"publish","type":"post","link":"http:\/\/bangla.sitestree.com\/?p=23063","title":{"rendered":"PIVOT Examples – SQL #Root #By Sayed Ahmed"},"content":{"rendered":"

http:\/\/stackoverflow.com\/questions\/24470\/sql-server-pivot-examples<\/p>\n

SELECT<\/span> Action<\/span>,<\/span>\r\n       MAX<\/span>(<\/span> CASE<\/span> data <\/span>WHEN<\/span> 'View'<\/span> THEN<\/span> data <\/span>ELSE<\/span> ''<\/span> END<\/span> )<\/span> ViewCol<\/span>,<\/span> \r\n       MAX<\/span>(<\/span> CASE<\/span> data <\/span>WHEN<\/span> 'Edit'<\/span> THEN<\/span> data <\/span>ELSE<\/span> ''<\/span> END<\/span> )<\/span> EditCol\r\n <\/span>FROM<\/span> t\r\n <\/span>GROUP<\/span> BY<\/span> Action\r\n\r\n<\/span><\/code>---\r\n\r\n<\/pre>\n
SELECT<\/span> act <\/span>AS<\/span> 'Action'<\/span>,<\/span> [<\/span>View<\/span>]<\/span> as<\/span> 'View'<\/span>,<\/span> [<\/span>Edit<\/span>]<\/span> as<\/span> 'Edit'<\/span>\r\nFROM<\/span> (<\/span>\r\n    SELECT<\/span> act<\/span>,<\/span> cmd <\/span>FROM<\/span> data\r\n<\/span>)<\/span> AS<\/span> src\r\n<\/span>PIVOT<\/span> (<\/span>\r\n    MAX<\/span>(<\/span>cmd<\/span>)<\/span> FOR<\/span> cmd <\/span>IN<\/span> ([<\/span>View<\/span>],<\/span> [<\/span>Edit<\/span>])<\/span>\r\n)<\/span> AS<\/span> pvt\r\n\r\n<\/span><\/code>--\r\n\r\n<\/pre>\n
Table setup:<\/p>\n
CREATE<\/span> TABLE<\/span> dbo<\/span>.<\/span>tbl <\/span>(<\/span>\r\n    action VARCHAR<\/span>(<\/span>20<\/span>)<\/span> NOT<\/span> NULL<\/span>,<\/span>\r\n    view_edit VARCHAR<\/span>(<\/span>20<\/span>)<\/span> NOT<\/span> NULL<\/span>\r\n);<\/span>\r\n\r\nINSERT<\/span> INTO<\/span> dbo<\/span>.<\/span>tbl <\/span>(<\/span>action<\/span>,<\/span> view_edit<\/span>)<\/span>\r\nVALUES<\/span> (<\/span>'Action1'<\/span>,<\/span> 'VIEW'<\/span>),<\/span>\r\n       (<\/span>'Action1'<\/span>,<\/span> 'EDIT'<\/span>),<\/span>\r\n       (<\/span>'Action2'<\/span>,<\/span> 'VIEW'<\/span>),<\/span>\r\n       (<\/span>'Action3'<\/span>,<\/span> 'VIEW'<\/span>),<\/span>\r\n       (<\/span>'Action3'<\/span>,<\/span> 'EDIT'<\/span>);<\/span><\/code><\/pre>\n
Your table: SELECT action, view_edit FROM dbo.tbl<\/code><\/p>\n
\"Your<\/p>\n
Query without using PIVOT:<\/strong><\/p>\n
SELECT<\/span> Action<\/span>,<\/span> \r\n[<\/span>View<\/span>]<\/span> =<\/span> (<\/span>Select<\/span> view_edit <\/span>FROM<\/span> tbl <\/span>WHERE<\/span> t<\/span>.<\/span>action <\/span>=<\/span> action <\/span>and<\/span> view_edit <\/span>=<\/span> 'VIEW'<\/span>),<\/span>\r\n[<\/span>Edit<\/span>]<\/span> =<\/span> (<\/span>Select<\/span> view_edit <\/span>FROM<\/span> tbl <\/span>WHERE<\/span> t<\/span>.<\/span>action <\/span>=<\/span> action <\/span>and<\/span> view_edit <\/span>=<\/span> 'EDIT'<\/span>)<\/span>\r\nFROM<\/span> tbl t\r\n<\/span>GROUP<\/span> BY<\/span> Action<\/span><\/code><\/pre>\n
Query using PIVOT:<\/strong><\/p>\n
SELECT<\/span> [<\/span>Action<\/span>],<\/span> [<\/span>View<\/span>],<\/span> [<\/span>Edit<\/span>]<\/span> FROM<\/span>\r\n(<\/span>SELECT<\/span> [<\/span>Action<\/span>],<\/span> view_edit <\/span>FROM<\/span> tbl<\/span>)<\/span> AS<\/span> t1 \r\n<\/span>PIVOT<\/span> (<\/span>MAX<\/span>(<\/span>view_edit<\/span>)<\/span> FOR<\/span> view_edit <\/span>IN<\/span> ([<\/span>View<\/span>],<\/span> [<\/span>Edit<\/span>])<\/span> )<\/span> AS<\/span> t2<\/span><\/code><\/pre>\n
Both queries result:<\/strong>
\n\"enter<\/p>\n
 <\/p>\n
  From: http:\/\/sitestree.com\/?p=2040
 Categories:Root, By Sayed Ahmed
Tags:
 Post Data:2015-06-14 21:36:12<\/p>\n
\t\tShop Online: https:\/\/www.ShopForSoul.com\/<\/a>
\n\t\t(Big Data, Cloud, Security, Machine Learning): Courses:  http:\/\/Training.SitesTree.com<\/a>
\n\t\tIn Bengali: http:\/\/Bangla.SaLearningSchool.com<\/a>
\n\t\thttp:\/\/SitesTree.com<\/a>
\n\t\t8112223 Canada Inc.\/JustEtc: http:\/\/JustEtc.net (Software\/Web\/Mobile\/Big-Data\/Machine Learning) <\/a>
\n\t\tShop Online:  https:\/\/www.ShopForSoul.com\/<\/a>
\n\t\tMedium:  https:\/\/medium.com\/@SayedAhmedCanada <\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
http:\/\/stackoverflow.com\/questions\/24470\/sql-server-pivot-examples SELECT Action, MAX( CASE data WHEN ‘View’ THEN data ELSE ” END ) ViewCol, MAX( CASE data WHEN ‘Edit’ THEN data ELSE ” END ) EditCol FROM t GROUP BY Action — SELECT act AS ‘Action’, [View] as ‘View’, [Edit] as ‘Edit’ FROM ( SELECT act, cmd FROM data ) AS src PIVOT ( … <\/p>\n
Continue reading<\/a><\/p>\n","protected":false},"author":8,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[1917],"tags":[],"class_list":["post-23063","post","type-post","status-publish","format-standard","hentry","category-fromsitestree-com","item-wrap"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack-related-posts":[{"id":14810,"url":"http:\/\/bangla.sitestree.com\/?p=14810","url_meta":{"origin":23063,"position":0},"title":"SQL Server (SSAS): Data Mining: Data Science: Data Analytics: Prediction: Neural Networks : Linear\/Logistics Regression","author":"Sayed","date":"June 15, 2019","format":false,"excerpt":"SQL Server Analysis Service: Data Mining Algorithms (Analysis Services - Data Mining) https:\/\/docs.microsoft.com\/en-us\/sql\/analysis-services\/data-mining\/data-mining-algorithms-analysis-services-data-mining?view=sql-server-2017 -- Microsoft Association Algorithm https:\/\/docs.microsoft.com\/en-us\/sql\/analysis-services\/data-mining\/microsoft-association-algorithm?view=sql-server-2017 -- Association Model Query Examples https:\/\/docs.microsoft.com\/en-us\/sql\/analysis-services\/data-mining\/association-model-query-examples?view=sql-server-2017 -- Microsoft Clustering Algorithm https:\/\/docs.microsoft.com\/en-us\/sql\/analysis-services\/data-mining\/microsoft-clustering-algorithm?view=sql-server-2017 Clustering Model Query Examples https:\/\/docs.microsoft.com\/en-us\/sql\/analysis-services\/data-mining\/clustering-model-query-examples?view=sql-server-2017 -- Microsoft Time Series Algorithm https:\/\/docs.microsoft.com\/en-us\/sql\/analysis-services\/data-mining\/microsoft-time-series-algorithm?view=sql-server-2017 --- Time Series Model Query Examples https:\/\/docs.microsoft.com\/en-us\/sql\/analysis-services\/data-mining\/time-series-model-query-examples?view=sql-server-2017 --- Microsoft Neural\u2026","rel":"","context":"In "AI ML DS RL DL NN NLP Data Mining Optimization"","block_context":{"text":"AI ML DS RL DL NN NLP Data Mining Optimization","link":"http:\/\/bangla.sitestree.com\/?cat=1910"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":78258,"url":"http:\/\/bangla.sitestree.com\/?p=78258","url_meta":{"origin":23063,"position":1},"title":"Tools and Tutorials for Data Manipulation","author":"Sayed","date":"May 22, 2025","format":false,"excerpt":"Join Data from Multiple Sources \u2022Power BI \u2022Python \u2022SQL \u2022Databases and Data Warehouse \u2022https:\/\/durhamcollege.desire2learn.com\/d2l\/le\/content\/467097\/viewContent\/6376898\/View \u2022Data Modeling and SQL \u2022https:\/\/durhamcollege.desire2learn.com\/d2l\/le\/content\/467097\/viewContent\/6376900\/View \u2022Microsoft Power BI \u2022https:\/\/durhamcollege.desire2learn.com\/d2l\/le\/content\/467097\/viewContent\/6377023\/View Tutorials and Examples \u2022MySQL Data Manipulation: \u2022https:\/\/www.databasejournal.com\/mysql\/mysql-data-manipulation-and-query-statements\/ \u2022https:\/\/www.w3schools.com\/sql\/ \u2022https:\/\/www.tutorialspoint.com\/sql\/index.htm \u2022Workbench: https:\/\/www.tutorialspoint.com\/create-a-new-database-with-mysql-workbench \u2022SQL Server Data Manipulation \u2022https:\/\/www.tutorialspoint.com\/ms_sql_server\/index.htm \u2022Management Studio: \u2022https:\/\/www.tutorialspoint.com\/ms_sql_server\/ms_sql_server_management_studio.htm \u2022Power BI Data Manipulation \u2022https:\/\/learn.microsoft.com\/en-us\/power-bi\/connect-data\/desktop-tutorial-importing-and-analyzing-data-from-a-web-page \u2022Data Manipulation in\u2026","rel":"","context":"In "Analytics and Machine Learning Project Development"","block_context":{"text":"Analytics and Machine Learning Project Development","link":"http:\/\/bangla.sitestree.com\/?cat=1974"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":22049,"url":"http:\/\/bangla.sitestree.com\/?p=22049","url_meta":{"origin":23063,"position":2},"title":"PHP Date Time Examples #Software Development #Web Development #Root #By Sayed Ahmed","author":"Author-Check- Article-or-Video","date":"March 9, 2021","format":false,"excerpt":"You can use the following code where users can select a date range from a drop down with quick dates. Very often, you will also need a date range calendar; you can use jquery date picker tool for the purpose. if ($quickDates==\"today\"){ $dateFrom = date(\"Y-m-d\"); $dateTo = date(\"Y-m-d\", time() +\u2026","rel":"","context":"In "FromSitesTree.com"","block_context":{"text":"FromSitesTree.com","link":"http:\/\/bangla.sitestree.com\/?cat=1917"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":26553,"url":"http:\/\/bangla.sitestree.com\/?p=26553","url_meta":{"origin":23063,"position":3},"title":"LongLivedCookie.java  Subclass of Cookie that automatically sets the max age to one year. #Programming Code Examples #Java\/J2EE\/J2ME #Servlet","author":"Author-Check- Article-or-Video","date":"April 28, 2021","format":false,"excerpt":"LongLivedCookie.java Subclass of Cookie that automatically sets the max age to one year. package cwp; import javax.servlet.http.*; \/** Cookie that persists 1 year. Default Cookie doesn't * persist past current session. * * Taken from Core Web Programming Java 2 Edition * from Prentice Hall and Sun Microsystems Press, *\u2026","rel":"","context":"In "FromSitesTree.com"","block_context":{"text":"FromSitesTree.com","link":"http:\/\/bangla.sitestree.com\/?cat=1917"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":27162,"url":"http:\/\/bangla.sitestree.com\/?p=27162","url_meta":{"origin":23063,"position":4},"title":"Pointers #Programming Code Examples #Java\/J2EE\/J2ME #Ajax","author":"Author-Check- Article-or-Video","date":"May 12, 2021","format":false,"excerpt":"\/* The following code example is taken from the book * \"The C++ Standard Library - A Tutorial and Reference\" * by Nicolai M. Josuttis, Addison-Wesley, 1999 * * (C) Copyright Nicolai M. Josuttis 1999. * Permission to copy, use, modify, sell and distribute this software * is granted provided\u2026","rel":"","context":"In "FromSitesTree.com"","block_context":{"text":"FromSitesTree.com","link":"http:\/\/bangla.sitestree.com\/?cat=1917"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":22513,"url":"http:\/\/bangla.sitestree.com\/?p=22513","url_meta":{"origin":23063,"position":5},"title":"SQL Server: Basics of Dynamic Management Views and DBCC Commands #Root #By Sayed Ahmed #MS SQL Server #SQL Server Reporting","author":"Author-Check- Article-or-Video","date":"March 15, 2021","format":false,"excerpt":"Inspired By: Tuning Queries: SQl Server - http:\/\/salearningschool.com\/displayArticle.php?table=Articles&articleID=1156&title=Tuning%20Queries:%20SQl%20Server What are these? Dynamic Management Views, and DBCC Commands Dynamic Management Views: \"Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.\" From: http:\/\/msdn.microsoft.com\/en-us\/library\/ms188754.aspx DBCC Statements:\u2026","rel":"","context":"In "FromSitesTree.com"","block_context":{"text":"FromSitesTree.com","link":"http:\/\/bangla.sitestree.com\/?cat=1917"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=\/wp\/v2\/posts\/23063","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=\/wp\/v2\/users\/8"}],"replies":[{"embeddable":true,"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=23063"}],"version-history":[{"count":0,"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=\/wp\/v2\/posts\/23063\/revisions"}],"wp:attachment":[{"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=23063"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=23063"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=23063"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}