SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Set a field with several results as string
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Lesombrero
Starting Member

Germany
43 Posts

Posted - 01/07/2013 :  01:35:22  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 01/07/2013 :  01:55:22  Show Profile  Reply with Quote

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



--
Chandu

Edited by - bandi on 01/07/2013 02:07:34
Go to Top of Page

Lesombrero
Starting Member

Germany
43 Posts

Posted - 01/07/2013 :  05:04:06  Show Profile  Reply with Quote
Thank you so much Bandi. Very helpful.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 01/07/2013 :  06:40:26  Show Profile  Reply with Quote
quote:
Originally posted by Lesombrero

Thank you so much Bandi. Very helpful.


Welcome

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/07/2013 :  09:59:57  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000