{"id":8211,"date":"2022-09-12T11:58:19","date_gmt":"2022-09-12T06:28:19","guid":{"rendered":"https:\/\/jassweb.com\/solved\/solved-sql-pivot-based-on-multiple-columns\/"},"modified":"2022-09-12T11:58:19","modified_gmt":"2022-09-12T06:28:19","slug":"solved-sql-pivot-based-on-multiple-columns","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-sql-pivot-based-on-multiple-columns\/","title":{"rendered":"[Solved] SQL Pivot based on multiple columns"},"content":{"rendered":"

[ad_1]
\n<\/p>\n

\n
\n
<\/div>\n
\n
\n

In order to get the result that you want you will have to apply both the UNPIVOT<\/code> and the PIVOT<\/code> functions.<\/p>\n

The unpivot<\/em> function will convert your Q1<\/code>, Q2<\/code>, Q3<\/code>, and Q4<\/code> columns into rows. Once that is done, you will apply the pivot<\/em> function.<\/p>\n

The unpivot code will be similar to this:<\/p>\n

select programid,\n  col + '_'+ category cat_col,\n  value\nfrom yourtable \nunpivot\n(\n  value\n  for col in (Q1, Q2, Q3, Q4)\n) unpiv\n<\/code><\/pre>\n
See SQL Fiddle with Demo<\/a>. This gives a result:<\/p>\n
| PROGRAMID |   CAT_COL |  VALUE |\n----------------------------------\n|       366 | Q1_People | 109034 |\n|       366 | Q2_People |  25418 |\n|       366 | Q3_People | 101130 |\n|       366 | Q4_People |  54787 |\n<\/code><\/pre>\n
You can see that this query creates a new column name to pivot which has the category value and the quarter name.  <\/p>\n
Once you have this result, you can apply the pivot function:<\/p>\n
select *\nfrom\n(\n  select programid,\n    col + '_'+ category cat_col,\n    value\n  from yourtable \n  unpivot\n  (\n    value\n    for col in (Q1, Q2, Q3, Q4)\n  ) unpiv\n) d\npivot\n(\n  sum(value)\n  for cat_col in (Q1_People, Q2_People, Q3_People, Q4_People,\n                  Q1_IT, Q2_IT, Q3_IT, Q4_IT,\n                  Q1_Travel, Q2_Travel, Q3_Travel, Q4_Travel)\n) piv\n<\/code><\/pre>\n
See SQL Fiddle with Demo<\/a>. This gives the result:<\/p>\n
| PROGRAMID | Q1_PEOPLE | Q2_PEOPLE | Q3_PEOPLE | Q4_PEOPLE | Q1_IT | Q2_IT | Q3_IT | Q4_IT | Q1_TRAVEL | Q2_TRAVEL | Q3_TRAVEL | Q4_TRAVEL |\n---------------------------------------------------------------------------------------------------------------------------------------------\n|       366 |    109034 |     25418 |    101130 |     54787 |     0 |     0 |     0 |     0 |      1195 |       613 |      1113 |      1195 |\n<\/code><\/pre>\n<\/p><\/div>\n
<\/div>\n<\/div>\n
            <\/span> <\/p><\/div>\n<\/div>\n
[ad_2]<\/p>\n
solved SQL Pivot based on multiple columns <\/p>\n","protected":false},"excerpt":{"rendered":"
[ad_1] In order to get the result that you want you will have to apply both the UNPIVOT and the PIVOT functions. The unpivot function will convert your Q1, Q2, Q3, and Q4 columns into rows. Once that is done, you will apply the pivot function. The unpivot code will be similar to this: select … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[320],"tags":[839,500,1009,2316],"class_list":["post-8211","post","type-post","status-publish","format-standard","hentry","category-solved","tag-pivot","tag-sql-server","tag-tsql","tag-unpivot"],"yoast_head":"\n[Solved] SQL Pivot based on multiple columns - JassWeb<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/jassweb.com\/solved\/solved-sql-pivot-based-on-multiple-columns\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Solved] SQL Pivot based on multiple columns - JassWeb\" \/>\n<meta property=\"og:description\" content=\"[ad_1] In order to get the result that you want you will have to apply both the UNPIVOT and the PIVOT functions. The unpivot function will convert your Q1, Q2, Q3, and Q4 columns into rows. Once that is done, you will apply the pivot function. The unpivot code will be similar to this: select ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-sql-pivot-based-on-multiple-columns\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2022-09-12T06:28:19+00:00\" \/>\n<meta name=\"author\" content=\"Kirat\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Kirat\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-pivot-based-on-multiple-columns\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-pivot-based-on-multiple-columns\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] SQL Pivot based on multiple columns\",\"datePublished\":\"2022-09-12T06:28:19+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-pivot-based-on-multiple-columns\/\"},\"wordCount\":114,\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"keywords\":[\"pivot\",\"sql-server\",\"tsql\",\"unpivot\"],\"articleSection\":[\"Solved\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-pivot-based-on-multiple-columns\/\",\"url\":\"https:\/\/jassweb.com\/solved\/solved-sql-pivot-based-on-multiple-columns\/\",\"name\":\"[Solved] SQL Pivot based on multiple columns - JassWeb\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#website\"},\"datePublished\":\"2022-09-12T06:28:19+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-pivot-based-on-multiple-columns\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/jassweb.com\/solved\/solved-sql-pivot-based-on-multiple-columns\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-pivot-based-on-multiple-columns\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/jassweb.com\/solved\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] SQL Pivot based on multiple columns\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/jassweb.com\/solved\/#website\",\"url\":\"https:\/\/jassweb.com\/solved\/\",\"name\":\"JassWeb\",\"description\":\"Build High-quality Websites\",\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/jassweb.com\/solved\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\",\"name\":\"Jass Web\",\"url\":\"https:\/\/jassweb.com\/solved\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png\",\"contentUrl\":\"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png\",\"width\":693,\"height\":132,\"caption\":\"Jass Web\"},\"image\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\",\"name\":\"Kirat\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750\",\"contentUrl\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750\",\"caption\":\"Kirat\"},\"sameAs\":[\"http:\/\/jassweb.com\"],\"url\":\"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"[Solved] SQL Pivot based on multiple columns - JassWeb","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/jassweb.com\/solved\/solved-sql-pivot-based-on-multiple-columns\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] SQL Pivot based on multiple columns - JassWeb","og_description":"[ad_1] In order to get the result that you want you will have to apply both the UNPIVOT and the PIVOT functions. The unpivot function will convert your Q1, Q2, Q3, and Q4 columns into rows. Once that is done, you will apply the pivot function. The unpivot code will be similar to this: select ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-sql-pivot-based-on-multiple-columns\/","og_site_name":"JassWeb","article_published_time":"2022-09-12T06:28:19+00:00","author":"Kirat","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Kirat","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/jassweb.com\/solved\/solved-sql-pivot-based-on-multiple-columns\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-sql-pivot-based-on-multiple-columns\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] SQL Pivot based on multiple columns","datePublished":"2022-09-12T06:28:19+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-sql-pivot-based-on-multiple-columns\/"},"wordCount":114,"publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"keywords":["pivot","sql-server","tsql","unpivot"],"articleSection":["Solved"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/jassweb.com\/solved\/solved-sql-pivot-based-on-multiple-columns\/","url":"https:\/\/jassweb.com\/solved\/solved-sql-pivot-based-on-multiple-columns\/","name":"[Solved] SQL Pivot based on multiple columns - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"datePublished":"2022-09-12T06:28:19+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-sql-pivot-based-on-multiple-columns\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-sql-pivot-based-on-multiple-columns\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-sql-pivot-based-on-multiple-columns\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] SQL Pivot based on multiple columns"}]},{"@type":"WebSite","@id":"https:\/\/jassweb.com\/solved\/#website","url":"https:\/\/jassweb.com\/solved\/","name":"JassWeb","description":"Build High-quality Websites","publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/jassweb.com\/solved\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/jassweb.com\/solved\/#organization","name":"Jass Web","url":"https:\/\/jassweb.com\/solved\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/","url":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","contentUrl":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","width":693,"height":132,"caption":"Jass Web"},"image":{"@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31","name":"Kirat","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/image\/","url":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750","contentUrl":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750","caption":"Kirat"},"sameAs":["http:\/\/jassweb.com"],"url":"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/"}]}},"_links":{"self":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/8211","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/comments?post=8211"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/8211\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=8211"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=8211"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=8211"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}