combining multiple rows into one row

[Database Administrator]

Hello Everyone,

This seems simple, but I am finding it hard to do it without a temp table or
a cursor. I need to do it without both.

The values of PRTHASH below need to become a single row

select TOP(5) CUST_ID,
hashbytes('SHA1', CAST(CUST_ID AS VARCHAR) +
',' + NAME1 +
',' + CAST(CUST_TIMESTAMP AS VARCHAR) )
AS 'PRTHASH'
from cust
where Cust_ID > @start
order by Cust_ID

I can do the following if I store CUST_ID and PRTHASH in a TEMP table

select @part ='';
select @part= @part + cast(PRTHASH AS VARCHAR)+ ',' from TEMP

But without a temp table or cursor, I am lost. I believe a correlated
subquery or replace statements with CASE can be used, but can't figure out
how. Even if I do use them, wouldn't this require writing many select
statements, if I select TOP 100 rows.

Please give me some hint if there exists one.

[Database Administrator]

I have seen some posts below that answer a similar question, but they either
use a function or save a table somewhere. I am not supposed to do that. I
also need to use a command that most major databases will understand. The
single row strings must fall out of select statements. Is that possible?
Thanks for your help!




"mayub" wrote:

> Hello Everyone,
>
> This seems simple, but I am finding it hard to do it without a temp table or
> a cursor. I need to do it without both.
>
> The values of PRTHASH below need to become a single row
>
> select TOP(5) CUST_ID,
> hashbytes('SHA1', CAST(CUST_ID AS VARCHAR) +
> ',' + NAME1 +
> ',' + CAST(CUST_TIMESTAMP AS VARCHAR) )
> AS 'PRTHASH'
> from cust
> where Cust_ID > @start
> order by Cust_ID
>
> I can do the following if I store CUST_ID and PRTHASH in a TEMP table
>
> select @part ='';
> select @part= @part + cast(PRTHASH AS VARCHAR)+ ',' from TEMP
>
> But without a temp table or cursor, I am lost. I believe a correlated
> subquery or replace statements with CASE can be used, but can't figure out
> how. Even if I do use them, wouldn't this require writing many select
> statements, if I select TOP 100 rows.
>
> Please give me some hint if there exists one.
>
>
>

[Database Administrator]

Here is the answer to my question:

select @part='';
select TOP(5) @part= @part+ cast(hashbytes('SHA1', CAST(CUST_ID AS
VARCHAR) +
',' + NAME1 +
',' + CAST(CUST_TIMESTAMP AS VARCHAR) )+
AS VARCHAR)+ ','
from cust
where Cust_ID > @start
order by cust_ID;




"mayub" wrote:

> I have seen some posts below that answer a similar question, but they either
> use a function or save a table somewhere. I am not supposed to do that. I
> also need to use a command that most major databases will understand. The
> single row strings must fall out of select statements. Is that possible?
> Thanks for your help!
>
>
>
>
> "mayub" wrote:
>
> > Hello Everyone,
> >
> > This seems simple, but I am finding it hard to do it without a temp table or
> > a cursor. I need to do it without both.
> >
> > The values of PRTHASH below need to become a single row
> >
> > select TOP(5) CUST_ID,
> > hashbytes('SHA1', CAST(CUST_ID AS VARCHAR) +
> > ',' + NAME1 +
> > ',' + CAST(CUST_TIMESTAMP AS VARCHAR) )
> > AS 'PRTHASH'
> > from cust
> > where Cust_ID > @start
> > order by Cust_ID
> >
> > I can do the following if I store CUST_ID and PRTHASH in a TEMP table
> >
> > select @part ='';
> > select @part= @part + cast(PRTHASH AS VARCHAR)+ ',' from TEMP
> >
> > But without a temp table or cursor, I am lost. I believe a correlated
> > subquery or replace statements with CASE can be used, but can't figure out
> > how. Even if I do use them, wouldn't this require writing many select
> > statements, if I select TOP 100 rows.
> >
> > Please give me some hint if there exists one.
> >
> >
> >

[Database Administrator]

mayub,

Yes, that does appear to work in SQL Server, but you also said, " I also
need to use a command that most major databases will understand." I am
unsure that "most major databases" will process this the same way.

Also, I do not believe that the "SELECT TOP (5) ... ORDER BY Cust_ID" is
guaranteed to build your string in low to high order, if that is important
to you. The TOP(5) will be honored, but the optimizer does not need to sort
in order to honor that directive.

So, you can wind up with a strings that has your TOP (5) in an order such
as: 5,1,4,2,3

FWIW,
RLF

"mayub" wrote in message
news:1C534037-2F16-4615-B923-FB696810ED01@microsoft.com...
> Here is the answer to my question:
>
> select @part='';
> select TOP(5) @part= @part+ cast(hashbytes('SHA1', CAST(CUST_ID AS
> VARCHAR) +
> ',' + NAME1 +
> ',' + CAST(CUST_TIMESTAMP AS
> VARCHAR) )+
> AS VARCHAR)+ ','
> from cust
> where Cust_ID > @start
> order by cust_ID;
>
>
>
>
> "mayub" wrote:
>
>> I have seen some posts below that answer a similar question, but they
>> either
>> use a function or save a table somewhere. I am not supposed to do that. I
>> also need to use a command that most major databases will understand. The
>> single row strings must fall out of select statements. Is that possible?
>> Thanks for your help!
>>
>>
>>
>>
>> "mayub" wrote:
>>
>> > Hello Everyone,
>> >
>> > This seems simple, but I am finding it hard to do it without a temp
>> > table or
>> > a cursor. I need to do it without both.
>> >
>> > The values of PRTHASH below need to become a single row
>> >
>> > select TOP(5) CUST_ID,
>> > hashbytes('SHA1', CAST(CUST_ID AS VARCHAR) +
>> > ',' + NAME1 +
>> > ',' + CAST(CUST_TIMESTAMP AS
>> > VARCHAR) )
>> > AS 'PRTHASH'
>> > from cust
>> > where Cust_ID > @start
>> > order by Cust_ID
>> >
>> > I can do the following if I store CUST_ID and PRTHASH in a TEMP table
>> >
>> > select @part ='';
>> > select @part= @part + cast(PRTHASH AS VARCHAR)+ ',' from TEMP
>> >
>> > But without a temp table or cursor, I am lost. I believe a correlated
>> > subquery or replace statements with CASE can be used, but can't figure
>> > out
>> > how. Even if I do use them, wouldn't this require writing many select
>> > statements, if I select TOP 100 rows.
>> >
>> > Please give me some hint if there exists one.
>> >
>> >
>> >