{"id":7141,"date":"2022-09-07T02:43:35","date_gmt":"2022-09-06T21:13:35","guid":{"rendered":"https:\/\/jassweb.com\/solved\/solved-sql-how-do-i-transpose-and-group-data-into-static-columns-duplicate\/"},"modified":"2022-09-07T02:43:35","modified_gmt":"2022-09-06T21:13:35","slug":"solved-sql-how-do-i-transpose-and-group-data-into-static-columns-duplicate","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-sql-how-do-i-transpose-and-group-data-into-static-columns-duplicate\/","title":{"rendered":"[Solved] SQL How do I transpose and group data into static columns? [duplicate]"},"content":{"rendered":"<p> [ad_1]<br \/>\n<\/p>\n<div id=\"answer-50218653\" class=\"answer js-answer accepted-answer js-accepted-answer\" data-answerid=\"50218653\" data-parentid=\"50218241\" data-score=\"2\" 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>This is a pretty common conditional aggregation. Notice how I posted consumable data as a table and insert statements. To be honest it took longer to do that portion than the actual code to select the data. You should do this in the future. Also you should avoid using keywords as column names. <\/p>\n<pre><code>declare @Something table\n(\n    UID int\n    , LAST varchar(10)\n    , FIRST varchar(10)\n    , FUND char(1)\n    , AMOUNT int\n    , STATUS int\n)\n\ninsert @Something values\n(1, 'Smith', 'John', 'C', 100, 1)\n, (1, 'Smith', 'John', 'B', 250, 1)\n, (1, 'Smith', 'John', 'E', 150, 1)\n, (2, 'Jones', 'Meg', 'B', 275, 1)\n, (2, 'Jones', 'Meg', 'F', 150, 1)\n, (3, 'Carter', 'Bill', 'A', 100, 1)\n;\n\nwith SortedValues as\n(\n    select *\n        , RowNum = ROW_NUMBER() over(partition by UID order by (select null))\n    from @Something\n)\n\nselect UID\n    , Last\n    , First\n    , Fund_1 = max(case when RowNum = 1 then Fund end)\n    , Amt_1 = max(case when RowNum = 1 then Amount end)\n    , Status_1 = max(case when RowNum = 1 then Status end)\n\n    , Fund_2 = max(case when RowNum = 2 then Fund end)\n    , Amt_2 = max(case when RowNum = 2 then Amount end)\n    , Status_2 = max(case when RowNum = 2 then Status end)\n\n    , Fund_3 = max(case when RowNum = 3 then Fund end)\n    , Amt_3 = max(case when RowNum = 3 then Amount end)\n    , Status_3 = max(case when RowNum = 3 then Status end)\nfrom SortedValues\ngroup by UID\n    , Last\n    , First\norder by UID\n    , Last\n    , First\n<\/code><\/pre>\n<\/p><\/div>\n<div class=\"mt24\"><\/div>\n<\/div>\n<p>            <span class=\"d-none\" itemprop=\"commentCount\"><\/span> <\/p><\/div>\n<\/div>\n<p>[ad_2]<\/p>\n<p>solved SQL How do I transpose and group data into static columns? [duplicate] <\/p>\n","protected":false},"excerpt":{"rendered":"<p>[ad_1] This is a pretty common conditional aggregation. Notice how I posted consumable data as a table and insert statements. To be honest it took longer to do that portion than the actual code to select the data. You should do this in the future. Also you should avoid using keywords as column names. declare &#8230; <a title=\"[Solved] SQL How do I transpose and group data into static columns? [duplicate]\" class=\"read-more\" href=\"https:\/\/jassweb.com\/solved\/solved-sql-how-do-i-transpose-and-group-data-into-static-columns-duplicate\/\" aria-label=\"More on [Solved] SQL How do I transpose and group data into static columns? [duplicate]\">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-7141","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] SQL How do I transpose and group data into static columns? [duplicate] - 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-how-do-i-transpose-and-group-data-into-static-columns-duplicate\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Solved] SQL How do I transpose and group data into static columns? [duplicate] - JassWeb\" \/>\n<meta property=\"og:description\" content=\"[ad_1] This is a pretty common conditional aggregation. Notice how I posted consumable data as a table and insert statements. To be honest it took longer to do that portion than the actual code to select the data. You should do this in the future. Also you should avoid using keywords as column names. declare ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-sql-how-do-i-transpose-and-group-data-into-static-columns-duplicate\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2022-09-06T21:13:35+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-how-do-i-transpose-and-group-data-into-static-columns-duplicate\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-how-do-i-transpose-and-group-data-into-static-columns-duplicate\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] SQL How do I transpose and group data into static columns? [duplicate]\",\"datePublished\":\"2022-09-06T21:13:35+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-how-do-i-transpose-and-group-data-into-static-columns-duplicate\/\"},\"wordCount\":81,\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"keywords\":[\"sql\",\"sql-server\"],\"articleSection\":[\"Solved\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-how-do-i-transpose-and-group-data-into-static-columns-duplicate\/\",\"url\":\"https:\/\/jassweb.com\/solved\/solved-sql-how-do-i-transpose-and-group-data-into-static-columns-duplicate\/\",\"name\":\"[Solved] SQL How do I transpose and group data into static columns? [duplicate] - JassWeb\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#website\"},\"datePublished\":\"2022-09-06T21:13:35+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-how-do-i-transpose-and-group-data-into-static-columns-duplicate\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/jassweb.com\/solved\/solved-sql-how-do-i-transpose-and-group-data-into-static-columns-duplicate\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-how-do-i-transpose-and-group-data-into-static-columns-duplicate\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/jassweb.com\/solved\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] SQL How do I transpose and group data into static columns? [duplicate]\"}]},{\"@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 How do I transpose and group data into static columns? [duplicate] - 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-how-do-i-transpose-and-group-data-into-static-columns-duplicate\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] SQL How do I transpose and group data into static columns? [duplicate] - JassWeb","og_description":"[ad_1] This is a pretty common conditional aggregation. Notice how I posted consumable data as a table and insert statements. To be honest it took longer to do that portion than the actual code to select the data. You should do this in the future. Also you should avoid using keywords as column names. declare ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-sql-how-do-i-transpose-and-group-data-into-static-columns-duplicate\/","og_site_name":"JassWeb","article_published_time":"2022-09-06T21:13:35+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-how-do-i-transpose-and-group-data-into-static-columns-duplicate\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-sql-how-do-i-transpose-and-group-data-into-static-columns-duplicate\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] SQL How do I transpose and group data into static columns? [duplicate]","datePublished":"2022-09-06T21:13:35+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-sql-how-do-i-transpose-and-group-data-into-static-columns-duplicate\/"},"wordCount":81,"publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"keywords":["sql","sql-server"],"articleSection":["Solved"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/jassweb.com\/solved\/solved-sql-how-do-i-transpose-and-group-data-into-static-columns-duplicate\/","url":"https:\/\/jassweb.com\/solved\/solved-sql-how-do-i-transpose-and-group-data-into-static-columns-duplicate\/","name":"[Solved] SQL How do I transpose and group data into static columns? [duplicate] - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"datePublished":"2022-09-06T21:13:35+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-sql-how-do-i-transpose-and-group-data-into-static-columns-duplicate\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-sql-how-do-i-transpose-and-group-data-into-static-columns-duplicate\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-sql-how-do-i-transpose-and-group-data-into-static-columns-duplicate\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] SQL How do I transpose and group data into static columns? [duplicate]"}]},{"@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\/7141","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=7141"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/7141\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=7141"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=7141"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=7141"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}