rows as columns

is it possible to write a query so that we can have all rows of one column in a single column
TIA
Yes. You could concatenate all the rows in the query as:
SELECT
column1 + column2 + column3
FROM
yourtable
One thing to note here is, since the columns would have different datatypes if you try to concatenate varchar column with int column SQL Server might throw an error. So it is adviced to use CONVERT function to convert all the values into varchar, something like:
SELECT
( CONVERT(varchar(5),intcolumn1) + CONVERT(varchar(10),decimalcolumn2) + regularvarcharcolumn3 )
FROM
yourtable

Rows and Columns

I have a query that reports all of the data I want. However it creates about 40 columns and 10 rows. I would like to swap the display so that it shows 10 columns and 40 rows, which would cause it to print out more readable. Can it be done?Are you using SQL Server 2005 or SQL Server 2000?|||

Hi,

in SQL Server 200 oyu will have to use appropiate CASE expressions, in SQL Server 2005 you can use the new PIVOT functionality-

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

I am using SQL 2000 which someone else said needed a CASE statement. What I have seen seems to want to add things. What I have is a list of numbers and then columns that are the results of my Query.

I have columns named Store , Name, Net, Tax, Deposits (and a bunch of others)

I have rows with the store numbers 2, 3, 4, 5 ,6 that I sort by

I would like the columns as rows and the store numbers across the top

Thanks

sql

RowNumber using SQL Query

Hi All,
I have following table structure,

----------------------
ChallanID ProductID PublicationDate Description Qty Amt
----------------------
43 9 4/1/2006 ABC 1 880
43 10 5/1/2006 BCA 1 930
43 11 5/1/2006 CBA 1 230

I want a sql query which select all the record with a serial number eg:

---------------------
SN# ChallanID ProductID PublicationDate Description Qty Amt
----------------------
1 43 9 4/1/2006 ABC 1 880
2 43 10 5/1/2006 BCA 1 930
3 43 11 5/1/2006 CBA 1 230use a temp table with identity column and insert your result into the temp table and select it back.|||U havent mentioned about how records to be ordered? In what order u want to generate serial No:?|||temp table shemp table...

