insert multiple values into one column

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)

insert multiple values into one column

Author

Topic

sharona
Yak Posting Veteran

75 Posts

Posted - 2011-09-22 : 10:09:53

i have an update query that updates my values into columns
as shown below;(the * seperate the columns)

ACTIONCODE ******* _BCP *****ABAC

AddCashAccounts *****ITA *****FR
AddCounterparty *****TDM *****ITA
AddEditBroker ******* ID *****TDM
AddNewDeal ********SET *****ITA
AddServicer ********ITA *****TDM
AddToMasterWhatIf ***TD *****ITA
AdjustmentScreen ***ITA ***** TDM
AdjustProductStatic **SET ***** FR
AllowArchive ******ITA ***** MGR
sometimes the action code can have multiple _bcp and abac
how do i insert multiple values into one column?
currently in AddCashAccounts under _BCP it shows ITA i would like to see FR, ITA

below is my script

CREATE TABLE #FINAL
( FUNDCODE VARCHAR(4),
ACTIONCODE VARCHAR (24),
ROLECODE VARCHAR(4),
_BCP VARCHAR(24),
ABAC VARCHAR(24))

-- GET ALL FUNDS ---

SELECT DISTINCT
R.FUNDCODE,
R.ACTIONCODE,
R.ROLECODE
INTO #FUND
from dbo.RoleAction R
where R.FundCode IN ('_BCP','ABAC')
GROUP BY
R.ACTIONCODE,
R.ROLECODE,
R.FUNDCODE

--Add all data to the final table
INSERT #Final
( ACTIONCODE,
ROLECODE)
SELECT DISTINCT
ACTIONCODE,
ROLECODE
FROM #FUND

-- Update final table
UPDATE #Final
SET _BCP = R.ROLECODE
FROM #fund r, #Final f
WHERE f.ACTIONCODE = r.ACTIONCODE
AND r.FUNDCODE = '_BCP'

UPDATE #Final
SET ABAC = R.ROLECODE
FROM #fund r, #Final f
WHERE f.ACTIONCODE = r.ACTIONCODE
AND r.FUNDCODE = 'ABAC'
----Final select

select distinct
ACTIONCODE,
_BCP,
ABAC
from #FINAL

group by
ACTIONCODE,
_BCP,
ABAC

--DROP TABLE #FUND
--DROP TABLE #Final

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-11-16 : 05:38:33

You can update column with concatenating other column values like

update table
set col1=col2+','+col1

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-16 : 05:52:04

see

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources