Merging several records of a field

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

Merging several records of a field

Author

Topic

Khus
Starting Member

2 Posts

Posted - 2013-09-08 : 03:27:46

Hi
I have a table like
Number Desc
1 Bank
2 Shop
3 Store
2 Home
1 Mall
2 House

I want to have a result as
Number Desc All
1 Bank Mall
2 Shop Home House
3 Store

Can you someone help to do this using a proper select syntax

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-08 : 05:08:21

How do you plan to make sure "Shop" is handled before "Home" which should be handled before" House"?

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Khus
Starting Member

2 Posts

Posted - 2013-09-08 : 06:29:42

Thank you for the reply.
I will have a field for the order like
Number Desc Order
1 Bank 1
2 Shop 1
3 Store 1
2 Home 2
1 Mall 2
2 House 3

quote:
Originally posted by SwePeso

How do you plan to make sure "Shop" is handled before "Home" which should be handled before" House"?

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-08 : 13:44:37

[code]DECLARE @Sample TABLE
(
Number TINYINT NOT NULL,
[Desc] VARCHAR(100) NOT NULL,
[Order] TINYINT NOT NULL
);

INSERT @Sample
VALUES (1, 'Bank', 1),
(2, 'Shop', 1),
(3, 'Store', 1),
(2, 'Home', 2),
(1, 'Mall', 2),
(2, 'House', 3);

-- SwePeso
SELECT n.Number,
STUFF(CAST(f.Data AS VARCHAR(MAX)), 1, 1, '') AS [Desc All]
FROM (
SELECT Number
FROM @Sample
GROUP BY Number
) AS n
CROSS APPLY (
SELECT ' ' + x.[Desc]
FROM @Sample AS x
WHERE x.Number = n.Number
ORDER BY x.[Order]
FOR XML PATH(''),
TYPE
) AS f(Data);[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources