TSQL Select Stmt

[JohnBates]

hi all,

I have a table which contains a date column and a column that contains a numerical value....only 1 record for each date:

(varchar) (smalldatetime) (Int)
DatabaseName Date Qty
Db1 3/1/2009 00:00:00 2501
Db1 4/1/2009 00:00:00 3722
Db2 3/1/2009 00:00:00 1488
Db2 4/1/2009 00:00:00 1754

one record for the 1st day of each month for each DatabaseName

My objective is to select the Quantities for presentation
in an Excel worksheet... but place the quantities across the line for each database.

How can I construct the Select statement so that it selects all quantities for a database name with the results like this:

Db1 2501 3722
Db2 1488 1754

I know it's fairly easy, just dont remember how I did this a few years ago.

Thanks ver much.
John

 

[JohnBates]

Can anyone help?

 

[bjb123]

I do something like that for some management reports. We are on sql 2000, which doesn't have native pivot table capabilities like sql 2005, so I just select the data as is and put it in excel and then use Excel's Pivot Table function.

 

[JohnBates]

Thanks bjb123. I have Excel 2003 anyes it is SQL Server 2005.

I will look into how to use Excel's pivot table feature.

John

 

[Joulius]

Hi!

Maybe this will help:

CREATE TABLE #TEST (DataBaseName varchar(55), Date smalldatetime, Qty int)
INSERT INTO #TEST
SELECT 'Db1','3/1/2009 00:00:00',2501 UNION ALL
SELECT 'Db1','4/1/2009 00:00:00',3722 UNION ALL
SELECT 'Db2','3/1/2009 00:00:00',1488 UNION ALL
SELECT 'Db2','4/1/2009 00:00:00',1754

SELECT * FROM #TEST

SELECT DISTINCT Date as D
INTO #DT
FROM #TEST

DECLARE @sql nvarchar(4000)
SET @sql = 'SELECT DataBaseName,'

SELECT	@sql = @sql + 'MAX(CASE WHEN Date = ''' + CAST(D as varchar) + ''' THEN Qty ELSE NULL END) as [Qty ' + CONVERT(varchar, D, 101) + '],'
FROM #DT

SELECT @sql = SUBSTRING(@sql, 1, LEN(@sql) - 1) + ' FROM #TEST GROUP BY DataBaseName'

EXEC sp_executesql @sql

DROP TABLE #DT
DROP TABLE #TEST

The obvious limitation is the size of the @sql variable, but you can remove/set a smaller alias for the quantity columns.

 

[simian101]

Here is a great site that I used to do a similar project. It works fast for thousands of records. I uses the xml clause.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

Also here is a little function I wrote to something similar that works best with fewer records. I passid and ID number (in your case a dbname) and it builds the list.


ALTER FUNCTION [dbo].[Return_PhoneNums_by_actbrch](
@lcAccId varchar(200)
)
RETURNS varchar(8000)
AS
-- Written by :MJ
-- returns all the phone numbers when passed an actbrch.
--
BEGIN
DECLARE @MyPhones as varchar(8000)
set @MyPhones=''
SELECT @MyPhones = ISNULL(@MyPhones+' ','')+svcid
FROM yad_srvmast
WHERE yad_srvmast.actbrch = @lcAccId
RETURN @MyPhones
END

Good Luck

Simi

 

[bjb123]

Since you plan on using excel anyway, I think using the pivot table there will be the most simple way.

If you want to use the SQL 2005 pivot capabilities it would look something like this:

using #TEST temp table from above

CREATE TABLE #TEST (DataBaseName varchar(55), Date smalldatetime, Qty int)
INSERT INTO #TEST
SELECT 'Db1','3/1/2009 00:00:00',2501 UNION ALL
SELECT 'Db1','4/1/2009 00:00:00',3722 UNION ALL
SELECT 'Db2','3/1/2009 00:00:00',1488 UNION ALL
SELECT 'Db2','4/1/2009 00:00:00',1754


Select *
FROM (SELECT DatabaseName, Date, QTY From #TEST) a
PIVOT (Sum(Qty) For Date in ([03/01/2009], [04/01/2009])) b

DatabaseName 03/01/2009 04/01/2009
Db1 2501 3722
Db2 1488 1754




With this method you will need to fill in any additional dates in the PIVOT (Sum(Qty) For Date in ([03/01/2009], [04/01/2009])) b section of the code

 

[Joulius]

bjb123 is right. I misread that sql2000 code is needed, so in case of sql2005 scenario PIVOT capabilities are the way to get the job done.
So, this should do the trick:

DECLARE @sql nvarchar(MAX)

SET @sql = 'SELECT * FROM (SELECT DatabaseName, Date, Qty From #TEST) a
PIVOT (Sum(Qty) FOR Date IN ('

SELECT @sql = @sql + '[' + CAST(D as varchar) + '],'
FROM #DT

SELECT @sql = SUBSTRING(@sql, 1, LEN(@sql) - 1) + ')) b'

EXEC sp_executesql @sql