[AccessD] SQL Question

Hello Martin,

I am wondering whether this is a once off, or a query that you will have to run regularly.  In other words, how much effort does it justify

If it is once off, you may be able to synthisise another column that will allow the following which was copied from BOL

SELECT Year, 
    SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1,
    SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2,
    SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3,
    SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4
FROM Northwind.dbo.Pivot
GROUP BY Year
GO

If the data is changing and you need it dynamic, you could create another column, or create a dynamic column count for each address to gain a 1,2,3,4,5 could.

Again, without knowing how much data and how much time will be spent to solve it, it is hard to say whether it is worth creating temp tables, populating them and then selecting the data from them.  You could do that and use an increment to help id the addresses

Let us know more details,

I guess that if it was Jet you would use a  simple cross tab

Mark


  ----- Original Message ----- 
  From: Martin Reid 
  To: accessd 
  Sent: Tuesday, June 24, 2003 4:32 PM
  Subject: [AccessD] SQL Question


  Have two tables 

  Table A Contains staff data, Name etc
  Table B contains contact data

  Results of join as expected

  Staff Member A  Telephone Number 1
  Staff Member A Telephone Number 2
  Staff Member A Telephone Number 3

  And so on

  What I need to do is to flatten this out and create a single table

  Staff Member A     Phone Number 1     Phone Number 2     Phone Number 3 etc
  Staff Member B      Phone Number 1    Phone Number 2

  We dont knwo how many numbers will exist for each member of staff but the Max will be 5 and I must end up with a single table containing the data.

  Need this to work in Access and SQL Server 2000

  Martin








------------------------------------------------------------------------------


  _______________________________________________
  AccessD mailing list
  AccessD at databaseadvisors.com
  http://databaseadvisors.com/mailman/listinfo/accessd
  Website: http://www.databaseadvisors.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030624/eff5d8ca/attachment-0001.html>