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.
| Author |
Topic |
|
deepak23
Starting Member
8 Posts |
 Posted - 2010-12-05 : 15:35:41
|
| I am trying to concatenate multiple rows into one... This is the current table I have: Current Table: InvestorType SegmentNumOnExclDate ExclReasonFHLMC SD1002 4g Trial Mod FHLMC SD1002 4g Lockout Code 3,9 FHLMC SD1002 4g In HSS FHLMC SD1002 4g CC Restrictions FNMA 4f BK FNMA 4f In UtilitiesOff DB FHLMC SD1002 4f Short Sale Under Contract FHLMC SD1002 4f BK After Concatenation InvestorType SegmentNumOnExclDate ExclReasonFHLMC SD1002 4g Trial Mod; Lockout Code3,9; In HSS; CC RestrictionsFNMA 4f BK; In UtilitiesOff DBFHLMC SD1002 4f ShortSale Under Contract, BK |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-12-05 : 15:42:43
|
| This seem s to be cropping up a lotwith cte as(select InvestorType, SegmentNumOnExclDate, ExclReason, seq = row_number() over (pertition by InvestorType, SegmentNumOnExclDate order by ExclReason))select InvestorType, SegmentNumOnExclDate,ExclReason = max(case when seq = 1 then ExclReason else '' end+ max(case when seq = 2 then ';'+ExclReason else '' end+ max(case when seq = 3 then ';'+ExclReason else '' end+ max(case when seq = 4 then ';'+ExclReason else '' end...from ctegroup by InvestorType, SegmentNumOnExclDate==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
deepak23
Starting Member
8 Posts |
Posted - 2010-12-05 : 20:42:33
|
| Thanks that worked with minor edits.. Only problem is if there are 41 different exlusion reason then I will have to write the case 41 timesmax(case when rn = 41 then ';' + ExclReason else '' end) . It would be goood to do a 'Do - While Statement' to check max rn and then write that many case statements.. |
|
|
|
|
|
|
Concatenating multiple rows into one