Hash temp table - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Hash temp table

Author

Topic

ms
Starting Member

11 Posts

Posted - 2008-10-20 : 05:40:17

Could someone please help me with this. I'm currently using hash temp table to do some select and doing some computation with it. Is there any way i could transfer this data into a view so that I can do pivot reports in excel. I'm trying to see if i can avoid using a db table for the same.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 05:42:37

you can. just use population query for your hash table as the view definition. for more details post query used for populating hash table.

ms
Starting Member

11 Posts

Posted - 2008-10-20 : 06:19:10

Here's the code

DROP TABLE #temp1

SELECT [Code1] as [Prov],
[Code2] as [Spec],
Prov=' ',
Spec=' ',
COUNT(CASE month([Start Date]) WHEN 4 THEN [NN_NO] ELSE NULL END) AS April,
COUNT(CASE month([Start Date]) WHEN 5 THEN [NN_NO] ELSE NULL END) AS May,
COUNT(CASE month([Start Date]) WHEN 6 THEN [NN_NO] ELSE NULL END) AS Jun,
COUNT(CASE month([Start Date]) WHEN 7 THEN [NN_NO] ELSE NULL END) AS Jul,
COUNT(CASE month([Start Date]) WHEN 8 THEN [NN_NO] ELSE NULL END) AS Aug,
COUNT(CASE month([Start Date]) WHEN 9 THEN [NN_NO] ELSE NULL END) AS Sep,
COUNT(CASE month([Start Date]) WHEN 10 THEN [NN_NO] ELSE NULL END) AS Oct,
COUNT(CASE month([Start Date]) WHEN 11 THEN [NN_NO] ELSE NULL END) AS Nov,
COUNT(CASE month([Start Date]) WHEN 12 THEN [NN_NO] ELSE NULL END) AS Dec,
COUNT(CASE month([Start Date]) WHEN 1 THEN [NN_NO] ELSE NULL END) AS Jan,
COUNT(CASE month([Start Date]) WHEN 2 THEN [NN_NO] ELSE NULL END) AS Feb,
COUNT(CASE month([Start Date]) WHEN 3 THEN [NN_NO] ELSE NULL END) AS Mar,
COUNT(dbo.In_LIVE.[NN_NO]) AS Total,
To_NN=0,
INTO #temp1
FROM dbo.In_LIVE
WHERE (LEN(dbo.In_LIVE.[NN_NO]) <> 0)
GROUP BY [Prov],
[Spec]

UPDATE #temp1
set Prov= B.Tr
From #temp1 A, Prov B
Where A.[Code1] = B.ID

UPDATE #temp1
Set Spec= B.Sp
From #temp1 A, Sc B
Where A.[Code2] = B.Id

DECLARE @To_NN int
SET @To_NN = 0
UPDATE #temp1
Set @To_NN = To_NN= (SELECT count([NN_NO])
FROM dbo.In_LIVE B
WHERE B.[Code1] =#temp1.[Prov]
AND B.[Code2] =#temp1.[Spec])

SELECT * FROM #temp1
ORDER BY [Prov Code],[Spec Code])

THanks

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-10-20 : 06:58:49

You could do it as a function - you can query like the VIEW you asked for but usually run faster:
CREATE FUNCTION [dbo].[fnName] ()

RETURNS Table
AS

Return
(
SELECT [Code1] as [Prov],
[Code2] as [Spec],
Prov=' ',
Spec=' ',
COUNT(CASE month([Start Date]) WHEN 4 THEN [NN_NO] ELSE NULL END) AS April,
COUNT(CASE month([Start Date]) WHEN 5 THEN [NN_NO] ELSE NULL END) AS May,
COUNT(CASE month([Start Date]) WHEN 6 THEN [NN_NO] ELSE NULL END) AS Jun,
COUNT(CASE month([Start Date]) WHEN 7 THEN [NN_NO] ELSE NULL END) AS Jul,
COUNT(CASE month([Start Date]) WHEN 8 THEN [NN_NO] ELSE NULL END) AS Aug,
COUNT(CASE month([Start Date]) WHEN 9 THEN [NN_NO] ELSE NULL END) AS Sep,
COUNT(CASE month([Start Date]) WHEN 10 THEN [NN_NO] ELSE NULL END) AS Oct,
COUNT(CASE month([Start Date]) WHEN 11 THEN [NN_NO] ELSE NULL END) AS Nov,
COUNT(CASE month([Start Date]) WHEN 12 THEN [NN_NO] ELSE NULL END) AS Dec,
COUNT(CASE month([Start Date]) WHEN 1 THEN [NN_NO] ELSE NULL END) AS Jan,
COUNT(CASE month([Start Date]) WHEN 2 THEN [NN_NO] ELSE NULL END) AS Feb,
COUNT(CASE month([Start Date]) WHEN 3 THEN [NN_NO] ELSE NULL END) AS Mar,
COUNT(dbo.In_LIVE.[NN_NO]) AS Total,
To_NN=0

FROM dbo.In_LIVE
WHERE (LEN(dbo.In_LIVE.[NN_NO]) <> 0)
GROUP BY [Prov],
[Spec]
)

The updates of temp table - use CASE statements instead.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources