update with comma sepaartor - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

update with comma sepaartor

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2014-03-24 : 06:31:53

Hi,
I would like to update the final table with values into a comma separator as follows with examples:
I think I have the IDs set correctly for this example:
You can see from the final table that the code column can be a combination of more than one rows from the map tables...
Thank you

create table #tblTax(TaxStatusID int, FullName varchar(20)
insert into #tblTax values(1, 'Taxable')
insert into #tblTax values(2, 'exempt')

create table #tblTypes(TypeID int, description varchar(100)
insert into #tblTypes values(1, 'cor')
insert into #tblTypes values(2, 'tyr')
insert into #tblTypes values(3, 'mandate')
insert into #tblTypes values(6, 'rehab')
insert into #tblTypes values(12, 'mon')
insert into #tblTypes values(33, 'glx')

create table #tblForm(FormID int, ShortCode varchar(100), LongCode varchar(1000))
insert into #tblForm values(1, 'I', 'somelongcode')
insert into #tblForm values(2, 'C', 'hallo')
insert into #tblForm values(13, 'B', 'Barlow')
insert into #tblForm values(14, 'A', 'dand')
insert into #tblForm values(6, 'G', 'mond')
insert into #tblForm values(11, 'G', 'mond')
insert into #tblForm values(6, 'G', 'mond')

create table #tblMapping(ID int, FormID int, TypeID int, TaxID int)
insert into #tblMapping values(16, 12, 13, 1)
insert into #tblMapping values(14, 13, 12, 1)
insert into #tblMapping values(28, 14, 12, 1)
insert into #tblMapping values(18, 14, 33, 1)
insert into #tblMapping values(25, 13, 33, 1)
insert into #tblMapping values(21, 16, 23, 2)
insert into #tblMapping values(8, 6, 6, 2)
insert into #tblMapping values(1, 1, 1, 2)

create table #tblMain(ShortCode varchar(100), LongCode varchar(1000), ClientName varchar(200), TaxID int, TypeID int)
insert into #tblMain values (NULL, NULL, 'johnson', 1, 33)
insert into #tblMain values (NULL, NULL, 'anderson', 2, 6)
insert into #tblMain values (NULL, NULL, 'jacksons', 2, 1)
insert into #tblMain values (NULL, NULL, 'smith', 1, 12)

Looking at the above example, I would like to have the #tblMain as follows

#tblMain

ShoetName LongName ClientName TaxID TypeID
======================================================================
A, B 'dand, Barlow' 'johnson' 1 33
G 'mond' 'anderson' 2 6
I 'somelongcode' 'jacksons' 2 1
A, B 'dand, Barlow' 'smith' 1 12

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-03-27 : 01:56:13

You have entered a duplicate record in ' #tblForm ' table Which made your question more difficult so i deleted that row and got answer... (insert into #tblForm values(6, 'G', 'mond'))

SELECT DISTINCT STUFF((SELECT ','+' '+TF.ShortCode FROM
#tblMapping AS TMP INNER JOIN
#tblForm AS TF
ON TF.FormID = TMP.FormID
WHERE TM.TypeID = TMP.TypeID
AND TM.TaxID = TM.TaxID
ORDER BY TF.LongCode DESC FOR XML PATH('')),1,1,' ') AS ShortName
,''''+STUFF((SELECT ', '+TF.LongCode FROM #tblForm AS TF INNER JOIN
#tblMapping AS TMP
ON TF.FormID = TMP.FormID
WHERE TM.TypeID = TMP.TypeID
AND TM.TaxID = TM.TaxID
ORDER BY TF.LongCode DESC FOR XML PATH('')),1,1,' ')+'''' AS LongName,
''''+TM.ClientName+'''' AS ClientName,TM.TaxID,TM.TypeID
FROM #tblMain AS TM
INNER JOIN #tblMapping AS TMP
ON TM.TaxID =TMP.TaxID
INNER JOIN #tblForm AS TF
ON TF.FormID = TMP.FormID

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2014-03-27 : 02:41:24

SELECT DISTINCT
STUFF((SELECT DISTINCT ', '+TF.ShortCode FROM #tblMapping AS TMP INNER JOIN #tblForm AS TF
ON TF.FormID = TMP.FormID WHERE TM.TypeID = TMP.TypeID
FOR XML PATH('')),1,1,'') AS ShortName
, STUFF((SELECT ', '+TF.LongCode FROM #tblForm AS TF INNER JOIN #tblMapping AS TMP
ON TF.FormID = TMP.FormID WHERE TM.TypeID = TMP.TypeID
ORDER BY TF.LongCode DESC FOR XML PATH('')),1,1,'') AS LongName
, TM.ClientName
, TM.TaxID
, TM.TypeID
FROM #tblMain AS TM
INNER JOIN #tblMapping AS TMP
ON TM.TaxID =TMP.TaxID
INNER JOIN #tblForm AS TF
ON TF.FormID = TMP.FormID

Veera

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources