{"id":11969,"date":"2022-09-29T04:18:16","date_gmt":"2022-09-28T22:48:16","guid":{"rendered":"https:\/\/jassweb.com\/solved\/solved-sql-multiple-count-on-same-row-with-dynamic-column\/"},"modified":"2022-09-29T04:18:16","modified_gmt":"2022-09-28T22:48:16","slug":"solved-sql-multiple-count-on-same-row-with-dynamic-column","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-sql-multiple-count-on-same-row-with-dynamic-column\/","title":{"rendered":"[Solved] SQL Multiple count on same row with dynamic column"},"content":{"rendered":"<p> [ad_1]<br \/>\n<\/p>\n<div id=\"answer-17614663\" class=\"answer js-answer accepted-answer js-accepted-answer\" data-answerid=\"17614663\" data-parentid=\"17612471\" data-score=\"4\" 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 are using SQL Server then you can implement the PIVOT function and if you have an unknown number of period values, then you will need to use dynamic SQL:<\/p>\n<pre><code>DECLARE @cols AS NVARCHAR(MAX),\n    @query  AS NVARCHAR(MAX)\n\nselect @cols = STUFF((SELECT distinct ',' + QUOTENAME('PeriodId'+cast(periodid as varchar(10))) \n                    from Periods\n            FOR XML PATH(''), TYPE\n            ).value('.', 'NVARCHAR(MAX)') \n        ,1,1,'')\n\nset @query = 'SELECT resourcecode, ' + @cols + ' , Total\n            from \n            (\n               select s.resourcecode, \n                 ''PeriodId''+cast(p.periodid as varchar(10)) period,\n                count(*) over(partition by s.resourcecode) Total\n               from periods p\n               left join schedules s\n                 on p.periodid = s.periodid\n            ) x\n            pivot \n            (\n                count(period)\n                for period in (' + @cols + ')\n            ) p \n            where resourcecode is not null\n            order by resourcecode'\n\nexecute(@query)\n<\/code><\/pre>\n<p>See <a rel=\"nofollow noopener\" target=\"_blank\" href=\"http:\/\/sqlfiddle.com\/#!3\/fca71\/5\">SQL Fiddle with Demo<\/a>. This gives a result:<\/p>\n<pre><code>| RESOURCECODE | PERIODID1 | PERIODID2 | PERIODID3 | PERIODID4 | PERIODID5 | PERIODID6 | PERIODID7 | PERIODID8 | TOTAL |\n------------------------------------------------------------------------------------------------------------------------\n|           AA |         2 |         0 |         3 |         0 |         0 |         0 |         0 |         0 |     5 |\n|           BB |         2 |         1 |         1 |         0 |         0 |         0 |         0 |         0 |     4 |\n|           CC |         1 |         1 |         1 |         0 |         0 |         0 |         0 |         0 |     3 |\n<\/code><\/pre>\n<p><strike>Based on your previous question that was tagged with MySQL, I am assuming you are using MySQL as the database.  If so, then you do not have a PIVOT function so you will have to use an aggregate function with a CASE expression to transform the rows of data into columns.<\/strike><\/p>\n<p>If your column values are known, then you can hard-code the query:<\/p>\n<pre><code>select resourcecode,\n  sum(case when period = 'PeriodId1' then 1 else 0 end) PeriodId1,\n  sum(case when period = 'PeriodId2' then 1 else 0 end) PeriodId2,\n  sum(case when period = 'PeriodId3' then 1 else 0 end) PeriodId3,\n  sum(case when period = 'PeriodId4' then 1 else 0 end) PeriodId4,\n  sum(case when period = 'PeriodId5' then 1 else 0 end) PeriodId5,\n  sum(case when period = 'PeriodId6' then 1 else 0 end) PeriodId6,\n  sum(case when period = 'PeriodId7' then 1 else 0 end) PeriodId7,\n  sum(case when period = 'PeriodId8' then 1 else 0 end) PeriodId8,\n  count(*) Total\nfrom\n(\n  select concat('PeriodId', p.periodid) Period,\n    s.resourcecode\n  from periods p\n  left join schedules s\n    on p.periodid = s.periodid\n) d\nwhere resourcecode is not null\ngroup by resourcecode;\n<\/code><\/pre>\n<p>See <a rel=\"nofollow noopener\" target=\"_blank\" href=\"http:\/\/sqlfiddle.com\/#!2\/89bff\/9\">SQL Fiddle with Demo<\/a>.  But if the values will be unknown or dynamic then you will need to use a prepared statement to generate a sql string to execute:<\/p>\n<pre><code>SET @sql = NULL;\nSELECT\n  GROUP_CONCAT(DISTINCT\n    CONCAT(\n      'sum(CASE WHEN period = ''',\n      concat('PeriodId', periodid),\n      ''' THEN 1 else 0 END) AS `',\n      concat('PeriodId', periodid), '`'\n    )\n  ) INTO @sql\nFROM periods;\n\nSET @sql \n  = CONCAT('SELECT resourcecode, ', @sql, ' , count(*) Total\n            from\n            (\n              select concat(''PeriodId'', p.periodid) Period,\n                s.resourcecode\n              from periods p\n              left join schedules s\n                on p.periodid = s.periodid\n            ) d\n            where resourcecode is not null\n            group by resourcecode');\n\n\nPREPARE stmt FROM @sql;\nEXECUTE stmt;\nDEALLOCATE PREPARE stmt;\n<\/code><\/pre>\n<p>See <a rel=\"nofollow noopener\" target=\"_blank\" href=\"http:\/\/sqlfiddle.com\/#!2\/89bff\/13\">SQL Fiddle with Demo<\/a>.<\/p>\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 SQL Multiple count on same row with dynamic column <\/p>\n","protected":false},"excerpt":{"rendered":"<p>[ad_1] Since you are using SQL Server then you can implement the PIVOT function and if you have an unknown number of period values, then you will need to use dynamic SQL: DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) select @cols = STUFF((SELECT distinct &#8216;,&#8217; + QUOTENAME(&#8216;PeriodId&#8217;+cast(periodid as varchar(10))) from Periods FOR XML PATH(&#8221;), TYPE &#8230; <a title=\"[Solved] SQL Multiple count on same row with dynamic column\" class=\"read-more\" href=\"https:\/\/jassweb.com\/solved\/solved-sql-multiple-count-on-same-row-with-dynamic-column\/\" aria-label=\"More on [Solved] SQL Multiple count on same row with dynamic column\">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,341,500,501],"class_list":["post-11969","post","type-post","status-publish","format-standard","hentry","category-solved","tag-pivot","tag-sql","tag-sql-server","tag-sql-server-2008"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>[Solved] SQL Multiple count on same row with dynamic column - 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-multiple-count-on-same-row-with-dynamic-column\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Solved] SQL Multiple count on same row with dynamic column - JassWeb\" \/>\n<meta property=\"og:description\" content=\"[ad_1] Since you are using SQL Server then you can implement the PIVOT function and if you have an unknown number of period values, then you will need to use dynamic SQL: DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) select @cols = STUFF((SELECT distinct &#039;,&#039; + QUOTENAME(&#039;PeriodId&#039;+cast(periodid as varchar(10))) from Periods FOR XML PATH(&#039;&#039;), TYPE ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-sql-multiple-count-on-same-row-with-dynamic-column\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2022-09-28T22:48:16+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=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-multiple-count-on-same-row-with-dynamic-column\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-multiple-count-on-same-row-with-dynamic-column\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] SQL Multiple count on same row with dynamic column\",\"datePublished\":\"2022-09-28T22:48:16+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-multiple-count-on-same-row-with-dynamic-column\/\"},\"wordCount\":160,\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"keywords\":[\"pivot\",\"sql\",\"sql-server\",\"sql-server-2008\"],\"articleSection\":[\"Solved\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-multiple-count-on-same-row-with-dynamic-column\/\",\"url\":\"https:\/\/jassweb.com\/solved\/solved-sql-multiple-count-on-same-row-with-dynamic-column\/\",\"name\":\"[Solved] SQL Multiple count on same row with dynamic column - JassWeb\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#website\"},\"datePublished\":\"2022-09-28T22:48:16+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-multiple-count-on-same-row-with-dynamic-column\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/jassweb.com\/solved\/solved-sql-multiple-count-on-same-row-with-dynamic-column\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-multiple-count-on-same-row-with-dynamic-column\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/jassweb.com\/solved\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] SQL Multiple count on same row with dynamic column\"}]},{\"@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] SQL Multiple count on same row with dynamic column - 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-multiple-count-on-same-row-with-dynamic-column\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] SQL Multiple count on same row with dynamic column - JassWeb","og_description":"[ad_1] Since you are using SQL Server then you can implement the PIVOT function and if you have an unknown number of period values, then you will need to use dynamic SQL: DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) select @cols = STUFF((SELECT distinct ',' + QUOTENAME('PeriodId'+cast(periodid as varchar(10))) from Periods FOR XML PATH(''), TYPE ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-sql-multiple-count-on-same-row-with-dynamic-column\/","og_site_name":"JassWeb","article_published_time":"2022-09-28T22:48:16+00:00","author":"Kirat","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Kirat","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/jassweb.com\/solved\/solved-sql-multiple-count-on-same-row-with-dynamic-column\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-sql-multiple-count-on-same-row-with-dynamic-column\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] SQL Multiple count on same row with dynamic column","datePublished":"2022-09-28T22:48:16+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-sql-multiple-count-on-same-row-with-dynamic-column\/"},"wordCount":160,"publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"keywords":["pivot","sql","sql-server","sql-server-2008"],"articleSection":["Solved"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/jassweb.com\/solved\/solved-sql-multiple-count-on-same-row-with-dynamic-column\/","url":"https:\/\/jassweb.com\/solved\/solved-sql-multiple-count-on-same-row-with-dynamic-column\/","name":"[Solved] SQL Multiple count on same row with dynamic column - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"datePublished":"2022-09-28T22:48:16+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-sql-multiple-count-on-same-row-with-dynamic-column\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-sql-multiple-count-on-same-row-with-dynamic-column\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-sql-multiple-count-on-same-row-with-dynamic-column\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] SQL Multiple count on same row with dynamic column"}]},{"@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\/11969","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=11969"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/11969\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=11969"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=11969"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=11969"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}