Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Other Forums
 MS Access
 Two or three columns

Author  Topic 

Michiel
Starting Member

3 Posts

Posted - 2002-05-15 : 09:15:51
Hi,

Because I'm using my table in Visual Basic and I want to make a Data Report from it, I want to find an SQL-query to make several columns in my report.
E.g., when i want to have 2 columns, a table like this:
1 5
2 8
3 2
4 10
5 3
6 7

should be converted to a table like this:
1 5 4 10
2 8 5 3
3 2 6 7

using an SQL query.

Can somebody please help me?

Thanks!

Michiel
Starting Member

3 Posts

Posted - 2002-05-15 : 09:21:29
Oh, i forgot to mention: there could be several pages.
When there are 200 rows and every page can contain 50 rows, the table should look like this:

1 51
. .
. .
. .
50 100
101 151
. .
. .
. .
150 200

Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-15 : 10:46:21
That's not a SQL or query issue, that's a report/presentation thing. You can create multi-column reports in Access, I think the wizard might even do it for you. I can tell you that there is no way to do that as a query.

Go to Top of Page

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-05-15 : 12:08:55
Northwind Customers, 2 report columns, 10 lines per page:

SELECT Col1.Page, Col1.Line,
Col1.CustomerID, Col1.CompanyName, Col2.CustomerID, Col2.CompanyName
FROM (
SELECT C1.CustomerID, C1.CompanyName,
COUNT(*) / 20 AS Page, COUNT(C2.CustomerID) % 20 AS Line
FROM Customers AS C1
LEFT JOIN Customers AS C2 ON C1.CompanyName > C2.CompanyName
GROUP BY C1.CustomerID, C1.CompanyName
HAVING COUNT(*) % 20 BETWEEN 0 AND 9
) AS Col1
LEFT JOIN (
SELECT C1.CustomerID, C1.CompanyName,
COUNT(*) / 20 AS Page, COUNT(C2.CustomerID) % 20 - 10 AS Line
FROM Customers AS C1
LEFT JOIN Customers AS C2 ON C1.CompanyName > C2.CompanyName
GROUP BY C1.CustomerID, C1.CompanyName
HAVING COUNT(*) % 20 BETWEEN 10 AND 19
) AS Col2
ON Col1.Page = Col2.Page AND Col1.Line = Col2.Line
ORDER BY Col1.Page, Col1.Line

 
But just because you can, doesn't mean you should.


Go to Top of Page

Michiel
Starting Member

3 Posts

Posted - 2002-05-16 : 08:35:15
Thank you very much!

Go to Top of Page
   

- Advertisement -