Add text message to corresponding fields

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Add text message to corresponding fields

Author

Topic

Miki
Starting Member

5 Posts

Posted - 2010-05-27 : 14:26:26

select l.loanid,cr.payhabits,
year1=substring(payhabits,1,1),
year2=substring(payhabits,2,1),
year3=substring(payhabits,3,1),
year4=substring(payhabits,4,1),
year5=substring(payhabits,5,1),
year6=substring(payhabits,6,1),
year7=substring(payhabits,7,1),
year8=substring(payhabits,8,1),
year9=substring(payhabits,9,1),
year10=substring(payhabits,10,1),
year11=substring(payhabits,11,1),
year12=substring(payhabits,12,1)
from loan l
inner join credit cr on l.loanid=cr.loanid
inner join participation pr on l.loanid=pr.loanid

I got only like below without corresponding text in bracket:

Loanid, payhabits, Jan feb mar apr ... dec
98765 121123152287 1 2 1 1 7

I need to achieve:

I am trying to achieve output something like below:note that 12 months is corresponding to payhabits each codes for months.

Loanid, payhabits, Jan feb mar apr ... dec
98765 121123152287 1(5 days late) 2(10 days late) 1(5 days late)

The corresponding payhabits code in reference to text message is:
1=5 days late
2= 10 days late
3 = 15 days late and so forth. Please help.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-05-27 : 15:21:36

This can probably be done better with PIVOT and UNPIVOT, but this is a start. Note, I used variables to test this since you didn't supply any DDLs and sample data for all your tables. I hope it helps!

Jim


declare @loanid int
declare @payhabits varchar(12)

set @loanid = 123456
set @payhabits = '121123152287'

;with cte_habits
as
(
  select [loanid] =@loanid, [payhabits] = @payhabits,1 as [year] ,[habit] = substring(@payhabits,1,1) 
 union all
  select  [loanid] =@loanid, [payhabits] = @payhabits, [year] + 1 ,substring(@payhabits,year+1,1)  
  from cte_habits
 where year < 12
)

select h.loanid,h.payhabits,convert(varchar(2),h.[year] ) + code.[desc]
from cte_habits h
inner join
(
   select 1 as [year] , ' 5 days late' as [desc] union
   select 2, ' 10 days late' union
   select 3,  ' 15 days late'
) code

on
  h.[year] =  code.[year]

Everyday I learn something that somebody else already knew

Miki
Starting Member

5 Posts

Posted - 2010-05-27 : 18:06:45

Thank you Jim. It helped me. But I need output as listed below:

Loanid payhabits Jan Feb Mar
123456 121123152287 1 5 days late 2 10 days late 1 5 days late

I need one loan per record corresponding singly payhabits digit to 12 months. Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources