Update the column with names

Question

Update the column with names

karunakar2351

Right there with Babe

Points: 729
More actions
August 13, 2013 at 12:14 am

#276873

I have a table with column and data is
blp_proposalnoblp_documentattach blp_fundblp_branch
0002755746201,202,203,204,211 116 KA01
0002997284201,202,203,204,214,211116 KA01
0003234742201,202,203,204,211 116 KA01
0004046205201,202,203,204,211 116 JH73
0003659836201,202,203,204,211 116 JH73
0004029283201,202,203,204,211 116 JH73
0002975418201,202,203,204,214 116 AL139
0003176467201,202,203,204,211 116 AL17
0003365092201,202,203,204 116BT01
0003450570201,202,203,204 116 AS64
0004056265201,202,203,204,214 116 JM42
0007359820NULL 116 JA07
0007415964NULL 116NO13
0007244229NULL 116AL28
0007490863NULL 116MO06
And the other table with data is
BLD_FundBLD_DocumentIDBLD_Document
116201Benefit Illustration
116202Age proof
116203Address Proof
116204Proof of Identity
116205Income proofs
116206Risk Appetite awareness form
116207ECS Mandate
116208CC Mandate
116200Application Form
116209Employee Documents
116210Occupation Related Documents
116211Cash Authority Documents
116215Single Name Documentation
116212NRI Documents
116213PEP Documents
116214Female Life Guidelines
so i need the first table column update like the given below..
bld_Document attach
Benefit Illustration,Age proof,Address Proof,Proof of Identity,Cash Authority Documents
Benefit Illustration,Age proof,Address Proof,Proof of Identity,Female Life Guidelines,Cash Authority Documents
and so on....
Help me...

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

I would first split the data - you can use the function described in this article: Tally OH! An Improved SQL 8K “CSV Splitter” Function[/url] - then join it with the documents and then concatenate the rows again.

Concatenating is explained in this article:

Concatenating Row Values in Transact-SQL[/url]

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

ksatpute123 Hall of Fame Points: 3325 More actions · Answer 2

Here is the solution for your problem:

You can use any csv splitter function available and replace the one I have used in the code.

create table #t

(

blp_proposalno bigint,

blp_documentattach varchar(max),

blp_fund int,

blp_branch varchar(5)

)

create table #m

(

bld_fund int,

bld_documentid int,

bld_document varchar(100)

)

insert into #t

select 2755746,'201,202,203,204,211',116,'KA01' union all

select 2997284,'201,202,203,204,214,000',116,'KA01' union all

select 3234742,'201,202,203,204,211',116,'KA01' union all

select 4046205,'201,202,203,204,211',116,'JH73' union all

select 3659836,'201,202,203,204,211',116,'JH73' union all

select 4029283,'201,202,203,204,211',116,'JH73' union all

select 2975418,'201,202,203,204,214',116,'AL139' union all

select 3176467,'201,202,203,204,211',116,'AL17' union all

select 3365092,'201,202,203,204,',116,'BT01' union all

select 3450570,'201,202,203,204,',116,'AS64' union all

select 4056265,'201,202,203,204,214',116,'JM42' union all

select 7359820,NULL,116,'JA07' union all

select 7415964,NULL,116,'NO13' union all

select 7244229,NULL,116,'AL28' union all

select 7490863,NULL,116,'MO06'

insert into #m

select 116,201,'Benefit Illustration' union all

select 116,202,'Age proof' union all

select 116,203,'Address Proof' union all

select 116,204,'Proof of Identity' union all

select 116,205,'Income proofs' union all

select 116,206,'Risk Appetite awareness form' union all

select 116,207,'ECS Mandate' union all

select 116,208,'CC Mandate' union all

select 116,200,'Application Form' union all

select 116,209,'Employee Documents' union all

select 116,210,'Occupation Related Documents' union all

select 116,211,'Cash Authority Documents' union all

select 116,215,'Single Name Documentation' union all

select 116,212,'NRI Documents' union all

select 116,213,'PEP Documents'

-- Main solution begins here.

select t.*,p.bld_document

into #k

from

(

select a.*, Convert(int,b.value) [DocID] from #t a

cross apply

(select * from dbo.split_delimited_string(a.blp_documentattach,',')) b

) t

join #m p

on t.docID = p.bld_documentid

Select blp_proposalno, STUFF((Select ',' + bld_document from #k where (blp_proposalno = a.blp_proposalno) FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)'),1,1,'') [bld_Document attach],

blp_fund,blp_branch

from #k a

group by blp_proposalno, blp_fund, blp_branch

The result of the script is as follows:

blp_proposalno,bld_Document attach,blp_fund,blp_branch

2755746,Benefit Illustration,Age proof,Address Proof,Proof of Identity,Cash Authority Documents,116,KA01

2975418,Benefit Illustration,Age proof,Address Proof,Proof of Identity,116,AL139

2997284,Benefit Illustration,Age proof,Address Proof,Proof of Identity,116,KA01

3176467,Benefit Illustration,Age proof,Address Proof,Proof of Identity,Cash Authority Documents,116,AL17

3234742,Benefit Illustration,Age proof,Address Proof,Proof of Identity,Cash Authority Documents,116,KA01

3365092,Benefit Illustration,Age proof,Address Proof,Proof of Identity,116,BT01

3450570,Benefit Illustration,Age proof,Address Proof,Proof of Identity,116,AS64

3659836,Benefit Illustration,Age proof,Address Proof,Proof of Identity,Cash Authority Documents,116,JH73

4029283,Benefit Illustration,Age proof,Address Proof,Proof of Identity,Cash Authority Documents,116,JH73

4046205,Benefit Illustration,Age proof,Address Proof,Proof of Identity,Cash Authority Documents,116,JH73

4056265,Benefit Illustration,Age proof,Address Proof,Proof of Identity,116,JM42

karunakar2351 Right there with Babe Points: 729 More actions · Answer 3

Thanks but

b.value column is not availabe in any table so it is throwing an error

ksatpute123 Hall of Fame Points: 3325 More actions · Answer 4

b.value column is coming from the string split function .

select t.*,p.bld_document

into #k

from

(

select a.*, Convert(int,b.value) [DocID] from #t a

cross apply

(select Value from dbo.split_delimited_string(a.blp_documentattach,',')) b

) t

join #m p

on t.docID = p.bld_documentid

replace '*' with 'Value' or by the name of the column given by your string split function and it should work.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

SELECT

t.blp_proposalno,

t.blp_documentattach,

t.blp_fund,

t.blp_branch,

x.[bld_Document attach]

FROM #t t

OUTER APPLY (

SELECT [bld_Document attach] =

STUFF((

SELECT ',' + bld_document

FROM #m m

WHERE m.bld_fund = t.blp_fund

AND t.blp_documentattach LIKE '%'+CAST(m.bld_documentid AS VARCHAR(3))+'%'

FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(8000)'),1,1,'')

) x

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ksatpute123 Hall of Fame Points: 3325 More actions · Answer 6

Nice query. Certainly the best approach for the mentioned scenario. Removing the Split function and any hidden R-BAR associated with it. Thanks for posting.