combine rows that belong together

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)

combine rows that belong together

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2009-03-30 : 04:49:03

Hi

I have a table that looks like this..


MachineNo  Rowno  sectionID  Mtype

123        20      11111     22
456        20      11111     22
789        30      22222     33
122        30      22222     33

Then I run a query where based on "RowNo" and "SectionID", but I want to group the result so that the result looks like this..


MachineNo  Rowno   sectionID   Mtype

123, 456   20     11111        22
789, 122   30     22222        33

How do I group them together?

swathigardas
Posting Yak Master

149 Posts

Posted - 2009-03-30 : 05:51:17

DECLARE @tab table (MachineNo INT , Rowno INT, sectionID INT , Mtype INT)

INSERT INTO @Tab (MachineNo , Rowno , sectionID , Mtype )
SELECT 123 , 20 , 11111 , 22 union all
SELECT 456 , 20 , 11111 , 22 union all
SELECT 789 , 30 , 22222 , 33 union all
Select 122 , 30 , 22222 , 33

SELECT DISTINCT (SELECT CONVERT(VARCHAR,MachineNo)+',' AS 'data()'
FROM @tab t2
WHERE t2.Rowno=t1.RowNo
FOR XML PATH ('')) Machines ,Rowno,SectionID,Mtype

FROM @tab t1

magmo
Aged Yak Warrior

558 Posts

Posted - 2009-03-30 : 06:54:44

Hi

I get a syntax error near "xml"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources