Set a field with several results as string

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

Set a field with several results as string

Author

Topic

Lesombrero
Starting Member

43 Posts

Posted - 2013-01-07 : 01:35:22

Hello and happy new year to everyone,
I need to Update TableA and Set TxtMat as string (nvarchar100) by using key_compos.
See TableA:
id_prod / key_compos / TxtMatiereCompo_en
1019 / 2009111916447465 / NULL
1025 / 20091119164545691 / NULL
1030 / 20091119164517627 / NULL
1039 / 20091119164621171 / NULL

I have view_compos. and for my 1st key, it shows:
pourcent / id_compos / matiere_en
67 / 2009111916447465 / Aluminum
22 / 2009111916447465 / Wood
11 / 2009111916447465 / Bamboo

I would like my string to Set TxtMat to be: '67% Aluminum, 22% Wood, 11% Bamboo'
Could you please help me?
Thank you in advance.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-07 : 01:55:22

[code]
DECLARE @TableA TABLE (id_prod int, key_compos varchar(100), TxtMatiereCompo_en nvarchar(500))
INSERT INTO @TableA
SELECT 1019, '2009111916447465', NULL union all
SELECT 1025, '20091119164545691', NULL union all
SELECT 1030, '20091119164517627', NULL union all
SELECT 1039, '20091119164621171', NULL

DECLARE @tabB TABLE (pourcent int, id_compos varchar(100), matiere_en varchar(50))
INSERT INTO @tabB
SELECT 67, '2009111916447465', 'Aluminum' union all
SELECT 22, '2009111916447465', 'Wood' union all
SELECT 11, '2009111916447465', 'Bamboo'

;WITH CTE AS
(
SELECT a.key_compos, b.matiere_en, b.pourcent
FROM @TableA a JOIN @tabB b ON a.key_compos = b.id_compos
),
cte1 AS
(
SELECT c.key_compos, STUFF((SELECT ',' + CAST(s.pourcent as varchar(5))+ '% '+ s.matiere_en FROM CTE s WHERE s.key_compos = c.key_compos FOR XML PATH('')),1,1,'') AS CSV
FROM CTE c
GROUP BY c.key_compos
)
UPDATE t1
SET TxtMatiereCompo_en = csv
FROM @TableA t1
JOIN cte1 ON t1.key_compos = cte1.key_compos

SELECT * FROM @TableA

EDIT: Simplified solution is as follows

;WITH CTE AS
(
SELECT a.key_compos, b.matiere_en, b.pourcent, a.TxtMatiereCompo_en
FROM @TableA a JOIN @tabB b ON a.key_compos = b.id_compos
)
UPDATE extern
SET extern.TxtMatiereCompo_en = LEFT(TxtMat , LEN(TxtMat )-1)
FROM CTE AS extern
CROSS APPLY
(
SELECT CAST(intern.pourcent as varchar(5))+ '% '+ intern.matiere_en + ','
FROM CTE AS intern
WHERE extern.key_compos = intern.key_compos
FOR XML PATH('')
) pre_trimmed (TxtMat);

SELECT * FROM @TableA
[/code]

--
Chandu

Lesombrero
Starting Member

43 Posts

Posted - 2013-01-07 : 05:04:06

Thank you so much Bandi. Very helpful.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-07 : 06:40:26

quote:
Originally posted by Lesombrero

Thank you so much Bandi. Very helpful.

Welcome

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-07 : 09:59:57

why do you need a CTE for this? I cant see any recursion here

you just need this


DECLARE @TableA TABLE (id_prod int, key_compos  varchar(100), TxtMatiereCompo_en nvarchar(500))
INSERT INTO @TableA 
SELECT 1019, '2009111916447465', NULL union all
SELECT 1025, '20091119164545691', NULL union all
SELECT 1030, '20091119164517627', NULL union all
SELECT 1039, '20091119164621171', NULL

DECLARE @tabB TABLE (pourcent int, id_compos varchar(100), matiere_en varchar(50))
INSERT INTO @tabB
SELECT 67, '2009111916447465', 'Aluminum'  union all
SELECT 22, '2009111916447465', 'Wood' union all
SELECT 11, '2009111916447465', 'Bamboo'


UPDATE ta
SET ta.TxtMatiereCompo_en = STUFF((SELECT ',' + CAST(pourcent AS varchar(10)) + '% ' + CAST(matiere_en AS varchar(30)) FROM @tabB WHERE id_compos = ta.key_compos FOR XML PATH('')),1,1,'')
FROM @TableA ta


SELECT * FROM @TableA

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources