[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

How group a column to show side-by-side?

Ronald S. Cook

3/31/2007 9:30:00 PM

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
5 Answers

xyb

4/1/2007 12:10:00 AM

0

On 4?1?, ??5?30?, "Ronald S. Cook" <r...@westinis.com> wrote:
> 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

em,you have post the sample data and the acquire is not very clear
although :)
You can use one select statement to implement this select at this
issue but can not catch it
when you want multi rows return,i think you can use udf to do this
trick.
some code here for your problem,hope this can help you.
create function u_concatedstring(@ItemNumber numeric(??))
returns varchar(8000)
as
declare @ret varchar(8000)
select @ret = ''
select @ret = @ret + Type + '/' from tablename
where ItemNumber = @ItemNumber<where clause here>
go

select min(ID) ,ItemNumber ,cType = concatedstring(ItemNumber )
from table
group by ItemNumber

Steve Dassin

4/1/2007 5:35:00 AM

0

I knew somebody would find the right path -:)

"Uri Dimant" <urid@iscar.co.il> wrote in message
news:OtPbSnCdHHA.3976@TK2MSFTNGP06.phx.gbl...
> Ron
> ----sql server 2005
>
> Use demo
>
> GO
>
> CREATE TABLE DBO.Test (ID INT,ItemNumber INT, [Type] VARCHAR(20))
>
> INSERT INTO DBO.Test VALUES (1,9830302,'CD')
>
> INSERT INTO DBO.Test VALUES (2,9830302,'Cassette')
>
>
>
> SELECT ItemNumber,
>
> ( SELECT m2.[Type] + ','
>
> FROM DBO.Test m2
>
> WHERE m2.ItemNumber = m1.ItemNumber
>
> ORDER BY [Type]
>
> FOR XML PATH('') ) AS [Type]
>
> FROM DBO.Test m1
>
> GROUP BY ItemNumber;
>
> "Ronald S. Cook" <rcook@westinis.com> wrote in message
> news:%23kD$Gu9cHHA.984@TK2MSFTNGP04.phx.gbl...
> > 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
> >
> >
>
>

Uri Dimant

4/1/2007 6:48:00 AM

0

Ron
----sql server 2005

Use demo

GO

CREATE TABLE DBO.Test (ID INT,ItemNumber INT, [Type] VARCHAR(20))

INSERT INTO DBO.Test VALUES (1,9830302,'CD')

INSERT INTO DBO.Test VALUES (2,9830302,'Cassette')



SELECT ItemNumber,

( SELECT m2.[Type] + ','

FROM DBO.Test m2

WHERE m2.ItemNumber = m1.ItemNumber

ORDER BY [Type]

FOR XML PATH('') ) AS [Type]

FROM DBO.Test m1

GROUP BY ItemNumber;

"Ronald S. Cook" <rcook@westinis.com> wrote in message
news:%23kD$Gu9cHHA.984@TK2MSFTNGP04.phx.gbl...
> 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
>
>


Uri Dimant

4/1/2007 9:28:00 AM

0

People keep back what is the version they are using , so by default it
becames SQL Server 2005 :-))





"Steve Dassin" <rac4sqlnospam@net> wrote in message
news:OTcjO9CdHHA.4352@TK2MSFTNGP03.phx.gbl...
>I knew somebody would find the right path -:)
>
> "Uri Dimant" <urid@iscar.co.il> wrote in message
> news:OtPbSnCdHHA.3976@TK2MSFTNGP06.phx.gbl...
>> Ron
>> ----sql server 2005
>>
>> Use demo
>>
>> GO
>>
>> CREATE TABLE DBO.Test (ID INT,ItemNumber INT, [Type] VARCHAR(20))
>>
>> INSERT INTO DBO.Test VALUES (1,9830302,'CD')
>>
>> INSERT INTO DBO.Test VALUES (2,9830302,'Cassette')
>>
>>
>>
>> SELECT ItemNumber,
>>
>> ( SELECT m2.[Type] + ','
>>
>> FROM DBO.Test m2
>>
>> WHERE m2.ItemNumber = m1.ItemNumber
>>
>> ORDER BY [Type]
>>
>> FOR XML PATH('') ) AS [Type]
>>
>> FROM DBO.Test m1
>>
>> GROUP BY ItemNumber;
>>
>> "Ronald S. Cook" <rcook@westinis.com> wrote in message
>> news:%23kD$Gu9cHHA.984@TK2MSFTNGP04.phx.gbl...
>> > 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
>> >
>> >
>>
>>
>
>


Ronald S. Cook

4/1/2007 9:05:00 PM

0

And that's my version.. Thanks for the reply.


"Uri Dimant" <urid@iscar.co.il> wrote in message
news:uOPtgAEdHHA.4720@TK2MSFTNGP04.phx.gbl...
> People keep back what is the version they are using , so by default it
> becames SQL Server 2005 :-))
>
>
>
>
>
> "Steve Dassin" <rac4sqlnospam@net> wrote in message
> news:OTcjO9CdHHA.4352@TK2MSFTNGP03.phx.gbl...
>>I knew somebody would find the right path -:)
>>
>> "Uri Dimant" <urid@iscar.co.il> wrote in message
>> news:OtPbSnCdHHA.3976@TK2MSFTNGP06.phx.gbl...
>>> Ron
>>> ----sql server 2005
>>>
>>> Use demo
>>>
>>> GO
>>>
>>> CREATE TABLE DBO.Test (ID INT,ItemNumber INT, [Type] VARCHAR(20))
>>>
>>> INSERT INTO DBO.Test VALUES (1,9830302,'CD')
>>>
>>> INSERT INTO DBO.Test VALUES (2,9830302,'Cassette')
>>>
>>>
>>>
>>> SELECT ItemNumber,
>>>
>>> ( SELECT m2.[Type] + ','
>>>
>>> FROM DBO.Test m2
>>>
>>> WHERE m2.ItemNumber = m1.ItemNumber
>>>
>>> ORDER BY [Type]
>>>
>>> FOR XML PATH('') ) AS [Type]
>>>
>>> FROM DBO.Test m1
>>>
>>> GROUP BY ItemNumber;
>>>
>>> "Ronald S. Cook" <rcook@westinis.com> wrote in message
>>> news:%23kD$Gu9cHHA.984@TK2MSFTNGP04.phx.gbl...
>>> > 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
>>> >
>>> >
>>>
>>>
>>
>>
>
>