SELECT count(*) as [SS#],a.LastName
FROM Employees a join
Employees b
on a.LastName >= b.LastName
group by a.LastName
order by a.LastName

ps. I got this example from somewhere and it is not original work. If the original author sees this and takes any offense I am will to erase from the forum.|||SELECT count(*) as [SS#],a.LastName
FROM Employees a join
Employees b
on a.LastName >= b.LastName
group by a.LastName
order by a.LastName
No, that only works if you use a unique key i.e.
select id=1,name='ccc' into #t1 union all
select 3,'bbb' union all
select 4,'aaa' union all
select 9,'bbb'

select count(*) as [ss#], name=min(a.name)
from #t1 a, #t1 b
where a.id>=b.id
group by a.id
order by 1
else use a temp table with identity column as suggested by khtan
select ss#=identity(int,1,1),name into #t2 from #t1 order by name
select * from #t2|||Problem is, either way you have no guarantee that the "serial number" for any record won't change as the contents of the table changes. Seems to me a "serial number" is expected to be static, so you really should add it as a permanent column to your table (perhaps as an identity datatype).

Rownumber in result of query

Hi there,
Anyone knows how to get the rownumber in the result of the query?
thank you,
Max
Hi
Do you want to get the row of the original table or do you want to add a
numbering sequence to your result?
If it is the 2nd option, you need to add that manually before returning the
result to the client by means of a temp table.
Regards
Mike
"Max" wrote:

> Hi there,
> Anyone knows how to get the rownumber in the result of the query?
> thank you,
> Max
>
>
|||Here is an example.
use pubs
GO
--SELECT * FROM jobs
Select job_desc, (Select Count(*) + 1 FROM jobs B
WHERE B.job_desc < A.job_desc) AS RecNo
FROM jobs A
ORDER By job_desc
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Max" <maxde_vries@.hotmail.com> wrote in message
news:efU1Wu7nEHA.3392@.TK2MSFTNGP15.phx.gbl...
> Hi there,
> Anyone knows how to get the rownumber in the result of the query?
> thank you,
> Max
>
|||Max,
See:
How to dynamically number rows in a SELECT Statement
http://support.microsoft.com/?id=186133
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Max wrote:
> Hi there,
> Anyone knows how to get the rownumber in the result of the query?
> thank you,
> Max
>
|||use Northwind
select
(select count(*) from orders as A where A.orderid <= B.orderid) as
row_num,
orderid, customerid, convert(varchar(10), orderdate, 120) as
orderdate from orders as B order by orderid
"Max" <maxde_vries@.hotmail.com> wrote in message
news:efU1Wu7nEHA.3392@.TK2MSFTNGP15.phx.gbl...
> Hi there,
> Anyone knows how to get the rownumber in the result of the query?
> thank you,
> Max
>

Rownumber in result of query

Hi there,
Anyone knows how to get the rownumber in the result of the query?
thank you,
MaxHi
Do you want to get the row of the original table or do you want to add a
numbering sequence to your result?
If it is the 2nd option, you need to add that manually before returning the
result to the client by means of a temp table.
Regards
Mike
"Max" wrote:
> Hi there,
> Anyone knows how to get the rownumber in the result of the query?
> thank you,
> Max
>
>|||Here is an example.
use pubs
GO
--SELECT * FROM jobs
Select job_desc, (Select Count(*) + 1 FROM jobs B
WHERE B.job_desc < A.job_desc) AS RecNo
FROM jobs A
ORDER By job_desc
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Max" <maxde_vries@.hotmail.com> wrote in message
news:efU1Wu7nEHA.3392@.TK2MSFTNGP15.phx.gbl...
> Hi there,
> Anyone knows how to get the rownumber in the result of the query?
> thank you,
> Max
>|||Max,
See:
How to dynamically number rows in a SELECT Statement
http://support.microsoft.com/?id=186133
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Max wrote:
> Hi there,
> Anyone knows how to get the rownumber in the result of the query?
> thank you,
> Max
>|||use Northwind
select
(select count(*) from orders as A where A.orderid <= B.orderid) as
row_num,
orderid, customerid, convert(varchar(10), orderdate, 120) as
orderdate from orders as B order by orderid
"Max" <maxde_vries@.hotmail.com> wrote in message
news:efU1Wu7nEHA.3392@.TK2MSFTNGP15.phx.gbl...
> Hi there,
> Anyone knows how to get the rownumber in the result of the query?
> thank you,
> Max
>

ROWNUM and ORDER BY

Am using Oracle 9i.

Just wanted to know in which order the db will execute my query if my query contains a 'WHERE ROWNUM < 1000' and an 'ORDER BY ...'.
The documentation says that the order of evauation depends upon the indexes used in the ORDER BY, but doesn't specify clearly in which order.

Please help.You can't use ROWNUM and ORDER BY in the same select because the pseudo column ROWNUM is affected before the sort.

So, you have to do :

Select ... FROM (SELECT ... FROM ... ORDER bY...) WHERE ROWNUM<1000

ROWGUIDCOL column

hi
Can any one explain impact of tunning or query optimization that uses
ROWGUIDCOL?
Thanks
Kalyan
Kalyan wrote:
> hi
> Can any one explain impact of tunning or query optimization that uses
> ROWGUIDCOL?
>
> Thanks
> Kalyan
Can you explain in more detail what you are looking for. Using a GUID as
opposed to a INT IDENTITY, for example, doesn't really hurt performance,
except for the fact that a GUID takes up more space than an INT (16 vs 4
bytes). In that respect, the INT is a better choice.
David Gugick
Imceda Software
www.imceda.com

ROWGUIDCOL column

hi
Can any one explain impact of tunning or query optimization that uses
ROWGUIDCOL?
Thanks
KalyanKalyan wrote:
> hi
> Can any one explain impact of tunning or query optimization that uses
> ROWGUIDCOL?
>
> Thanks
> Kalyan
Can you explain in more detail what you are looking for. Using a GUID as
opposed to a INT IDENTITY, for example, doesn't really hurt performance,
except for the fact that a GUID takes up more space than an INT (16 vs 4
bytes). In that respect, the INT is a better choice.
David Gugick
Imceda Software
www.imceda.com

ROWGUIDCOL column

hi
Can any one explain impact of tunning or query optimization that uses
ROWGUIDCOL?
Thanks
KalyanKalyan wrote:
> hi
> Can any one explain impact of tunning or query optimization that uses
> ROWGUIDCOL?
>
> Thanks
> Kalyan
Can you explain in more detail what you are looking for. Using a GUID as
opposed to a INT IDENTITY, for example, doesn't really hurt performance,
except for the fact that a GUID takes up more space than an INT (16 vs 4
bytes). In that respect, the INT is a better choice.
--
David Gugick
Imceda Software
www.imceda.com

rowdelimiter not accepted in bulk insert statement , used in an sproc - please help

BULK INSERT bill_tbl FROM 'd:\ftp_Data\in\baddress.dat'
WITH
(
FIELDTERMINATOR = ';',
ROWTERMINATOR = '\n'
)
This is the query used to populate bill_tbl.
Actually this baddress.dat contain rowdelimiter of \r\n.
This can be seen by viewing the file in hex format (OD,OA) and also
the format file created by the bulk insert task of dts gives the last
row as \r\n
So i run the above code and it inserts rows into table. No data is
present in last column.
The above bulk insert stmt should leave a carriage return in the sql
table, but i see len is zero as well as i query for it for no avail.
2. So i use a dts with bulk insert. The first time i put a {LF} and it
goes fine, just like above, but again len is zero and i do not see
that it has imported the {CR} character.
3. So i run another bulk insert with {CR}{LF} as row delimiter, and it
goes fine. imports same number of rows and len of last col is zero
4. So i run another bulk insert with {CR} as row delimiter, i get an
error stating : conversion error for first column - makes sense as it
is trying to insert {LF} in first col and the first col size is 1.
5.So the main problem is in the above stmt, i put \r\n, it does not
work. I am not sure why.
I proved it works in the dts. The above code lies in a sproc and is
already written and being used, but they have suddenly discovered they
are having special characters when they try to import the table into a
text file and having problems.
So i would like to keep above code but introduce \r\n as row
delimiter. Can anyone tell me why it is not working ?
thanks
RS
From BOL:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\adminsql.chm::/ad_impt_bcp_0fqq.htm
"...However, it is only necessary to enter the characters \r\n as the
terminator when manually editing the terminator column of a bcp format file.
When you use bcp interactively and specify \n (newline) as the row
terminator, bcp prefixes the \r (carriage return) character automatically..."
So, when you use '\n' in BULK INSERT, SQL Server ads the '\r' character
automatically - that is why it is not part of the data that gets inserted via
BULK INSERT. You should not specify the rowterminator as '\r\n'.

> So i run the above code and it inserts rows into table. No data is
> present in last column.
If you run the above code with '\n' as the row terminator and BULK INSERT is
not inserting any data into the last column, it must be for a different
reason than the rowterminator. Do you have a sample row that you can provide?
Can you take a close look at what is between the last ';' and the 0x0D0A? Try
BULK INSERT with FIRSTROW =1 and LASTROW=1 so that you only insert the first
row and see if you get data.
Thanks.
-Mike
"rshivaraman@.gmail.com" wrote:

> BULK INSERT bill_tbl FROM 'd:\ftp_Data\in\baddress.dat'
> WITH
> (
> FIELDTERMINATOR = ';',
> ROWTERMINATOR = '\n'
> )
> --
> This is the query used to populate bill_tbl.
> Actually this baddress.dat contain rowdelimiter of \r\n.
> This can be seen by viewing the file in hex format (OD,OA) and also
> the format file created by the bulk insert task of dts gives the last
> row as \r\n
> So i run the above code and it inserts rows into table. No data is
> present in last column.
> The above bulk insert stmt should leave a carriage return in the sql
> table, but i see len is zero as well as i query for it for no avail.
> 2. So i use a dts with bulk insert. The first time i put a {LF} and it
> goes fine, just like above, but again len is zero and i do not see
> that it has imported the {CR} character.
> 3. So i run another bulk insert with {CR}{LF} as row delimiter, and it
> goes fine. imports same number of rows and len of last col is zero
> 4. So i run another bulk insert with {CR} as row delimiter, i get an
> error stating : conversion error for first column - makes sense as it
> is trying to insert {LF} in first col and the first col size is 1.
> 5.So the main problem is in the above stmt, i put \r\n, it does not
> work. I am not sure why.
> I proved it works in the dts. The above code lies in a sproc and is
> already written and being used, but they have suddenly discovered they
> are having special characters when they try to import the table into a
> text file and having problems.
> So i would like to keep above code but introduce \r\n as row
> delimiter. Can anyone tell me why it is not working ?
> thanks
> RS
>
|||Here is an example of what I meant. Based on your problem description, the
BULK INSERT should work.
Create a text File, with a crlf at the end of each row:
a;b
c;d
e;f
create table x(c1 char(1), c2 char(1))
go
BULK INSERT x FROM 'c:\dev\x.dat'
WITH
(
FIELDTERMINATOR = ';',
ROWTERMINATOR = '\n'
)
(3 row(s) affected)
select * from x
Results:
c1 c2
-- --
a b
c d
e f
(3 row(s) affected)
-Mike
"rshivaraman@.gmail.com" wrote:

> BULK INSERT bill_tbl FROM 'd:\ftp_Data\in\baddress.dat'
> WITH
> (
> FIELDTERMINATOR = ';',
> ROWTERMINATOR = '\n'
> )
> --
> This is the query used to populate bill_tbl.
> Actually this baddress.dat contain rowdelimiter of \r\n.
> This can be seen by viewing the file in hex format (OD,OA) and also
> the format file created by the bulk insert task of dts gives the last
> row as \r\n
> So i run the above code and it inserts rows into table. No data is
> present in last column.
> The above bulk insert stmt should leave a carriage return in the sql
> table, but i see len is zero as well as i query for it for no avail.
> 2. So i use a dts with bulk insert. The first time i put a {LF} and it
> goes fine, just like above, but again len is zero and i do not see
> that it has imported the {CR} character.
> 3. So i run another bulk insert with {CR}{LF} as row delimiter, and it
> goes fine. imports same number of rows and len of last col is zero
> 4. So i run another bulk insert with {CR} as row delimiter, i get an
> error stating : conversion error for first column - makes sense as it
> is trying to insert {LF} in first col and the first col size is 1.
> 5.So the main problem is in the above stmt, i put \r\n, it does not
> work. I am not sure why.
> I proved it works in the dts. The above code lies in a sproc and is
> already written and being used, but they have suddenly discovered they
> are having special characters when they try to import the table into a
> text file and having problems.
> So i would like to keep above code but introduce \r\n as row
> delimiter. Can anyone tell me why it is not working ?
> thanks
> RS
>
|||Hi Mike
Your answer solves my the question i had in mind.
2. What i meant to tell was, zero data is present in last column which
is a valid scenario . So i was expecting atleast one
carriage return character but the len of the column came as 0. which
is true as there is no data.
So you are saying the /r is assumed automatically.
Thanks a lot for your answer. I was going in loops trying to figure
this out.
On Aug 2, 12:08 pm, Mike Whiting
<MikeWhit...@.discussions.microsoft.com> wrote:
> From BOL:
> mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\adXminsql.chm::/ad_impt_bcp_0fqq.htm
> "...However, it is only necessary to enter the characters \r\n as the
> terminator when manually editing the terminator column of a bcp format file.
> When you use bcp interactively and specify \n (newline) as the row
> terminator, bcp prefixes the \r (carriage return) character automatically..."
> So, when you use '\n' in BULK INSERT, SQL Server ads the '\r' character
> automatically - that is why it is not part of the data that gets insertedvia
> BULK INSERT. You should not specify the rowterminator as '\r\n'.
>
> If you run the above code with '\n' as the row terminator and BULK INSERTis
> not inserting any data into the last column, it must be for a different
> reason than the rowterminator. Do you have a sample row that you can provide?
> Can you take a close look at what is between the last ';' and the 0x0D0A?Try
> BULK INSERT with FIRSTROW =1 and LASTROW=1 so that you only insert the first
> row and see if you get data.
> Thanks.
> -Mike
>
> "rshivara...@.gmail.com" wrote:
>
>
>
> - Show quoted text -
|||>5.So the main problem is in the above stmt, i put \r\n, it does not
>work. I am not sure why.
The documetation for SQL Server 2005 seems a bit clearer about the row
terminator. It describes \r as "Carriage return/line feed". It does
not show \r\n as a choice.
Roy Harvey
Beacon Falls, CT
On Thu, 02 Aug 2007 07:58:26 -0700, rshivaraman@.gmail.com wrote:

>BULK INSERT bill_tbl FROM 'd:\ftp_Data\in\baddress.dat'
>WITH
>(
>FIELDTERMINATOR = ';',
>ROWTERMINATOR = '\n'
>)
>--
>This is the query used to populate bill_tbl.
>Actually this baddress.dat contain rowdelimiter of \r\n.
>This can be seen by viewing the file in hex format (OD,OA) and also
>the format file created by the bulk insert task of dts gives the last
>row as \r\n
>So i run the above code and it inserts rows into table. No data is
>present in last column.
>The above bulk insert stmt should leave a carriage return in the sql
>table, but i see len is zero as well as i query for it for no avail.
>2. So i use a dts with bulk insert. The first time i put a {LF} and it
>goes fine, just like above, but again len is zero and i do not see
>that it has imported the {CR} character.
>3. So i run another bulk insert with {CR}{LF} as row delimiter, and it
>goes fine. imports same number of rows and len of last col is zero
>4. So i run another bulk insert with {CR} as row delimiter, i get an
>error stating : conversion error for first column - makes sense as it
>is trying to insert {LF} in first col and the first col size is 1.
>5.So the main problem is in the above stmt, i put \r\n, it does not
>work. I am not sure why.
>I proved it works in the dts. The above code lies in a sproc and is
>already written and being used, but they have suddenly discovered they
>are having special characters when they try to import the table into a
>text file and having problems.
>So i would like to keep above code but introduce \r\n as row
>delimiter. Can anyone tell me why it is not working ?
>thanks
>RS
sql

Rowcounts on all tables in a DB

I need to create a query to get the row counts of all tables in a
particular database. Can somebody point me to a method of doing this?
Regards,
Randy
This will probably work for what you need:
--2005
select object_name(object_id), rows
from sys.partitions
order by 2 desc
--2000
select object_name(id), rowcnt
from sysindexes
order by 2 desc
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"Randy Galliano" <r_galliano@.yahoo.com> wrote in message
news:OCM3fb1cIHA.1132@.TK2MSFTNGP06.phx.gbl...
>I need to create a query to get the row counts of all tables in a
>particular database. Can somebody point me to a method of doing this?
> Regards,
> Randy
|||Hi
--SQL Server 2005
SELECT
t.name,
[RowCount] = SUM
(
CASE
WHEN (p.index_id < 2) AND (a.type = 1) THEN p.rows
ELSE 0
END
)
FROM
sys.tables t
INNER JOIN sys.partitions p
ON t.object_id = p.object_id
INNER JOIN sys.allocation_units a
ON p.partition_id = a.container_id
GROUP BY
t.name;
"Randy Galliano" <r_galliano@.yahoo.com> wrote in message
news:OCM3fb1cIHA.1132@.TK2MSFTNGP06.phx.gbl...
>I need to create a query to get the row counts of all tables in a
>particular database. Can somebody point me to a method of doing this?
> Regards,
> Randy
|||Thank you for all the help. The queries are just what I needed.
Regards,
Randy.
Randy Galliano wrote:
> I need to create a query to get the row counts of all tables in a
> particular database. Can somebody point me to a method of doing this?
> Regards,
> Randy
|||On Wed, 20 Feb 2008 16:44:12 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:

>Just an FYI that for 2000, the rowcount can very well be off (not reflect reality). In 2005 it is
>likely to reflect reality.
But if you run:
DBCC UPDATEUSAGE(0) WITH COUNT_ROWS
just before you should have accurate numbers for all tables. Or, to
be more precise, each table's count will have been accurate very
recently.
Roy Harvey
Beacon Falls, CT
|||On Feb 20, 5:23Xam, Randy Galliano <r_galli...@.yahoo.com> wrote:
> I need to create a query to get the row counts of all tables in a
> particular database. XCan somebody point me to a method of doing this?
> Regards,
> Randy
Also refer
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/02/different-ways-to-count-rows-from-a-table.aspx

Rowcounts on all tables in a DB

I need to create a query to get the row counts of all tables in a
particular database. Can somebody point me to a method of doing this?
Regards,
RandyThis will probably work for what you need:
--2005
select object_name(object_id), rows
from sys.partitions
order by 2 desc
--2000
select object_name(id), rowcnt
from sysindexes
order by 2 desc
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"Randy Galliano" <r_galliano@.yahoo.com> wrote in message
news:OCM3fb1cIHA.1132@.TK2MSFTNGP06.phx.gbl...
>I need to create a query to get the row counts of all tables in a
>particular database. Can somebody point me to a method of doing this?
> Regards,
> Randy|||Hi
--SQL Server 2005
SELECT
t.name,
[RowCount] = SUM
(
CASE
WHEN (p.index_id < 2) AND (a.type = 1) THEN p.rows
ELSE 0
END
)
FROM
sys.tables t
INNER JOIN sys.partitions p
ON t.object_id = p.object_id
INNER JOIN sys.allocation_units a
ON p.partition_id = a.container_id
GROUP BY
t.name;
"Randy Galliano" <r_galliano@.yahoo.com> wrote in message
news:OCM3fb1cIHA.1132@.TK2MSFTNGP06.phx.gbl...
>I need to create a query to get the row counts of all tables in a
>particular database. Can somebody point me to a method of doing this?
> Regards,
> Randy|||Just an FYI that for 2000, the rowcount can very well be off (not reflect reality). In 2005 it is
likely to reflect reality.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"jason" <jason-r3move@.statisticsio.com> wrote in message
news:B2937C70-6DD2-4E49-8028-1148FF13E8FF@.microsoft.com...
> This will probably work for what you need:
> --2005
> select object_name(object_id), rows
> from sys.partitions
> order by 2 desc
> --2000
> select object_name(id), rowcnt
> from sysindexes
> order by 2 desc
>
> --
> Jason Massie
> www: http://statisticsio.com
> rss: http://feeds.feedburner.com/statisticsio
>
> "Randy Galliano" <r_galliano@.yahoo.com> wrote in message
> news:OCM3fb1cIHA.1132@.TK2MSFTNGP06.phx.gbl...
>>I need to create a query to get the row counts of all tables in a particular database. Can
>>somebody point me to a method of doing this?
>> Regards,
>> Randy
>|||Thank you for all the help. The queries are just what I needed.
Regards,
Randy.
Randy Galliano wrote:
> I need to create a query to get the row counts of all tables in a
> particular database. Can somebody point me to a method of doing this?
> Regards,
> Randy|||On Wed, 20 Feb 2008 16:44:12 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>Just an FYI that for 2000, the rowcount can very well be off (not reflect reality). In 2005 it is
>likely to reflect reality.
But if you run:
DBCC UPDATEUSAGE(0) WITH COUNT_ROWS
just before you should have accurate numbers for all tables. Or, to
be more precise, each table's count will have been accurate very
recently.
Roy Harvey
Beacon Falls, CT|||On Feb 20, 5:23=A0am, Randy Galliano <r_galli...@.yahoo.com> wrote:
> I need to create a query to get the row counts of all tables in a
> particular database. =A0Can somebody point me to a method of doing this?
> Regards,
> Randy
Also refer
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/02/different-ways-t=
o-count-rows-from-a-table.aspx

RowCount using Group BY and Having

Hi,
I have a query that uses Group By and Having to retrieve records.
The query is working fine and I want to get the Row Count of that Query.
How will I do that.
Thanks
Kiran
"Kiran" <Kiran@.nospam.net> wrote in message
news:eIhHM6EAFHA.3908@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a query that uses Group By and Having to retrieve records.
> The query is working fine and I want to get the Row Count of that Query.
> How will I do that.
> Thanks
> Kiran
>
SELECT ... Group By Query
SELECT @.@.ROWCOUNT
You could also save the rowcount into a variable like this:
DECLARE @.MyCount int
SELECT ... Group By Query
SELECT @.MyCount = @.@.RowCount
HTH
Rick Sawtell
MCT, MCSD, MCDBA

RowCount using Group BY and Having

Hi,
I have a query that uses Group By and Having to retrieve records.
The query is working fine and I want to get the Row Count of that Query.
How will I do that.
Thanks
Kiran"Kiran" <Kiran@.nospam.net> wrote in message
news:eIhHM6EAFHA.3908@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a query that uses Group By and Having to retrieve records.
> The query is working fine and I want to get the Row Count of that Query.
> How will I do that.
> Thanks
> Kiran
>
SELECT ... Group By Query
SELECT @.@.ROWCOUNT
You could also save the rowcount into a variable like this:
DECLARE @.MyCount int
SELECT ... Group By Query
SELECT @.MyCount = @.@.RowCount
HTH
Rick Sawtell
MCT, MCSD, MCDBA

RowCount using Group BY and Having

Hi,
I have a query that uses Group By and Having to retrieve records.
The query is working fine and I want to get the Row Count of that Query.
How will I do that.
Thanks
Kiran"Kiran" <Kiran@.nospam.net> wrote in message
news:eIhHM6EAFHA.3908@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a query that uses Group By and Having to retrieve records.
> The query is working fine and I want to get the Row Count of that Query.
> How will I do that.
> Thanks
> Kiran
>
SELECT ... Group By Query
SELECT @.@.ROWCOUNT
You could also save the rowcount into a variable like this:
DECLARE @.MyCount int
SELECT ... Group By Query
SELECT @.MyCount = @.@.RowCount
HTH
Rick Sawtell
MCT, MCSD, MCDBA

RowCount or Equivalent

I have a query that returns a set of rows - sorted by part#. On the report I can hide the duplicates (part#). How can I test the part# so that whenever a new part# starts I can reverse image the whole l line. I have not defined any groups. Is this a must?

Sorry I am not understanding your question.

RowCount does not exist, use CountRows instead, then What does 'reversing the image' means.

If you are after counting rows returned by some reports parameters, something along these lines should help.

=iif(CountRows("SM") <= 0,"Your query returned no result.",(iif(CountRows("SM") >= 64500,"Report is too large to open in Excel. Please use CSV Export then use another database like Access. " & "Number of rows returned: " & CountRows("SM") , "Number of rows returned: " & CountRows("SM") )))

Philippe

RowCount or Equivalent

I have a query that returns a set of rows - sorted by part#. On the report I can hide the duplicates (part#). How can I test the part# so that whenever a new part# starts I can reverse image the whole l line. I have not defined any groups. Is this a must?

Sorry I am not understanding your question.

RowCount does not exist, use CountRows instead, then What does 'reversing the image' means.

If you are after counting rows returned by some reports parameters, something along these lines should help.

=iif(CountRows("SM") <= 0,"Your query returned no result.",(iif(CountRows("SM") >= 64500,"Report is too large to open in Excel. Please use CSV Export then use another database like Access. " & "Number of rows returned: " & CountRows("SM") , "Number of rows returned: " & CountRows("SM") )))

Philippe

rowcount help

i'm trying to get total rows found by query that uses top clause...
for example:
select top 10 myTable.* from myTable where myTable.number > 200
let's say there are 13 rows matching that condition, and by using
@.@.rowcount my result would be: 10.
is there any way to get total row count, without affecting the TOP
clause? i believe that the mysql equivalent would be
SQL_CALC_FOUND_ROWS().
tnx...> is there any way to get total row count, without affecting the TOP
> clause?
One method is with a subquery:
SELECT TOP 10
myTable.*,
(SELECT COUNT(*)
FROM myTable
WHERE myTable.number > 200) AS TotalRows
FROM myTable
WHERE myTable.number > 200
Hope this helps.
Dan Guzman
SQL Server MVP
"D.B." <dejan.bukovic@.gmail.com> wrote in message
news:1148128944.554703.296710@.38g2000cwa.googlegroups.com...
> i'm trying to get total rows found by query that uses top clause...
> for example:
> select top 10 myTable.* from myTable where myTable.number > 200
>
> let's say there are 13 rows matching that condition, and by using
> @.@.rowcount my result would be: 10.
>
> is there any way to get total row count, without affecting the TOP
> clause? i believe that the mysql equivalent would be
> SQL_CALC_FOUND_ROWS().
>
> tnx...
>|||well, it helped...
but i'm worried about the execution time when using full-text search...
anyway, thanx for your help...|||You could select the entire set into a temp table or table variable.
Then, get the count of the temp table, then select the number you want.
e.g.
declare @.tAllRows as table(...)
insert into @.tAllRows
select ... from myTable where myTable.number>200
declare @.iTotalCount int
set @.iTotalCount = @.@.rowcount
select top 10 myTable.*, @.@.rowcount from @.tAllRows
While I usually try to avoid temp tables and table variables,
_sometimes_ they help a lot with performance. Benchmarking would be a
good idea, though, as my approach could be a disaster.
Also, are you paging based on an identity field? If so, you might not
get the results you expect when the numbers are not continuous.|||err...
select top 10 myTable.*, @.iTotalCount from @.tAllRows
my bad...

rowcount help

i'm trying to get total rows found by query that uses top clause...
for example:
select top 10 myTable.* from myTable where myTable.number > 200
let's say there are 13 rows matching that condition, and by using
@.@.rowcount my result would be: 10.
is there any way to get total row count, without affecting the TOP
clause? i believe that the mysql equivalent would be
SQL_CALC_FOUND_ROWS().
tnx...> is there any way to get total row count, without affecting the TOP
> clause?
One method is with a subquery:
SELECT TOP 10
myTable.*,
(SELECT COUNT(*)
FROM myTable
WHERE myTable.number > 200) AS TotalRows
FROM myTable
WHERE myTable.number > 200
--
Hope this helps.
Dan Guzman
SQL Server MVP
"D.B." <dejan.bukovic@.gmail.com> wrote in message
news:1148128944.554703.296710@.38g2000cwa.googlegroups.com...
> i'm trying to get total rows found by query that uses top clause...
> for example:
> select top 10 myTable.* from myTable where myTable.number > 200
>
> let's say there are 13 rows matching that condition, and by using
> @.@.rowcount my result would be: 10.
>
> is there any way to get total row count, without affecting the TOP
> clause? i believe that the mysql equivalent would be
> SQL_CALC_FOUND_ROWS().
>
> tnx...
>|||well, it helped...
but i'm worried about the execution time when using full-text search...
anyway, thanx for your help...|||You could select the entire set into a temp table or table variable.
Then, get the count of the temp table, then select the number you want.
e.g.
declare @.tAllRows as table(...)
insert into @.tAllRows
select ... from myTable where myTable.number>200
declare @.iTotalCount int
set @.iTotalCount = @.@.rowcount
select top 10 myTable.*, @.@.rowcount from @.tAllRows
While I usually try to avoid temp tables and table variables,
_sometimes_ they help a lot with performance. Benchmarking would be a
good idea, though, as my approach could be a disaster.
Also, are you paging based on an identity field? If so, you might not
get the results you expect when the numbers are not continuous.|||err...
select top 10 myTable.*, @.iTotalCount from @.tAllRows
my bad...

rowcount help

i'm trying to get total rows found by query that uses top clause...

for example:
select top 10 myTable.* from myTable where myTable.number > 200

let's say there are 13 rows matching that condition, and by using
@.@.rowcount my result would be: 10.

is there any way to get total row count, without affecting the TOP
clause? i believe that the mysql equivalent would be
SQL_CALC_FOUND_ROWS().

tnx...I answered this question in microsoft.public.sqlserver.server.

It's bad etiquette to post the same question independently to multiple
groups because that causes unnecessary duplication of effort by persons
trying to help. If you have a question appropriate for multiple groups,
specify all groups in a single post so that all responses appear in all
groups.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"D.B." <dejan.bukovic@.gmail.com> wrote in message
news:1148128056.678229.59640@.i39g2000cwa.googlegro ups.com...
> i'm trying to get total rows found by query that uses top clause...
> for example:
> select top 10 myTable.* from myTable where myTable.number > 200
>
> let's say there are 13 rows matching that condition, and by using
> @.@.rowcount my result would be: 10.
>
> is there any way to get total row count, without affecting the TOP
> clause? i believe that the mysql equivalent would be
> SQL_CALC_FOUND_ROWS().
>
> tnx...sql