June 2, 2004 at 6:01 am
i have a query in which there is a group by companyname,
i fire this query using ado recordset get the content and show in the report (which is made in asp and html)
what i want is to have a break so that there will be blank line in a recordset, so that i can insert a blank line in the report
so that they can differntiate between companines ?/
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
June 2, 2004 at 6:26 am
Do it in the report. Something like this. You will have to pad this with html (paragraphs, table etc).
<%
prevcompanyname = ""
if rs.eof = false then prevcompanyname = rs("companyname")
do until rs.eof
if rs("companyname") <> prevcompanyname then
prevcompanyname = rs("companyname")
%>
<%end if%>
<%=rs("companyname")%>
<%
rs.movenext
loop
%>
Far away is close at hand in the images of elsewhere.
Anon.
June 2, 2004 at 9:31 am
One more thing to notice: if you use "GROUP BY companyname", you should also use "ORDER BY companyname". Usually you get the results ordered even if you don't use ORDER BY, but this is not guaranteed because SQL Server may later use another query plan.
Here is a quote from Books Online:
"Note If the ORDER BY clause is not specified, groups returned using the GROUP BY clause are not in any particular order. It is recommended that you always use the ORDER BY clause to specify a particular ordering of the data."
Razvan
June 4, 2004 at 10:28 am
One way, if you want to do it in SQL would be:
SELECT companyname, field2, field3
FROM
(select companyname, companyname as sortname, 1 as level, field2, field3
from table1
UNION all
SELECT '' as companyname, companyname as sortname, 2 as level,
'' as field2, '' as field3
from table1
group by companyname
) as a
ORDER BY sortname, level
I hope this gives you an idea.
If you want to sort on any other of the returned fields just add them to the final sort:
SELECT companyname, field2, field3
FROM
(select companyname, companyname as sortname, 1 as level, field2, field3
from table1
ORDER BY field2
UNION all
SELECT '' as companyname, companyname as sortname, 2 as level,
'' as field2, '' as field3
from table1
group by companyname
) as a
ORDER BY sortname, level, field2, field3
If any of field2, field3 are numeric, then convert then to varchar() in the top select using: '£' + CONVERT(varchar(20), field2, 1) as field2 or some variation.
Regards
Peter Tillotson
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply