Help with syntax - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Help with syntax

Author

Topic

tuffetobbe
Starting Member

2 Posts

Posted - 2011-08-31 : 08:28:45

Hi ppl
First time posting here on the forum. I have a problem that I can´t find a solution for at the moment, perhaps somebody can help me. I got tbl_1 with 3 columns(id,ix,text) that I want to use to create tbl_2(id, text).
For example tbl_1
(1,1, 'Hi')
(1,2,'Ppl!')
(2,1,'Bye')
(2,2,'Bye')
(2,3,'Ppl')

Should populate tbl_2 like
(1,'Hi Ppl')
(2,'Bye Bye Ppl')

Any suggestions on how to do this as easy as possible? (Tbl_1 have around 8 million rows)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-31 : 08:40:28

[code]insert into tbl_2 (id,[text])
select distinct id,
stuff((select ' ' + [text] from tbl_1 where id = t.id for xml path ('')),1,1,'')
from tbl_1 t
[/code]

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-08-31 : 08:41:38

[code]

-- First round
SELECT ID,
CAST([Text] AS VARCHAR(MAX)) AS [Text]
INTO dbo.Table2
FROM dbo.Table1
WHERE IX = 1

DECLARE @IX INT,
@End INT

SELECT @IX = 1,
@End = MAX(IX)
FROM dbo.Table1

-- Iterate remaining
WHILE @IX <= @End
BEGIN
SET @IX = @IX + 1

UPDATE t2
SET t2.[Text] = t2.[Text] + ' ' + t1.[Text]
FROM dbo.Table2 AS t2
INNER JOIN dbo.Table1 AS t1 ON t1.ID = t2.ID
AND t1.IX = @IX
END[/code]

N 56°04'39.26"
E 12°55'05.63"

tuffetobbe
Starting Member

2 Posts

Posted - 2011-08-31 : 09:02:45

Tried SwePeso:s version and it worked like a charm. Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources