{"id":15606,"date":"2022-10-12T07:09:12","date_gmt":"2022-10-12T01:39:12","guid":{"rendered":"https:\/\/jassweb.com\/solved\/solved-extend-current-query-calculated-columns\/"},"modified":"2022-10-12T07:09:12","modified_gmt":"2022-10-12T01:39:12","slug":"solved-extend-current-query-calculated-columns","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-extend-current-query-calculated-columns\/","title":{"rendered":"[Solved] extend current query, calculated columns"},"content":{"rendered":"<p> [ad_1]<br \/>\n<\/p>\n<div id=\"answer-25787550\" class=\"answer js-answer accepted-answer js-accepted-answer\" data-answerid=\"25787550\" data-parentid=\"25784437\" data-score=\"7\" 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>Since you have two columns that you now want to PIVOT, you&#8217;ll first have to unpivot those columns and then convert those values into the new columns.<\/p>\n<p>Starting in SQL Server 2005, you could use <code>CROSS APPLY<\/code> to unpivot the columns. The basic syntax will be similar to:<\/p>\n<pre><code>select \n  name,\n  new_col,\n  total  \nfrom\n(\n  select name, \n    dt = year(date),\n    result,\n    total = count(*) over(partition by name)\n  from list\n) d\ncross apply\n(\n  select 'dt', dt union all\n  select 'result', result\n) c (old_col_name, new_col)\n<\/code><\/pre>\n<p>See <a rel=\"nofollow noopener\" target=\"_blank\" href=\"http:\/\/sqlfiddle.com\/#!3\/0226b\/28\">SQL Fiddle with Demo<\/a>.  This query gets you a list of names, with the &#8220;new columns&#8221; and then the Total entries for each name. <\/p>\n<pre><code>|     NAME | NEW_COL | TOTAL |\n|----------|---------|-------|\n| Person A |    2012 |    11 |\n| Person A |       1 |    11 |\n| Person A |    2012 |    11 |\n| Person A |       2 |    11 |\n<\/code><\/pre>\n<p>You&#8217;ll see that the dates and the results are now both stored in &#8220;new_col&#8221;. These values will now be used as the new column names.  If you have a limited number of columns, then you would simply hard-code the query:<\/p>\n<pre><code>select name, lost = [1], \n  draw=[2], won = [3], \n  [2014], [2013], [2012], Total\nfrom\n(\n  select \n    name,\n    new_col,\n    total  \n  from\n  (\n    select name, \n      dt = year(date),\n      result,\n      total = count(*) over(partition by name)\n    from list\n  ) d\n  cross apply\n  (\n    select 'dt', dt union all\n    select 'result', result\n  ) c (old_col_name, new_col)\n) src\npivot\n(\n  count(new_col)\n  for new_col in([1], [2], [3], [2014], [2013], [2012])\n) piv\norder by [2014];\n<\/code><\/pre>\n<p>See <a rel=\"nofollow noopener\" target=\"_blank\" href=\"http:\/\/sqlfiddle.com\/#!3\/0226b\/31\">SQL Fiddle with Demo<\/a><\/p>\n<p>Now since your years are dynamic, then you&#8217;ll need to use dynamic sql.  But it appears that you have 3 results and potentially multiple years &#8211; so I&#8217;d use a combination of static\/dynamic sql to make this easier:<\/p>\n<pre><code>DECLARE @cols AS NVARCHAR(MAX),\n    @query  AS NVARCHAR(MAX),\n    @orderby nvarchar(max)\n\nselect @cols \n  = STUFF((SELECT  ',' + QUOTENAME(year(date)) \n           from list\n           group by year(date)\n           order by year(date) desc\n            FOR XML PATH(''), TYPE\n            ).value('.', 'NVARCHAR(MAX)') \n        ,1,1,'')\n\nselect @orderby = 'ORDER BY ['+cast(year(getdate()) as varchar(4)) + '] desc'\n\nset @query = 'SELECT name, lost = [1], \n                draw=[2], won = [3],' + @cols + ', Total\n            from \n            (\n              select \n                name,\n                new_col,\n                total  \n              from\n              (\n                select name, \n                  dt = year(date),\n                  result,\n                  total = count(*) over(partition by name)\n                from list\n              ) d\n              cross apply\n              (\n                select ''dt'', dt union all\n                select ''result'', result\n              ) c (old_col_name, new_col)\n            ) x\n            pivot \n            (\n                count(new_col)\n                for new_col in ([1], [2], [3],' + @cols + ')\n            ) p '+ @orderby\n\nexec sp_executesql @query;\n<\/code><\/pre>\n<p>See <a rel=\"nofollow noopener\" target=\"_blank\" href=\"http:\/\/sqlfiddle.com\/#!3\/0226b\/29\">SQL Fiddle with Demo<\/a>. This gives a result:<\/p>\n<pre><code>|     NAME | LOST | DRAW | WON | 2014 | 2013 | 2012 | TOTAL |\n|----------|------|------|-----|------|------|------|-------|\n| Person B |    7 |    1 |   2 |    6 |    2 |    2 |    10 |\n| Person A |    5 |    3 |   3 |    4 |    3 |    4 |    11 |\n| Person C |    2 |    1 |   1 |    3 |    1 |    0 |     4 |\n<\/code><\/pre>\n<p>If you want to only filter the result columns for the current year, then you can perform this filtering a variety of ways but the easiest you be to include a filter in the unpivot. The hard-coded version would be:<\/p>\n<pre><code>select name, lost = [1], \n  draw=[2], won = [3], \n  [2014], [2013], [2012], Total\nfrom\n(\n  select \n    name,\n    new_col,\n    total  \n  from\n  (\n    select name, \n      dt = year(date),\n      result,\n      total = count(*) over(partition by name)\n    from list\n  ) d\n  cross apply\n  (\n    select 'dt', dt union all\n    select 'result', case when dt = 2014 then result end  \n  ) c (old_col_name, new_col)\n) src\npivot\n(\n  count(new_col)\n  for new_col in([1], [2], [3], [2014], [2013], [2012])\n) piv\norder by [2014] desc;\n<\/code><\/pre>\n<p>See <a rel=\"nofollow noopener\" target=\"_blank\" href=\"http:\/\/sqlfiddle.com\/#!3\/0226b\/42\">SQL Fiddle with Demo<\/a>. Then the dynamic sql version would be:<\/p>\n<pre><code>DECLARE @cols AS NVARCHAR(MAX),\n    @query  AS NVARCHAR(MAX),\n    @orderby nvarchar(max),\n    @currentYear varchar(4)\n\nselect @currentYear = cast(year(getdate()) as varchar(4))\n\nselect @cols \n  = STUFF((SELECT  ',' + QUOTENAME(year(date)) \n           from list\n           group by year(date)\n           order by year(date) desc\n            FOR XML PATH(''), TYPE\n            ).value('.', 'NVARCHAR(MAX)') \n        ,1,1,'')\n\nselect @orderby = 'ORDER BY ['+ @currentYear + '] desc'\n\nset @query = 'SELECT name, lost = [1], \n                draw=[2], won = [3],' + @cols + ', Total\n            from \n            (\n              select \n                name,\n                new_col,\n                total  \n              from\n              (\n                select name, \n                  dt = year(date),\n                  result,\n                  total = count(*) over(partition by name)\n                from list\n              ) d\n              cross apply\n              (\n                select ''dt'', dt union all\n                select ''result'', case when dt=\"+@currentYear+\" then result end\n              ) c (old_col_name, new_col)\n            ) x\n            pivot \n            (\n                count(new_col)\n                for new_col in ([1], [2], [3],' + @cols + ')\n            ) p '+ @orderby\n\nexec sp_executesql @query;\n<\/code><\/pre>\n<p>See <a rel=\"nofollow noopener\" target=\"_blank\" href=\"http:\/\/sqlfiddle.com\/#!3\/0226b\/45\">SQL Fiddle with Demo<\/a>. This version will give a result:<\/p>\n<pre><code>|     NAME | LOST | DRAW | WON | 2014 | 2013 | 2012 | TOTAL |\n|----------|------|------|-----|------|------|------|-------|\n| Person B |    4 |    0 |   2 |    6 |    2 |    2 |    10 |\n| Person A |    2 |    1 |   1 |    4 |    3 |    4 |    11 |\n| Person C |    1 |    1 |   1 |    3 |    1 |    0 |     4 |\n<\/code><\/pre>\n<\/p><\/div>\n<div class=\"mt24\"><\/div>\n<\/div>\n<p>            <span class=\"d-none\" itemprop=\"commentCount\">1<\/span> <\/p><\/div>\n<\/div>\n<p>[ad_2]<\/p>\n<p>solved extend current query, calculated columns <\/p>\n","protected":false},"excerpt":{"rendered":"<p>[ad_1] Since you have two columns that you now want to PIVOT, you&#8217;ll first have to unpivot those columns and then convert those values into the new columns. Starting in SQL Server 2005, you could use CROSS APPLY to unpivot the columns. The basic syntax will be similar to: select name, new_col, total from ( &#8230; <a title=\"[Solved] extend current query, calculated columns\" class=\"read-more\" href=\"https:\/\/jassweb.com\/solved\/solved-extend-current-query-calculated-columns\/\" aria-label=\"More on [Solved] extend current query, calculated columns\">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,3459],"class_list":["post-15606","post","type-post","status-publish","format-standard","hentry","category-solved","tag-sql","tag-sql-server-2008-r2"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>[Solved] extend current query, calculated 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-extend-current-query-calculated-columns\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Solved] extend current query, calculated columns - JassWeb\" \/>\n<meta property=\"og:description\" content=\"[ad_1] Since you have two columns that you now want to PIVOT, you&#8217;ll first have to unpivot those columns and then convert those values into the new columns. Starting in SQL Server 2005, you could use CROSS APPLY to unpivot the columns. The basic syntax will be similar to: select name, new_col, total from ( ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-extend-current-query-calculated-columns\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2022-10-12T01:39:12+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=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-extend-current-query-calculated-columns\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-extend-current-query-calculated-columns\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] extend current query, calculated columns\",\"datePublished\":\"2022-10-12T01:39:12+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-extend-current-query-calculated-columns\/\"},\"wordCount\":244,\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"keywords\":[\"sql\",\"sql-server-2008-r2\"],\"articleSection\":[\"Solved\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-extend-current-query-calculated-columns\/\",\"url\":\"https:\/\/jassweb.com\/solved\/solved-extend-current-query-calculated-columns\/\",\"name\":\"[Solved] extend current query, calculated columns - JassWeb\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#website\"},\"datePublished\":\"2022-10-12T01:39:12+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-extend-current-query-calculated-columns\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/jassweb.com\/solved\/solved-extend-current-query-calculated-columns\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-extend-current-query-calculated-columns\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/jassweb.com\/solved\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] extend current query, calculated 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=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] extend current query, calculated 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-extend-current-query-calculated-columns\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] extend current query, calculated columns - JassWeb","og_description":"[ad_1] Since you have two columns that you now want to PIVOT, you&#8217;ll first have to unpivot those columns and then convert those values into the new columns. Starting in SQL Server 2005, you could use CROSS APPLY to unpivot the columns. The basic syntax will be similar to: select name, new_col, total from ( ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-extend-current-query-calculated-columns\/","og_site_name":"JassWeb","article_published_time":"2022-10-12T01:39:12+00:00","author":"Kirat","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Kirat","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/jassweb.com\/solved\/solved-extend-current-query-calculated-columns\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-extend-current-query-calculated-columns\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] extend current query, calculated columns","datePublished":"2022-10-12T01:39:12+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-extend-current-query-calculated-columns\/"},"wordCount":244,"publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"keywords":["sql","sql-server-2008-r2"],"articleSection":["Solved"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/jassweb.com\/solved\/solved-extend-current-query-calculated-columns\/","url":"https:\/\/jassweb.com\/solved\/solved-extend-current-query-calculated-columns\/","name":"[Solved] extend current query, calculated columns - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"datePublished":"2022-10-12T01:39:12+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-extend-current-query-calculated-columns\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-extend-current-query-calculated-columns\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-extend-current-query-calculated-columns\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] extend current query, calculated 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=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\/15606","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=15606"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/15606\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=15606"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=15606"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=15606"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}