Divide one column into many - SQL

I have a column called @months which stores months in this format

@month = '01-03-05-11-12'

I would like to have a SELECT query that divides this column into 12, making it Jan, Feb, March

Each of my record has this column in front. So if a record has @month = '01-03' in it, it shows under January and March. Can something like that be done? Or anything close is good e开发者_如何学运维nough.

I played with case statement but could not produce the results.

Code if anyone wants to try

create table recs(
id int not null primary key,
cust_name varchar(20),
callmonth varchar(36)
)

insert into recs values(1,'john','01-12')
insert into recs values(2,'Jessica','02-06')
insert into recs values(3,'Charlie','01-06')
insert into recs values(4,'steale','03-04')
insert into recs values(5,'Silica','01-02-03-04-05-06-07-08-09-10-11-12')
insert into recs values(6,'Luder','01-03-05-07-09-11-12')
insert into recs values(7,'Panther','01-06-12')
insert into recs values(8,'Dinky','03-04-15')

I may be unclear about what you are trying to do, but you can devide into 12 tables using the following:

INSERT INTO January_table
SELECT *
FROM Original_table
WHERE month LIKE '%01%';

Do this for each month and it should give you 12 tables containing only the values that have that month. You could then use a view to combine them.

Alternatively, if you are looking for one query, you might be able to use a case statement like the one below:

INSERT INTO table
SELECT *
    CASE
    WHEN month LIKE '%01%' THEN 'True'
    ELSE 'False'
    END,
    CASE
    WHEN month LIKE '%02%' THEN 'True'
    ...
FROM Original_table;

This will yield a table with all fields from the original table, followed by 12 "monthly columns" each with a true or false representing whether that month is present in that row.

i agree with other posters - you should change your table design -as you have it it is very poor form.

your case statement should generally be of this form:

case when instr(month,'01') > 0 then 'Jan'

you should split the values by '-' - and then youll have a table

and then you should see if that value is inside this table + its string name.

p.s.

You have to hav a table like this :

01 - jan
02 - feb
...
...

let say : table months ( TBLMNTH) will have ( id , name )

e.g. :

1 |   jan  
2 |   feb
select name from  TBLMNTH where @month CHARINDEX(name , @month)>-1

Create SPLIT function and use it with DATENAME as below , you can get list of months in string format..I think this will solve part of your problem.

select  DATENAME(month, DATEADD(month, convert(int, val) , -1 )) AS month_str 
from  SPLIT('01-03-05-11-12', '-')

Result will be (tested in mssql server;it's working)

January
March
May
November
December

UDF

CREATE FUNCTION SPLIT
(
 @s nvarchar(max),
 @splitChar nchar(1)
)
returns @t table (id int identity(1,1), val nvarchar(max))
as
begin

declare @i int, @j int
select @i = 0, @j = (len(@s) - len(replace(@s,@splitChar,'')))

;with cte 
as
(
 select
  i = @i + 1,
  s = @s, 
  n = substring(@s, 0, charindex(@splitChar, @s)),
  m = substring(@s, charindex(@splitChar, @s)+1, len(@s) - charindex(@splitChar, @s))

 union all

 select 
  i = cte.i + 1,
  s = cte.m, 
  n = substring(cte.m, 0, charindex(@splitChar, cte.m)),
  m = substring(
   cte.m,
   charindex(@splitChar, cte.m) + 1,
   len(cte.m)-charindex(@splitChar, cte.m)
 )
 from cte
 where i <= @j
)
insert into @t (val)
select pieces
from 
(
 select 
 ltrim(rtrim(case when i <= @j then n else m end)) pieces
 from cte
) t
where
 len(pieces) > 0
option (maxrecursion 0)

return

end

GO