sql server new users: How can I concatenate specific fields across records in a query?

Here is the layout of my table:

CREATE TABLE [rdb].[image_use](
[RecordID] [int] IDENTITY(1,1) NOT NULL,
[ImageID] [int] NULL,
[ModuleID] [int] NULL,
[LessonID] [int] NULL,
[TopicID] [int] NULL,
[PageID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

I want to group on the ImageID, then display the remaining fields as
ModuleID.LessonID.TopicID.PageID [LF] and then the next
ModuleID.LessonID.TopicID.PageID for that ImageID, etc. for each record with
the same ImageID.

There would only be one record per ImageID.

Is this doable in a query? I've seen some transaction methods posted on the
web, but I don't have an understanding of how that works and how to
implement it.

Thanks in advance.

[quoted text, click to view]

Hi HX,

If this is formatting for output, then the answer is that you should let
the client handle it. This will always be a slow operation on the
server.

For versions of SQL Server up until SQL Server 2000, the only supported
way to do this server-side is to set up a cursor, loop over the rows and
concatenate them into a variable. If you google, you may find set-based
algorithms, but they are not supported and even known to produce
unexpected results from time to time.

On SQL Server 2005, you can use (or rather: abuse) some of the new XML
functionality to do the concatenation - but only if the input has no
characters that have a special meaning in XML (like <, >, and & - but
there might be more of them). Here is the basic trick:

CREATE TABLE #t
(id int NOT NULL PRIMARY KEY,
txt varchar(20) NOT NULL);
INSERT INTO #t (id, txt)
SELECT 1, 'First' UNION ALL
SELECT 2, 'word' UNION ALL
SELECT 3, 'of' UNION ALL
SELECT 4, 'the' UNION ALL
SELECT 5, 'list';
go

SELECT * FROM #t ORDER BY id;

SELECT STUFF((SELECT ', ' + txt AS "text()"
FROM #t
ORDER BY id
FOR XML PATH('')), 1, 2, '') AS "Concatenated text";

Note that the STUFF is only added to get rid of the leading ', ' in the
final list.

--
Hugo Kornelis, SQL Server MVP