H.TSmith H.TSmith - 2 months ago 17
SQL Question

splitting one column into several

First time poster here, so please forgive mishaps.

I have a query in sql that for the sake of simplicity returns duration, business duration and impacted service. The problem lies in the fact that if its impacted service, sometimes it has more than one value in the row. For example it can be 'crm, payrol and scheduling'. See below.

duration business duration impacted service
60 40 crm
100 95 payroll
70 70 scheduling
50 45 crm,scheduling, scheduling

What I want is an individual row for each of the values inside that one. Like so:

duration business duration impacted service
60 40 crm
50 45 crm
100 95 payroll
50 45 payroll
70 70 scheduling
50 45 scheduling


How would you go about it?

Thanks!

Answer

One method uses a split function. Google "SQL Server split function" on the web. Then you will have a split function.

You can then do this with outer apply:

select t.duration, t.business_duration, s.impacted_service
from t outer apply
     (dbo.split(t.impacted_service)) s(impacted_service);
Comments