Help with a simple SQL2005 stored proc

[wxman]

This is a home project (not work related) that is driving me nuts!!

I am trying to select all my all time weather records from my database so that I can display them on a “all time weather records page”..

I can grab all my weather all time records from my database via simple multiple select statements and then insert these into a table – but they end up as

9.8 2007-01-27 13:26:00
8.7 2007-01-27 14:53:00
17 2007-01-27 13:51:00
1031.3 2007-01-27 13:23:00
1031.0 2007-01-27 14:01:00
10.0 2007-01-27 13:23:00
7.2 2007-01-27 13:51:00
0.0 2007-01-27 13:23:00
421 2007-01-27 13:25:00

What I really want is the records returned on a single row I.E.

AirTempHi, AirTempHiDate, AirTempLo, AirTempLoDate etc..

Can anyone point me in the right direction? – Here is my storedProc ..

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Stuart Robinson>
-- Create date: <Create Date,20070127>
-- Description: <To be Used with ByLink.com - Records.aspx
-- =============================================
ALTER PROCEDURE [dbo].[spu_GetWxSTNRecords]

AS
BEGIN
SET NOCOUNT ON;

CREATE TABLE [dbo].[#tempRecords](
[Reading] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[ReadingDate] [SmallDateTime] NULL,
) ON [PRIMARY]

INSERT INTO [dbo].[#tempRecords]
SELECT TOP 1 AirTemp AS AirTempHi, importTime AS AirTempHiDate
FROM WxSTNData
ORDER BY AirTemp DESC

INSERT INTO [dbo].[#tempRecords]
SELECT TOP 1 AirTemp AS AirTempLo, importTime AS AirTempLoDate
FROM WxSTNData
ORDER BY AirTemp

INSERT INTO [dbo].[#tempRecords]
SELECT TOP 1 WindG, importTime AS WindGHiDate
FROM WxSTNData
ORDER BY WindG DESC

INSERT INTO [dbo].[#tempRecords]
SELECT TOP 1 SLP AS SlpHi, importTime AS SlpHiDate
FROM WxSTNData
ORDER BY SLP DESC

INSERT INTO [dbo].[#tempRecords]
SELECT TOP 1 SLP AS SlpLo, importTime AS SlpLoDate
FROM WxSTNData
ORDER BY SLP

INSERT INTO [dbo].[#tempRecords]
SELECT TOP 1 GTemp AS GTempHi, importTime AS GTempHiDate
FROM WxSTNData
ORDER BY GTemp DESC

INSERT INTO [dbo].[#tempRecords]
SELECT TOP 1 GTemp AS GTempLo, importTime AS GTempLoDate
FROM WxSTNData
ORDER BY GTemp

INSERT INTO [dbo].[#tempRecords]
SELECT TOP 1 UV AS UVHi, importTime AS UVHiDate
FROM WxSTNData
ORDER BY UV DESC

INSERT INTO [dbo].[#tempRecords]
SELECT TOP 1 Solar AS SolarHi, importTime AS SolarHiDate
FROM WxSTNData
ORDER BY Solar DESC

SELECT * from [dbo].[#tempRecords]
DROP TABLE [dbo].[#tempRecords]
END

[dang65]

I'm not a DBA so there's probably a much more elegant way than this, but maybe you could try:

1. Declare variables for each of the values you want:

declare @AirTempHi varchar(50)
declare @AirTempHiDate datetime
declare @AirTempLo varchar(50)
declare @AirTempLoDate datetime

etc

2. Include all the columns in the temporary table:

create table #AllEmployees
(
AirTempHi varchar(50),
AirTempHiDate datetime,
AirTempLo varchar(50),
AirTempLoDate datetime ... and so on
)

3. Get the values for each variable:

SELECT TOP 1 @AirTempHi = AirTemp, @AirTempHiDate = importTime FROM WxSTNData
ORDER BY AirTemp DESC

etc

4. Once you've got all the values then do one insert into the temporary table:

INSERT INTO [dbo].[#tempRecords]
(AirTempHi, AirTempHiDate, AirTempLo, AirTempLoDate)
values(@AirTempHi, @AirTempHiDate, @AirTempLo, @AirTempLoDate)

etc

5. Then do one select on the final temp table contents, as in your version.

I'm not certain of all the syntax, but it's something like that anyway.

[wxman]

Great, this looks good - I have will have a fiddle after the Forest / Chelsea match.

Thnaks for taking the time to reply