Merge rows - 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

General SQL Server Forums

New to SQL Server Programming

Merge rows

Author

Topic

fastmichaels
Yak Posting Veteran

71 Posts

Posted - 2008-11-20 : 09:11:26

I guys, I know how to combine fields of a row using the cast function, but after that I would like to merge rows. So the fields of a row have been cast together, the fields of another row have been cast together and the same for the fields of a third row, How do you merge all the three cast rows together? i.e.

Firstname Lastname Age
John Smith 25
Paul Adams 20
James Jones 26

to read "John Smith 25, Paul Adams 20, James Jones 26" with or without the commas it doesn't matter.

Thanks everyone

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-20 : 09:24:54

[code]
DECLARE @DataList varchar(8000)
SELECT @DataList=COALESCE(@DataList,'') + Firstname + Lastname + CAST(Age AS varchar(3)) + ','
FROM YourTable
[/code]

fastmichaels
Yak Posting Veteran

71 Posts

Posted - 2008-11-20 : 09:34:21

quote:

Originally posted by visakh16


DECLARE @DataList varchar(8000)
SELECT @DataList=COALESCE(@DataList,'') + Firstname + Lastname + CAST(Age AS varchar(3)) + ','
FROM YourTable

Thanks mate, which part tells it to select 3 rows of cast fields?

Thanks for your help

fastmichaels
Yak Posting Veteran

71 Posts

Posted - 2008-11-20 : 09:53:32

quote:

Originally posted by visakh16


DECLARE @DataList varchar(8000)
SELECT @DataList=COALESCE(@DataList,'') + Firstname + Lastname + CAST(Age AS varchar(3)) + ','
FROM YourTable

I am getting an erro say " Must declare the scalar variable "@DataList" "

Any ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-20 : 10:10:39

quote:
Originally posted by fastmichaels

quote:
Originally posted by visakh16

DECLARE @DataList varchar(8000)
SELECT @DataList=COALESCE(@DataList,'') + Firstname + Lastname + CAST(Age AS varchar(3)) + ','
FROM YourTable

I am getting an erro say " Must declare the scalar variable "@DataList" "

Any ideas?

show your query. i dont think you're using same query i provided as it worked fine for me

fastmichaels
Yak Posting Veteran

71 Posts

Posted - 2008-11-20 : 10:22:25

quote:
Originally posted by visakh16

quote:
Originally posted by fastmichaels

quote:
Originally posted by visakh16

DECLARE @DataList varchar(8000)
SELECT @DataList=COALESCE(@DataList,'') + Firstname + Lastname + CAST(Age AS varchar(3)) + ','
FROM YourTable

I am getting an erro say " Must declare the scalar variable "@DataList" "

Any ideas?

show your query. i dont think you're using same query i provided as it worked fine for me

DECLARE @DataList varchar(8000)
SELECT id, @DataList=COALESCE(@DataList,'') + firstname + lastname + CAST(age as varchar(3)) + ',' FROM table3

"id" is a field I do not want merging with the rest so it would look like:

id cast field
001 John Smith 26, Paul.......

the same id will be on each of the 3 rows, but I only want the firstname lastname and age fields of all rows merging, with the id field separate.

Hope you understand.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-20 : 10:26:38

nope. you cant mix up variable assigning and field selection in same select.
b/w are you using sql 2005?

fastmichaels
Yak Posting Veteran

71 Posts

Posted - 2008-11-20 : 10:27:55

quote:
Originally posted by visakh16

nope. you cant mix up variable assigning and field selection in same select.
b/w are you using sql 2005?

MS SQL Express 2005

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-20 : 10:34:43

then i think you can try like this


SELECT DISTINCT t.ID,STUFF((SELECT ','+Firstname + Lastname + CAST(Age AS varchar(3)) FROM YourTable WHERE ID=t.ID FOR XML PATH('')),1,1,'')
FROM YourTable t

fastmichaels
Yak Posting Veteran

71 Posts

Posted - 2008-11-20 : 10:44:27

quote:
Originally posted by visakh16

nope. you cant mix up variable assigning and field selection in same select.
b/w are you using sql 2005?

What I want to do is use three rows and merge them all totgether, apart from the id field, I want that to be to be merged with the id field of the other 2 rows, but only have the data in it apear once as the id will be the same on each row. Currently my row is as follows:

SELECT id,cast(age as varchar(3))+' '+firstname+' '+lastname FROM table3

I want three of these merged together with one id and then the merged firstname lastname and age fields. so:

id firstname lastname aga
001 John Smith 25
001 Paul Adams 20
001 James Jones 26

becomes:

id all merged fields together
001 John Smith 25, Paul Adams 20, James Jones 26

does this make sense?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-20 : 10:45:58

yup then use my last posted suggestion


SELECT DISTINCT t.ID,STUFF((SELECT ','+Firstname + Lastname + CAST(Age AS varchar(3)) FROM YourTable WHERE ID=t.ID FOR XML PATH('')),1,1,'')
FROM YourTable t

fastmichaels
Yak Posting Veteran

71 Posts

Posted - 2008-11-20 : 11:11:55

quote:
Originally posted by visakh16

yup then use my last posted suggestion

