{"id":31602,"date":"2023-01-22T20:41:59","date_gmt":"2023-01-22T15:11:59","guid":{"rendered":"https:\/\/jassweb.com\/solved\/solved-split-column-into-different-fields-with-sql-server-closed\/"},"modified":"2023-01-22T20:41:59","modified_gmt":"2023-01-22T15:11:59","slug":"solved-split-column-into-different-fields-with-sql-server-closed","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-split-column-into-different-fields-with-sql-server-closed\/","title":{"rendered":"[Solved] Split column into different fields with SQL Server [closed]"},"content":{"rendered":"

[ad_1]
\n<\/p>\n

\n
\n
<\/div>\n
\n
\n

This answer has a lot of assumptions as you did not provide any logic for how you want your first table to get to your second. Here are the assumptions I made:<\/p>\n

    \n
  1. A claim\/Policy combo share a ID and are prefixed with P for policy and C for claim.<\/li>\n
  2. The policy-date for a claim and policy are identical for a given ID.<\/li>\n
  3. There is a 1 to 1 relationship for policies and claims.<\/li>\n
  4. If there is a policy and a claim, the case_type should be policy.<\/li>\n<\/ol>\n

    With these assumptions, here is the table variable I made to test this:<\/p>\n

    Edit: Updated for new sample data<\/p>\n

    DECLARE @tbl TABLE (\npolicy_id NVARCHAR(5),\npolicy_date date,\ncase_type NVARCHAR(6)\n)\n\nINSERT @tbl VALUES\n('p111','05\/10\/2017','Policy'), ('c111','05\/10\/2017','Claim'),\n('p103','06\/10\/2017','Policy'), ('c103','06\/10\/2017','Claim'),\n('p124','05\/10\/2017','Policy'), ('c133','06\/10\/2017','Claim'),\n('p155','07\/10\/2017','Policy'), ('c155','07\/10\/2017','Claim'),\n('p144','09\/10\/2017','Policy'), ('c177','09\/10\/2017','Claim'),\n('AI100','2017-08-10','Policy'), ('EC100','2017-08-10','Claim')\n<\/code><\/pre>\n
    And here is the query to get the result set you are looking for.<\/p>\n
    SELECT\n    MAX(CASE case_type\n        WHEN 'Policy' THEN policy_id\n        END) AS Policy,\n    MAX(CASE case_type\n        WHEN 'Claim' THEN policy_id\n        END) AS Claim,\n    MAX(policy_date) AS policy_date,\n    MIN(CASE case_type          --This is a custom priority for which case_type is displayed.\n        WHEN 'Policy' THEN 1    --If there is a policy then it's policy,\n        WHEN 'Info Only' Then 2 --if there is no policy but there is an info only then it's info only,\n        WHEN 'Claim' THEN 3     --and if it's only a claim then it's claim.\n        END) AS case_type\nFROM @tbl\nGROUP BY\n    REPLACE(REPLACE(REPLACE(REPLACE(policy_id,'ai',''), 'ec', ''), 'c', ''), 'p', '')   --Does not include info only, I'm not sure what prefix is applied for that\n    --SUBSTRING(policy_id, PATINDEX('%[0-9.-]%', policy_id), 10)\n<\/code><\/pre>\n
    The substring option is unnecessary for the current example set but is more thorough. It will strip any letters prefixing the numbers up to 10 digits (the limit for the int data type). See this awesome blog<\/a> for more details, however as we are only stripping the letters prefixing it I stopped at step 2.<\/p>\n<\/p><\/div>\n
    <\/div>\n<\/div>\n
                10<\/span> <\/p><\/div>\n<\/div>\n
    [ad_2]<\/p>\n
    solved Split column into different fields with SQL Server [closed] <\/p>\n","protected":false},"excerpt":{"rendered":"
    [ad_1] This answer has a lot of assumptions as you did not provide any logic for how you want your first table to get to your second. Here are the assumptions I made: A claim\/Policy combo share a ID and are prefixed with P for policy and C for claim. The policy-date for a claim … 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":[500,501],"class_list":["post-31602","post","type-post","status-publish","format-standard","hentry","category-solved","tag-sql-server","tag-sql-server-2008"],"yoast_head":"\n[Solved] Split column into different fields with SQL Server [closed] - 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-split-column-into-different-fields-with-sql-server-closed\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Solved] Split column into different fields with SQL Server [closed] - JassWeb\" \/>\n<meta property=\"og:description\" content=\"[ad_1] This answer has a lot of assumptions as you did not provide any logic for how you want your first table to get to your second. Here are the assumptions I made: A claim\/Policy combo share a ID and are prefixed with P for policy and C for claim. The policy-date for a claim ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-split-column-into-different-fields-with-sql-server-closed\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2023-01-22T15:11:59+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-split-column-into-different-fields-with-sql-server-closed\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-split-column-into-different-fields-with-sql-server-closed\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] Split column into different fields with SQL Server [closed]\",\"datePublished\":\"2023-01-22T15:11:59+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-split-column-into-different-fields-with-sql-server-closed\/\"},\"wordCount\":194,\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"keywords\":[\"sql-server\",\"sql-server-2008\"],\"articleSection\":[\"Solved\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-split-column-into-different-fields-with-sql-server-closed\/\",\"url\":\"https:\/\/jassweb.com\/solved\/solved-split-column-into-different-fields-with-sql-server-closed\/\",\"name\":\"[Solved] Split column into different fields with SQL Server [closed] - JassWeb\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#website\"},\"datePublished\":\"2023-01-22T15:11:59+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-split-column-into-different-fields-with-sql-server-closed\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/jassweb.com\/solved\/solved-split-column-into-different-fields-with-sql-server-closed\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-split-column-into-different-fields-with-sql-server-closed\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/jassweb.com\/solved\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] Split column into different fields with SQL Server [closed]\"}]},{\"@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] Split column into different fields with SQL Server [closed] - 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-split-column-into-different-fields-with-sql-server-closed\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] Split column into different fields with SQL Server [closed] - JassWeb","og_description":"[ad_1] This answer has a lot of assumptions as you did not provide any logic for how you want your first table to get to your second. Here are the assumptions I made: A claim\/Policy combo share a ID and are prefixed with P for policy and C for claim. The policy-date for a claim ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-split-column-into-different-fields-with-sql-server-closed\/","og_site_name":"JassWeb","article_published_time":"2023-01-22T15:11:59+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-split-column-into-different-fields-with-sql-server-closed\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-split-column-into-different-fields-with-sql-server-closed\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] Split column into different fields with SQL Server [closed]","datePublished":"2023-01-22T15:11:59+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-split-column-into-different-fields-with-sql-server-closed\/"},"wordCount":194,"publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"keywords":["sql-server","sql-server-2008"],"articleSection":["Solved"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/jassweb.com\/solved\/solved-split-column-into-different-fields-with-sql-server-closed\/","url":"https:\/\/jassweb.com\/solved\/solved-split-column-into-different-fields-with-sql-server-closed\/","name":"[Solved] Split column into different fields with SQL Server [closed] - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"datePublished":"2023-01-22T15:11:59+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-split-column-into-different-fields-with-sql-server-closed\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-split-column-into-different-fields-with-sql-server-closed\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-split-column-into-different-fields-with-sql-server-closed\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] Split column into different fields with SQL Server [closed]"}]},{"@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\/31602","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=31602"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/31602\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=31602"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=31602"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=31602"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}