How group column to show side-by-side?

This is nasty question but...

How can I take the following data from a table:

ID ItemNumber Type
1 9830302 CD
2 9830302 Cassette

And run a select statement to get me:

ID ItemNumber Type
1 9830302 CD/Cassette

Thanks,
Ron

Ron:

There is a pretty good discussion of this issue at this post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1336558&SiteID=1

If you only ever have to worry about a couple of different types you can do something similar to this:

select min_id as id,
itemNumber,
min_type + '/' + max_type as Type
from ( select min(id) as min_id,
max(id) as max_id,
min(type) as min_type,
max(type) as max_type,
itemNumber,
from yourTable
where itemNumber = 983032
) x

|||

Using a trick that Arnie Rowland gave in: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1378712&SiteID=1

You can use an XML ELEMENTS subquery to collapse the multiple items into a single list.

Code Snippet

DECLARE @.ItemTable table
(
ItemID int,
ItemNumber int,
TypeDesc varchar(20)
)

INSERT INTO @.ItemTable Values ( 1, 9830302, 'CD' )
INSERT INTO @.ItemTable Values ( 2, 9830302, 'Cassette' )
INSERT INTO @.ItemTable Values ( 3, 9830303, 'CD' )
INSERT INTO @.ItemTable Values ( 4, 9830304, 'CD' )
INSERT INTO @.ItemTable Values ( 5, 9830304, 'Cassette' )
INSERT INTO @.ItemTable Values ( 6, 9830304, 'DVD' )
INSERT INTO @.ItemTable Values ( 7, 9830305, 'Cassette' )

-- Create Delimited list from multiple rows
-- From Tony Roberson
-- SQL 2005
SELECT DISTINCT ItemNumber, List = SUBSTRING(
(
SELECT '/' + TypeDesc as [text()]
FROM @.ItemTable Det
WHERE Det.ItemNumber = Itm.ItemNumber
FOR XML path(''), elements
), 2, 4096
)
FROM @.ItemTable Itm

I hope this is useful

|||PIVOT ?