SELECT DISTINCT t.ID,STUFF((SELECT ','+Firstname + Lastname + CAST(Age AS varchar(3)) FROM YourTable WHERE ID=t.ID FOR XML PATH('')),1,1,'')
FROM YourTable t

what is the "t" part, do I only need to change the "yourtable" part?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-20 : 11:14:29

quote:
Originally posted by fastmichaels

quote:
Originally posted by visakh16

yup then use my last posted suggestion

SELECT DISTINCT t.ID,STUFF((SELECT ','+Firstname + Lastname + CAST(Age AS varchar(3)) FROM YourTable WHERE ID=t.ID FOR XML PATH('')),1,1,'')
FROM YourTable t

what is the "t" part, do I only need to change the "yourtable" part?

yup replace yourtable with actual table name both outside and inside the subquery

fastmichaels
Yak Posting Veteran

71 Posts

Posted - 2008-11-20 : 11:21:42

quote:
Originally posted by visakh16

quote:
Originally posted by fastmichaels

quote:
Originally posted by visakh16

yup then use my last posted suggestion

SELECT DISTINCT t.ID,STUFF((SELECT ','+Firstname + Lastname + CAST(Age AS varchar(3)) FROM YourTable WHERE ID=t.ID FOR XML PATH('')),1,1,'')
FROM YourTable t

what is the "t" part, do I only need to change the "yourtable" part?

yup replace yourtable with actual table name both outside and inside the subquery

I am getting an error saying "The multi-part identifier "t.id" could not be bound" and "Incorrect syntax near the keyword 'FROM'"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-20 : 11:25:45

show your query please

fastmichaels
Yak Posting Veteran

71 Posts

Posted - 2008-11-20 : 11:32:03

quote:
Originally posted by visakh16

show your query please

SELECT DISTINCT t.ID,STUFF((SELECT ','+Firstname + Lastname + CAST(Age AS varchar(3)) FROM table3 WHERE ID=t.ID FOR XML PATH('')),1,1,'')
FROM table3 t

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-20 : 11:41:07

what about this?


SELECT t.ID,LEFT(cl.ColList,LEN(cl.ColList)-1)
FROM (SELECT DISTINCT ID FROM table3)t
CROSS APPLY(SELECT Firstname + Lastname + CAST(Age AS varchar(3))+ ',' FROM table3 
WHERE ID=t.ID 
FOR XML PATH(''))cl(ColList)

also make sure your column names are ID,Firstname,Lastname,... in your table

fastmichaels
Yak Posting Veteran

71 Posts

Posted - 2008-11-20 : 11:50:46

quote:
Originally posted by visakh16

what about this?

SELECT t.ID,LEFT(cl.ColList,LEN(cl.ColList)-1)
FROM (SELECT DISTINCT ID FROM table3)t
CROSS APPLY(SELECT Firstname + Lastname + CAST(Age AS varchar(3))+ ',' FROM table3
WHERE ID=t.ID
FOR XML PATH(''))cl(ColList)

also make sure your column names are ID,Firstname,Lastname,... in your table

There are loads of error messages with this one. Can I just check that all the "ID"'s in your script refer to my field and are not code elements. If I changed my "ID" field to "ref" how would it change your above code?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-20 : 11:56:51

[code]DECLARE @Sample TABLE
(
ID CHAR(3),
FirstName VARCHAR(20),
LastName VARCHAR(20),
Age TINYINT
)

INSERT @Sample
SELECT '001', 'John', 'Smith', 25 UNION ALL
SELECT '001', 'Paul', 'Adams', 20 UNION ALL
SELECT '001', 'James', 'Jones', 26

SELECT *
FROM @Sample

SELECT t.ID,
STUFF(cl.x, 1, 2, '') AS NameList
FROM (
SELECT ID
FROM @Sample
GROUP BY ID
) AS t
CROSS APPLY (
SELECT ', ' + s.Firstname + ' ' + s.Lastname + ' ' + CAST(s.Age AS VARCHAR(3))
FROM @Sample AS s
WHERE s.ID = t.ID
FOR XML PATH('')
) AS cl(x)[/code]

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-20 : 11:57:36

[code]
SELECT t.ref,LEFT(cl.ColList,LEN(cl.ColList)-1)
FROM (SELECT DISTINCT ref FROM table3)t
CROSS APPLY(SELECT Firstname + Lastname + CAST(Age AS varchar(3))+ ',' FROM table3
WHERE ref=t.ref
FOR XML PATH(''))cl(ColList)
[/code]

if you could post some table sample data that would be great

fastmichaels
Yak Posting Veteran

71 Posts

Posted - 2008-11-20 : 12:21:33

quote:
Originally posted by visakh16

SELECT t.ref,LEFT(cl.ColList,LEN(cl.ColList)-1)
FROM (SELECT DISTINCT ref FROM table3)t
CROSS APPLY(SELECT Firstname + Lastname + CAST(Age AS varchar(3))+ ',' FROM table3
WHERE ref=t.ref
FOR XML PATH(''))cl(ColList)

if you could post some table sample data that would be great

It is as before but I have changes "id" to "ref" in the table just to ensure I am not getting confused with "id" as a field name and id as a code element.

ref firstname lastname aga
001 John Smith 25
001 Paul Adams 20
001 James Jones 26

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources