{"id":19167,"date":"2022-11-05T17:14:37","date_gmt":"2022-11-05T11:44:37","guid":{"rendered":"https:\/\/jassweb.com\/solved\/solved-making-a-dynamic-pivot-table-but-without-using-the-fiddle\/"},"modified":"2022-11-05T17:14:37","modified_gmt":"2022-11-05T11:44:37","slug":"solved-making-a-dynamic-pivot-table-but-without-using-the-fiddle","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-making-a-dynamic-pivot-table-but-without-using-the-fiddle\/","title":{"rendered":"[Solved] Making a dynamic pivot table but without using the fiddle"},"content":{"rendered":"<p> [ad_1]<br \/>\n<\/p>\n<div id=\"answer-56367353\" class=\"answer js-answer accepted-answer js-accepted-answer\" data-answerid=\"56367353\" data-parentid=\"56367234\" 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>It would be a lot easier for you to create a cross tabs query. If you can identify the pattern, the dynamic code can be easier to code and there are multiple examples on the internet (and this site). If you don&#8217;t know how to create dynamic code, I&#8217;d suggest that you stay away from it until you fully understand the do&#8217;s and don&#8217;ts.<\/p>\n<pre><code>WITH a\nAS (\n    SELECT a.account,\n        dense_rank() OVER ( PARTITION BY a.account ORDER BY ratechangedate) AS index_num,\n        ratechangedate,\n        new_noterate\n    FROM MARS_DW.[dbo].[vw_GTMScheduledRateAndPaymentChangesWithAccountNumber_Archive] a\n    INNER JOIN (\n        SELECT *\n        FROM mars..vw_loans\n        WHERE loanstatus &lt;&gt; 'bk payment plan'\n        ) b ON a.account = b.account\n    WHERE archivedate=\"5\/20\/2019\"\n    )\nSELECT a.Account,\n    MIN( CASE WHEN index_num = 1 THEN ratechangedate END) AS [date 1],\n    MIN( CASE WHEN index_num = 1 THEN new_noterate END)   AS [rate 1],\n    MIN( CASE WHEN index_num = 2 THEN ratechangedate END) AS [date 2],\n    MIN( CASE WHEN index_num = 2 THEN new_noterate END)   AS [rate 2],\n    MIN( CASE WHEN index_num = 3 THEN ratechangedate END) AS [date 3],\n    MIN( CASE WHEN index_num = 3 THEN new_noterate END)   AS [rate 3],\n    MIN( CASE WHEN index_num = 4 THEN ratechangedate END) AS [date 4],\n    MIN( CASE WHEN index_num = 4 THEN new_noterate END)   AS [rate 4],\n    MIN( CASE WHEN index_num = 5 THEN ratechangedate END) AS [date 5],\n    MIN( CASE WHEN index_num = 5 THEN new_noterate END)   AS [rate 5],\n    MIN( CASE WHEN index_num = 6 THEN ratechangedate END) AS [date 6],\n    MIN( CASE WHEN index_num = 6 THEN new_noterate END)   AS [rate 6],\n    MIN( CASE WHEN index_num = 7 THEN ratechangedate END) AS [date 7],\n    MIN( CASE WHEN index_num = 7 THEN new_noterate END)   AS [rate 7],\n    MIN( CASE WHEN index_num = 8 THEN ratechangedate END) AS [date 8],\n    MIN( CASE WHEN index_num = 8 THEN new_noterate END)   AS [rate 8]\nFROM a\nGROUP BY a.Account;\n<\/code><\/pre>\n<p><strong>UPDATE:<\/strong><\/p>\n<p>I told you to read the article on how to work with dynamic SQL. I also told you to identify the pattern, you didn&#8217;t. Now, there&#8217;s an answer that will be much slower than what I&#8217;m suggesting and I just don&#8217;t want subpar code to propagate, so here&#8217;s an option.<\/p>\n<pre><code>DECLARE @cols AS NVARCHAR(MAX),\n        @query  AS NVARCHAR(MAX),\n        @archivedate AS DATETIME = '20190520'; --Always use ISO 8601 format YYYYMMDD\n\nWITH \nE(n) AS(\n    SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)\n),\nE2(n) AS(\n    SELECT a.n FROM E a, E b\n),\nE4(n) AS(\n    SELECT a.n FROM E2 a, E2 b\n),\ncteTally(n) AS(\n    SELECT TOP((SELECT TOP (1) COUNT(DISTINCT ratechangedate) datecount\n                FROM MARS_DW.[dbo].[vw_GTMScheduledRateAndPaymentChangesWithAccountNumber_Archive]\n                WHERE ArchiveDate = @archivedate AND AppliedDate &gt; '1\/2\/2018'\n                GROUP BY account\n                ORDER BY datecount DESC)) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n\n    FROM E4\n)        \nSELECT @cols = (SELECT REPLACE( '\n    ,MIN( CASE WHEN index_num = &lt;&lt;index_num&gt;&gt; THEN ratechangedate END) AS [date &lt;&lt;index_num&gt;&gt;]\n    ,MIN( CASE WHEN index_num = &lt;&lt;index_num&gt;&gt; THEN new_noterate END)   AS [rate &lt;&lt;index_num&gt;&gt;]' , '&lt;&lt;index_num&gt;&gt;', n)\n            FROM cteTally\n            ORDER BY n\n            FOR XML PATH(''), TYPE\n            ).value('.', 'NVARCHAR(MAX)') \n\nset @query = \nN'WITH a AS (\n    SELECT a.account,\n        dense_rank() OVER ( PARTITION BY a.account ORDER BY ratechangedate) AS index_num,\n        ratechangedate,\n        new_noterate\n    FROM MARS_DW.[dbo].[vw_GTMScheduledRateAndPaymentChangesWithAccountNumber_Archive] a\n    INNER JOIN (\n        SELECT *\n        FROM mars..vw_loans\n        WHERE loanstatus &lt;&gt; ''bk payment plan''\n        ) b ON a.account = b.account\n    WHERE archivedate = @date\n    )\nSELECT a.Account' + @cols + N'\nFROM a\nGROUP BY a.Account;'\n\n\nEXECUTE sp_executesql @query, N'@date datetime', @date = @archivedate;\n<\/code><\/pre>\n<\/p><\/div>\n<div class=\"mt24\"><\/div>\n<\/div>\n<p>            <span class=\"d-none\" itemprop=\"commentCount\">13<\/span> <\/p><\/div>\n<\/div>\n<p>[ad_2]<\/p>\n<p>solved Making a dynamic pivot table but without using the fiddle <\/p>\n","protected":false},"excerpt":{"rendered":"<p>[ad_1] It would be a lot easier for you to create a cross tabs query. If you can identify the pattern, the dynamic code can be easier to code and there are multiple examples on the internet (and this site). If you don&#8217;t know how to create dynamic code, I&#8217;d suggest that you stay away &#8230; <a title=\"[Solved] Making a dynamic pivot table but without using the fiddle\" class=\"read-more\" href=\"https:\/\/jassweb.com\/solved\/solved-making-a-dynamic-pivot-table-but-without-using-the-fiddle\/\" aria-label=\"More on [Solved] Making a dynamic pivot table but without using the fiddle\">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":[341,500],"class_list":["post-19167","post","type-post","status-publish","format-standard","hentry","category-solved","tag-sql","tag-sql-server"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>[Solved] Making a dynamic pivot table but without using the fiddle - 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-making-a-dynamic-pivot-table-but-without-using-the-fiddle\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Solved] Making a dynamic pivot table but without using the fiddle - JassWeb\" \/>\n<meta property=\"og:description\" content=\"[ad_1] It would be a lot easier for you to create a cross tabs query. If you can identify the pattern, the dynamic code can be easier to code and there are multiple examples on the internet (and this site). If you don&#8217;t know how to create dynamic code, I&#8217;d suggest that you stay away ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-making-a-dynamic-pivot-table-but-without-using-the-fiddle\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2022-11-05T11:44:37+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=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-making-a-dynamic-pivot-table-but-without-using-the-fiddle\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-making-a-dynamic-pivot-table-but-without-using-the-fiddle\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] Making a dynamic pivot table but without using the fiddle\",\"datePublished\":\"2022-11-05T11:44:37+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-making-a-dynamic-pivot-table-but-without-using-the-fiddle\/\"},\"wordCount\":148,\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"keywords\":[\"sql\",\"sql-server\"],\"articleSection\":[\"Solved\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-making-a-dynamic-pivot-table-but-without-using-the-fiddle\/\",\"url\":\"https:\/\/jassweb.com\/solved\/solved-making-a-dynamic-pivot-table-but-without-using-the-fiddle\/\",\"name\":\"[Solved] Making a dynamic pivot table but without using the fiddle - JassWeb\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#website\"},\"datePublished\":\"2022-11-05T11:44:37+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-making-a-dynamic-pivot-table-but-without-using-the-fiddle\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/jassweb.com\/solved\/solved-making-a-dynamic-pivot-table-but-without-using-the-fiddle\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-making-a-dynamic-pivot-table-but-without-using-the-fiddle\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/jassweb.com\/solved\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] Making a dynamic pivot table but without using the fiddle\"}]},{\"@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=1776403586\",\"contentUrl\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1776403586\",\"caption\":\"Kirat\"},\"sameAs\":[\"http:\/\/jassweb.com\"],\"url\":\"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"[Solved] Making a dynamic pivot table but without using the fiddle - 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-making-a-dynamic-pivot-table-but-without-using-the-fiddle\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] Making a dynamic pivot table but without using the fiddle - JassWeb","og_description":"[ad_1] It would be a lot easier for you to create a cross tabs query. If you can identify the pattern, the dynamic code can be easier to code and there are multiple examples on the internet (and this site). If you don&#8217;t know how to create dynamic code, I&#8217;d suggest that you stay away ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-making-a-dynamic-pivot-table-but-without-using-the-fiddle\/","og_site_name":"JassWeb","article_published_time":"2022-11-05T11:44:37+00:00","author":"Kirat","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Kirat","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/jassweb.com\/solved\/solved-making-a-dynamic-pivot-table-but-without-using-the-fiddle\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-making-a-dynamic-pivot-table-but-without-using-the-fiddle\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] Making a dynamic pivot table but without using the fiddle","datePublished":"2022-11-05T11:44:37+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-making-a-dynamic-pivot-table-but-without-using-the-fiddle\/"},"wordCount":148,"publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"keywords":["sql","sql-server"],"articleSection":["Solved"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/jassweb.com\/solved\/solved-making-a-dynamic-pivot-table-but-without-using-the-fiddle\/","url":"https:\/\/jassweb.com\/solved\/solved-making-a-dynamic-pivot-table-but-without-using-the-fiddle\/","name":"[Solved] Making a dynamic pivot table but without using the fiddle - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"datePublished":"2022-11-05T11:44:37+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-making-a-dynamic-pivot-table-but-without-using-the-fiddle\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-making-a-dynamic-pivot-table-but-without-using-the-fiddle\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-making-a-dynamic-pivot-table-but-without-using-the-fiddle\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] Making a dynamic pivot table but without using the fiddle"}]},{"@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=1776403586","contentUrl":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1776403586","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\/19167","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=19167"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/19167\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=19167"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=19167"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=19167"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}