[Solved] Split column into different fields with SQL Server [closed]

by


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:

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

With these assumptions, here is the table variable I made to test this:

Edit: Updated for new sample data

DECLARE @tbl TABLE (
policy_id NVARCHAR(5),
policy_date date,
case_type NVARCHAR(6)
)

INSERT @tbl VALUES
('p111','05/10/2017','Policy'), ('c111','05/10/2017','Claim'),
('p103','06/10/2017','Policy'), ('c103','06/10/2017','Claim'),
('p124','05/10/2017','Policy'), ('c133','06/10/2017','Claim'),
('p155','07/10/2017','Policy'), ('c155','07/10/2017','Claim'),
('p144','09/10/2017','Policy'), ('c177','09/10/2017','Claim'),
('AI100','2017-08-10','Policy'), ('EC100','2017-08-10','Claim')

And here is the query to get the result set you are looking for.

SELECT
    MAX(CASE case_type
        WHEN 'Policy' THEN policy_id
        END) AS Policy,
    MAX(CASE case_type
        WHEN 'Claim' THEN policy_id
        END) AS Claim,
    MAX(policy_date) AS policy_date,
    MIN(CASE case_type          --This is a custom priority for which case_type is displayed.
        WHEN 'Policy' THEN 1    --If there is a policy then it's policy,
        WHEN 'Info Only' Then 2 --if there is no policy but there is an info only then it's info only,
        WHEN 'Claim' THEN 3     --and if it's only a claim then it's claim.
        END) AS case_type
FROM @tbl
GROUP BY
    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
    --SUBSTRING(policy_id, PATINDEX('%[0-9.-]%', policy_id), 10)

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 for more details, however as we are only stripping the letters prefixing it I stopped at step 2.

10

solved Split column into different fields with SQL Server [closed]