{"id":15463,"date":"2022-10-11T16:35:46","date_gmt":"2022-10-11T11:05:46","guid":{"rendered":"https:\/\/jassweb.com\/solved\/solved-t-sql-unpivot-table\/"},"modified":"2022-10-11T16:35:46","modified_gmt":"2022-10-11T11:05:46","slug":"solved-t-sql-unpivot-table","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-t-sql-unpivot-table\/","title":{"rendered":"[Solved] T-SQL (Un)Pivot Table"},"content":{"rendered":"<p> [ad_1]<br \/>\n<\/p>\n<div id=\"answer-24262689\" class=\"answer js-answer accepted-answer js-accepted-answer\" data-answerid=\"24262689\" data-parentid=\"24261764\" data-score=\"1\" data-position-on-page=\"1\" data-highest-scored=\"1\" data-question-has-accepted-highest-score=\"1\" itemprop=\"acceptedAnswer\" itemscope itemtype=\"https:\/\/schema.org\/Answer\">\n<div class=\"post-layout\">\n<div class=\"votecell post-layout--left\"><\/div>\n<div class=\"answercell post-layout--right\">\n<div class=\"s-prose js-post-body\" itemprop=\"text\">\n<p>I usually use dynamic sql. Something like this<\/p>\n<pre><code>create table #T\n(\n    ID int,\n    aText1 varchar(4),\n    aText2 varchar(4),\n    aInt1 int,\n    aInt2 int\n)\n\ninsert into #T\nselect 1, 'ABC1', 'XYZ1', 2,  20\nunion\nselect 2, 'ABC1', 'XYZ2', 3,  25\nunion\nselect 3, 'ABC2', 'XYZ2', 1,  30\nunion\nselect 4, 'ABC2', 'XYZ1', 4,  35\n\ndeclare @sql nvarchar(max)\n\nset @sql=\"select aText1 \"\n\nselect @sql = @sql + ', SUM(case when aText2 = ''' + aText2 + ''' then aInt1 end) as [' + aText2 + ' - aInt1] '+\n                     ', SUM(case when aText2 = ''' + aText2 + ''' then aInt2 end) as [' + aText2 + ' - aInt2]'  \nfrom \n(\n    select distinct aText2 from #T\n) T\n\nset @sql = @sql + ' from #T group by aText1'\n\nexec sp_executeSQL @sql\n\ndrop table #T\n<\/code><\/pre>\n<p>Or you can create another view (like #T2 in my next example) and use PIVOT<\/p>\n<pre><code>create table #T\n(\n    ID int,\n    aText1 varchar(4),\n    aText2 varchar(4),\n    aInt1 int,\n    aInt2 int\n)\n\ninsert into #T\nselect 1, 'ABC1', 'XYZ1', 2,  20\nunion\nselect 2, 'ABC1', 'XYZ2', 3,  25\nunion\nselect 3, 'ABC2', 'XYZ2', 1,  30\nunion\nselect 4, 'ABC2', 'XYZ1', 4,  35\n\ncreate table #T2\n(\n    aText1 varchar(4),\n    aText2 varchar(20),\n    aValue int\n)\n\ninsert into #T2\nselect aText1, aText2 + ' - aInt1' as aText2, aInt1\nfrom #T\nunion\nselect aText1, aText2 + ' - aInt2', aInt2 \nfrom #T\n\ndeclare @sql nvarchar(max), @columns nvarchar(max)\nset @columns=\"\"\nselect @columns = @columns + ', [' + aText2 + ']'\nfrom (select distinct aText2 from #T2) as T\n\nset @columns = substring(@columns, 2, len(@columns))\n\nset @sql=\"\n        SELECT *\n        FROM\n        (SELECT \n                aText1, \n                aText2, \n                aValue \n            FROM \n                #T2 \n        ) AS SourceTable\n        PIVOT\n        (\n            SUM(aValue)\n            FOR aText2 in (\"+@columns+')\n        ) AS PivotTable'\n\nexec sp_executeSQL @SQL\n\ndrop table #T2\ndrop table #T\n<\/code><\/pre>\n<\/p><\/div>\n<div class=\"mt24\"><\/div>\n<\/div>\n<p>            <span class=\"d-none\" itemprop=\"commentCount\">6<\/span> <\/p><\/div>\n<\/div>\n<p>[ad_2]<\/p>\n<p>solved T-SQL (Un)Pivot Table <\/p>\n","protected":false},"excerpt":{"rendered":"<p>[ad_1] I usually use dynamic sql. Something like this create table #T ( ID int, aText1 varchar(4), aText2 varchar(4), aInt1 int, aInt2 int ) insert into #T select 1, &#8216;ABC1&#8217;, &#8216;XYZ1&#8217;, 2, 20 union select 2, &#8216;ABC1&#8217;, &#8216;XYZ2&#8217;, 3, 25 union select 3, &#8216;ABC2&#8217;, &#8216;XYZ2&#8217;, 1, 30 union select 4, &#8216;ABC2&#8217;, &#8216;XYZ1&#8217;, 4, 35 declare &#8230; <a title=\"[Solved] T-SQL (Un)Pivot Table\" class=\"read-more\" href=\"https:\/\/jassweb.com\/solved\/solved-t-sql-unpivot-table\/\" aria-label=\"More on [Solved] T-SQL (Un)Pivot Table\">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,3459,1009,2316],"class_list":["post-15463","post","type-post","status-publish","format-standard","hentry","category-solved","tag-pivot","tag-sql-server-2008-r2","tag-tsql","tag-unpivot"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>[Solved] T-SQL (Un)Pivot Table - 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-t-sql-unpivot-table\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Solved] T-SQL (Un)Pivot Table - JassWeb\" \/>\n<meta property=\"og:description\" content=\"[ad_1] I usually use dynamic sql. Something like this create table #T ( ID int, aText1 varchar(4), aText2 varchar(4), aInt1 int, aInt2 int ) insert into #T select 1, &#039;ABC1&#039;, &#039;XYZ1&#039;, 2, 20 union select 2, &#039;ABC1&#039;, &#039;XYZ2&#039;, 3, 25 union select 3, &#039;ABC2&#039;, &#039;XYZ2&#039;, 1, 30 union select 4, &#039;ABC2&#039;, &#039;XYZ1&#039;, 4, 35 declare ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-t-sql-unpivot-table\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2022-10-11T11:05:46+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-t-sql-unpivot-table\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-t-sql-unpivot-table\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] T-SQL (Un)Pivot Table\",\"datePublished\":\"2022-10-11T11:05:46+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-t-sql-unpivot-table\/\"},\"wordCount\":35,\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"keywords\":[\"pivot\",\"sql-server-2008-r2\",\"tsql\",\"unpivot\"],\"articleSection\":[\"Solved\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-t-sql-unpivot-table\/\",\"url\":\"https:\/\/jassweb.com\/solved\/solved-t-sql-unpivot-table\/\",\"name\":\"[Solved] T-SQL (Un)Pivot Table - JassWeb\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#website\"},\"datePublished\":\"2022-10-11T11:05:46+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-t-sql-unpivot-table\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/jassweb.com\/solved\/solved-t-sql-unpivot-table\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-t-sql-unpivot-table\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/jassweb.com\/solved\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] T-SQL (Un)Pivot Table\"}]},{\"@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] T-SQL (Un)Pivot Table - 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-t-sql-unpivot-table\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] T-SQL (Un)Pivot Table - JassWeb","og_description":"[ad_1] I usually use dynamic sql. Something like this create table #T ( ID int, aText1 varchar(4), aText2 varchar(4), aInt1 int, aInt2 int ) insert into #T select 1, 'ABC1', 'XYZ1', 2, 20 union select 2, 'ABC1', 'XYZ2', 3, 25 union select 3, 'ABC2', 'XYZ2', 1, 30 union select 4, 'ABC2', 'XYZ1', 4, 35 declare ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-t-sql-unpivot-table\/","og_site_name":"JassWeb","article_published_time":"2022-10-11T11:05:46+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-t-sql-unpivot-table\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-t-sql-unpivot-table\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] T-SQL (Un)Pivot Table","datePublished":"2022-10-11T11:05:46+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-t-sql-unpivot-table\/"},"wordCount":35,"publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"keywords":["pivot","sql-server-2008-r2","tsql","unpivot"],"articleSection":["Solved"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/jassweb.com\/solved\/solved-t-sql-unpivot-table\/","url":"https:\/\/jassweb.com\/solved\/solved-t-sql-unpivot-table\/","name":"[Solved] T-SQL (Un)Pivot Table - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"datePublished":"2022-10-11T11:05:46+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-t-sql-unpivot-table\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-t-sql-unpivot-table\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-t-sql-unpivot-table\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] T-SQL (Un)Pivot Table"}]},{"@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\/15463","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=15463"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/15463\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=15463"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=15463"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=15463"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}