2 questions on Sql server query

7 Jun 2006 12:58 AM

Jay

Can these write on a SQL query, but not sp
1. Is it possible to show a column that shows the row number?

2. I have a table like this:
ID, Name, Note
-------------------------
1, Jay, abc
2, Jay, DEF
How to show a record set be:
ID, Name, Note
-------------------------
Jay, abcDEF

This table contain around million rows.

Thank you.

7 Jun 2006 5:04 AM

Uri Dimant

Jay
I'd prefer doing such reports on the client side

create table cars
(
make   varchar(10) NOT NULL ,
model varchar(10) NOT NULL
)
go

insert cars
     ( make, model )
select 'Honda', 'Civic'   UNION ALL
select 'Honda', 'Accord' UNION ALL
select 'Toyota', 'Corolla' UNION ALL
select 'Toyota', 'Camry'   UNION ALL
select 'Toyota', 'Celica' UNION ALL
select 'Toyota', 'Avalon' UNION ALL
select 'Ford',   'Taurus'
go
select make, max(case when ct = 1 then        model else '' end)
           + max(case when ct = 2 then ', ' + model else '' end)
           + max(case when ct = 3 then ', ' + model else '' end)
           + max(case when ct = 4 then ', ' + model else '' end) as models
from( select a.make, a.model, count(*)
          from cars a, cars b
         where a.make = b.make
           and a.model >= b.model
         group by a.make, a.model
      )as t(make, model, ct)
group by make
order by make

Show quoteHide quote

"Jay" <cylix2***@gmail.com> wrote in message
news:1149641900.707043.270260@h76g2000cwa.googlegroups.com...
> Can these write on a SQL query, but not sp
> 1. Is it possible to show a column that shows the row number?
>
>
> 2. I have a table like this:
> ID, Name, Note
> -------------------------
> 1, Jay, abc
> 2, Jay, DEF
> How to show a record set be:
> ID, Name, Note
> -------------------------
> Jay, abcDEF
>
> This table contain around million rows.
>
> Thank you.
>

8 Jun 2006 12:54 AM

Jay

Thank you for your reply,
however it does not solve the problem completely,
I never know how many row with the same model in the table.

Uri Dimant å¯«é“ï¼š

Show quoteHide quote

> Jay
> I'd prefer doing such reports on the client side
>
> create table cars
> (
>   make   varchar(10) NOT NULL ,
>   model varchar(10) NOT NULL
> )
> go
>
> insert cars
>      ( make, model )
> select 'Honda', 'Civic'   UNION ALL
> select 'Honda', 'Accord' UNION ALL
> select 'Toyota', 'Corolla' UNION ALL
> select 'Toyota', 'Camry'   UNION ALL
> select 'Toyota', 'Celica' UNION ALL
> select 'Toyota', 'Avalon' UNION ALL
> select 'Ford',   'Taurus'
> go
> select make, max(case when ct = 1 then        model else '' end)
>            + max(case when ct = 2 then ', ' + model else '' end)
>            + max(case when ct = 3 then ', ' + model else '' end)
>            + max(case when ct = 4 then ', ' + model else '' end) as models
>   from( select a.make, a.model, count(*)
>           from cars a, cars b
>          where a.make = b.make
>            and a.model >= b.model
>          group by a.make, a.model
>       )as t(make, model, ct)
> group by make
> order by make
>
>
>
> "Jay" <cylix2***@gmail.com> wrote in message
> news:1149641900.707043.270260@h76g2000cwa.googlegroups.com...
> > Can these write on a SQL query, but not sp
> > 1. Is it possible to show a column that shows the row number?
> >
> >
> > 2. I have a table like this:
> > ID, Name, Note
> > -------------------------
> > 1, Jay, abc
> > 2, Jay, DEF
> > How to show a record set be:
> > ID, Name, Note
> > -------------------------
> > Jay, abcDEF
> >
> > This table contain around million rows.
> >
> > Thank you.
> >

18 Jun 2006 6:52 AM

Jens

Are you on SQL Server 2005 ? THere is a new function called
ROW_NUMBER() which can give you the number of each row.

HTH, Jens Suessmeyer.

---
http://www.sqlserver2005.de
---