October 26, 2012 at 1:51 am
Hello everybody,
i work with sql server 2005 and i'm a beginner with t sql.
I must to transform a table into a list separate by a carriage return and place this string into a field..
But i don't know how i can do this !!
For the moment i have this as example :
declare @tMaster table (idMaster int, textFR varchar(20), textEN varchar(20))
declare @tChild table(idChild int, idMaster int, textFR varchar(20), textEN varchar(20))
INSERT INTO @tMaster values(1, null, null), (2, null, null)
INSERT INTO @tChild values(1,1 ,'element 1', 'element 1'), (2, 1, 'element 2', 'element 2')
INSERT INTO @tChild values(3 , 2, 'element 3', 'element 4')
SELECT * FROM @tMaster SELECT * FROM @tChild
the result for the first table is :
idMaster |textFR |textEN
-----------------------
1 null null
2 null null
the result for the second table is :
idChild idMaster |textFR |textEN
--------------------------------
1 1 ligne 1 ligne 1 en
2 1 ligne 2 ligne 2 en
3 2 ligne x ligne x en
the result must be :
idMaster |textFR| textEN
---------------------------------------------------
1 ligne 1 (carriage return) ligne 2 ligne 1 en (carriage return) ligne 2
2 ligne x ligne x en
Ok, it's enought simple i must to transfer all rows locate in the table tChild to the table tMaster, just the field (textFR, textEN)
i will create a query UPDATE SELECT but EACH rows locate in table tchild must be end with a carriage return
How i can add at the end of rows (line) a carrige return ?
i must to add a carriage return because these string will be displayed in a text box
Thanks for your time
Christophe
October 26, 2012 at 3:46 am
You can use CHAR(10) + CHAR(13) to add a carriage return.
Example:
SELECT 'This is a ' + CHAR(10) + CHAR(13) + 'test!'
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 26, 2012 at 4:22 am
Here is another example from here:
DECLARE @strPrint VARCHAR(100);
SET @strPrint = 'Example of carriage return';
SET @strPrint = @strPrint + CHAR(13);
SET @strPrint = @strPrint + 'SQLAuthority.com';
PRINT @strPrint;
GO
PRINT '---------------------------------'
DECLARE @strPrint VARCHAR(100);
SET @strPrint = 'Example of new line feed';
SET @strPrint = @strPrint + CHAR(10);
SET @strPrint = @strPrint + 'SQLAuthority.com';
PRINT @strPrint;
GO
October 26, 2012 at 7:15 am
declare @tMaster table (idMaster int, textFR varchar(20), textEN varchar(20))
declare @tChild table(idChild int, idMaster int, textFR varchar(20), textEN varchar(20))
INSERT INTO @tMaster values(1, null, null), (2, null, null)
INSERT INTO @tChild values(1,1 ,'element 1', 'element 1'), (2, 1, 'element 2', 'element 2')
INSERT INTO @tChild values(3 , 2, 'element 3', 'element 4')
UPDATE @tMaster
SET textFR = STUFF(REPLACE((SELECT CHAR(10) + CHAR(13) + C.TextFR
FROM @tChild C WHERE C.idMaster = M.idMaster
FOR XML PATH ('')),'[SEQUENCE]',''),1,1,'')
,textEN = STUFF(REPLACE((SELECT CHAR(10) + CHAR(13) + C.textEN
FROM @tChild C WHERE C.idMaster = M.idMaster
FOR XML PATH ('')),'[SEQUENCE]',''),1,1,'')
FROM @tMaster M
SELECT * FROM @tMaster
Switch to text output to see "new lines" in SSMS
For some reason, sequence of characters in REPLACE is not displayed!
That sequence is added by FOR XML PATH when used in conjunction with CHAR(10 + CHAR(10)
Browser cannot show this sequence at all: & # x 0 D ;
Remove spaces between above characters and use it instead of [SEQUENCE] in the query (REPLACE)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply