concatneting filenames with common id

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 2008 Forums

Transact-SQL (2008)

concatneting filenames with common id

Author

Topic

Swati Jain
Posting Yak Master

139 Posts

Posted - 2009-06-30 : 08:12:54

Hi All,

How to convert following output A to Output B with sql query?

output A
ID File
1 a.tif
2 b.tif
3 c.tif
3 d.tif
3 e.tif
to
output B

ID File
1 a.tif
2 b.tif
3 c.tif;d.tif;e.tif

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-30 : 08:19:41

see concatenate records without UDF

KH
[spoiler]Time is always against us[/spoiler]

Swati Jain
Posting Yak Master

139 Posts

Posted - 2009-06-30 : 08:26:37

quote:
Originally posted by khtan

see concatenate records without UDF

KH
[spoiler]Time is always against us[/spoiler]

Pls explain me with above example as i am newbie to sql..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-30 : 08:29:57

quote:
Originally posted by Swati Jain

quote:
Originally posted by khtan

see concatenate records without UDF

KH
[spoiler]Time is always against us[/spoiler]

Pls explain me with above example as i am newbie to sql..

Read about Cross apply and For xml clause in sql server help file

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-30 : 08:48:32

[code]
-- Testing Table
DECLARE @outputA TABLE
(
[ID] int,
[File] varchar(10)
)

-- Sample Data
INSERT INTO @outputA
SELECT 1, 'a.tif' UNION ALL
SELECT 2, 'b.tif' UNION ALL
SELECT 3, 'c.tif' UNION ALL
SELECT 3, 'd.tif' UNION ALL
SELECT 3, 'e.tif'

-- Query
SELECT [ID],
[File] = STUFF((SELECT ',' + x.[File] FROM @outputA AS x WHERE x.[ID] = a.[ID] FOR XML PATH('')), 1, 1, '')
FROM @outputA a
GROUP BY [ID]

/* -- RESULT
ID File
----------- -----------------
1 a.tif
2 b.tif
3 c.tif,d.tif,e.tif

(3 row(s) affected)
*/
[/code]

Just use the Query and replace @outputA with your actual table name

KH
[spoiler]Time is always against us[/spoiler]

Swati Jain
Posting Yak Master

139 Posts

Posted - 2009-06-30 : 09:24:45

Thanks khtan,

It works brilliantly!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources