Splitting SQL Column into Multiple Columns Based on Value

Splitting SQL column into multiple columns based on value

To apply each developer and lead across your owners for an Opp_ID, you'll want something like:

SELECT o.opp_id
    , o.Role_User_Name AS Owner
    , l.Role_User_Name AS Lead
    , d.Role_User_Name AS Developer
FROM t1 AS o
LEFT OUTER JOIN t1 l ON o.opp_id = l.opp_id AND l.Role_Name = 'Lead'
LEFT OUTER JOIN t1 d ON o.opp_id = d.opp_id AND d.Role_Name = 'Developer'
WHERE o.Role_Name = 'Owner'

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=da4daea062534245bed474f93ffafbb7

Split multiple values from a string in one column, into multiple columns using SQL Server

With a bit of JSON and assuming you have a known or maximum number of tags

Select A.CompanyName
      ,A.CompanyNumber
      ,Tag1  = JSON_VALUE(S,'$[0]')
      ,Tag2  = JSON_VALUE(S,'$[1]')
      ,Tag3  = JSON_VALUE(S,'$[2]')
From  YourTable A
Cross Apply ( values ( '["'+replace(STRING_ESCAPE(Tags,'json'),';','","')+'"]' ) ) B(S)

Split column into multiple columns by criteria

No need for an addition subquery or CTE. You can pivot your dataset using conditional aggregation with slight modifications of your query: just remove shift from the group by clause, and then implement conditional logic in the sum()s:

select
  date,
  sum(case when shift = 1 then value end) shift1,
  sum(case when shift = 2 then value end) shift2,
  sum(case when shift = 3 then value end) shift3
from
  db.table
where
  date >= date '2020-01-01'
  and filter = 'type'
group by date
order by date

Note:

• there is no need to prefix the column names since a single table comes into play. I removed those

• date is the name of datatype in Oracle, hence not a good choice for a column name

Split single column into multiple columns based on Rank Number

Sort of odd that you have ties. But you can use conditional aggregation with strings:

select student,
       string_agg(case when program_rn = 1 then program_id end, ', '),
       string_agg(case when program_rn = 2 then program_id end, ', '),
       string_agg(case when program_rn = 3 then program_id end, ', ')
from t
group by student;

If you know the maximum that need to be concatenated, you can use conditional aggregation:

select student,
       concat(max(case when program_rn = 1 and seqnum = 1 then program_id + '; ' end),
              max(case when program_rn = 1 and seqnum = 2 then program_id + '; ' end),
              max(case when program_rn = 1 and seqnum = 3 then program_id + '; ' end)
             ),
       concat(max(case when program_rn = 2 and seqnum = 1 then program_id + '; ' end),
              max(case when program_rn = 2 and seqnum = 2 then program_id + '; ' end),
              max(case when program_rn = 2 and seqnum = 3 then program_id + '; ' end)
             ),
       concat(max(case when program_rn = 3 and seqnum = 1 then program_id + '; ' end),
              max(case when program_rn = 3 and seqnum = 2 then program_id + '; ' end),
              max(case when program_rn = 3 and seqnum = 3 then program_id + '; ' end)
             ),
from (select t.*,
             row_number() over (partition by student, program_rn order by program_id) as seqnum
      from t
     ) t
group by student;

This is cumbersome, but possibly simpler than FOR XML PATH.

Note that I changed the delimiter to a semicolon, because that seems more natural for leaving it at the end of the list. Although it can be removed, that just further complicates the logic, perhaps unnecessarily.

Splitting SQL Columns into Multiple Columns Based on Specific Column Value

I can't find simpler than this :

/* Replace @Programs with the name of your table */

SELECT majors.program_name, options.program_name, 
    specs.program_name, subspecs.program_name, majors.code
FROM @Programs majors
LEFT JOIN @Programs options 
    ON majors.code = options.code AND options.program_level = 'Option'
LEFT JOIN @Programs specs 
    ON options.code = specs.code AND specs.program_level = 'Specialty'
LEFT JOIN @Programs subspecs 
    ON specs.code = subspecs.code AND subspecs.program_level = 'Subspecialty'
WHERE majors.program_level = 'Major'

EDIT : corrected typo "Speciality", it should work now.

How to split column into two columns based on unique ID?

Just use case expressions:

select id,
       (case when type = 'card' then value end) as card,
       (case when type = 'cash' then value end) as cash
from t;

Note: The ordering for the result set is indeterminate. You have not explained if the ordering is important to the question, but you seem to have the highest value for each id, then the second highest, and so on. If that is really desired:

order by row_number() over (partition by id order by value desc),
         id desc

How to split a column into two columns based on the value in the another column

You have a very arcane data structure. SQL tables are inherently unordered. From what I can tell, the SQL value is in the "next" row based on the id.

If so, you can use lead():

select . . .,
       stringvalue as fieldname, next_string_value as stringvalue
from (select t.*, lead(t.stringvalue) over (order by id) as next_string_value
      from t
     ) t
where t.objname = 'objname';

If you are really using SQL Server 2008, you can use a self-join. This does assume that the ids have no gaps in them.

---

Related Topics