Two or three columns - SQL Server Forums

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

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.

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.

Michiel
Starting Member

3 Posts

Posted - 2002-05-16 : 08:35:15

Thank you very much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources