another un-crosstab - 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 2000 Forums

SQL Server Development (2000)

another un-crosstab

Author

Topic

wu88
Starting Member

2 Posts

Posted - 2002-08-14 : 12:34:00

I saw the post about a "reverse pivot table" and have adapted that code into my query. however, I wonder if there is a more efficient way for me to do it....

my data set looks like:
Date Name 100 200 300.....all the way to 2400 (as in hours)
--------------------------------------------------------------------
20020814 Test 1.12 2.25 3.25....

i need it to look like:

Date Name Hour Price
--------------------------------------------------------------
20020814 Test 1 1.12
20020814 Test 2 2.25
20020814 Test 3 3.25
...

my SQL looks like:

SELECT [Date], [Name], 1 as Hour, [100] AS Price FROM [TABLE]
WHERE Date = 20000701 and Name = 'Test'
UNION
SELECT [Date], [Name], 2 as Hour, [200] AS Price FROM [TABLE]
WHERE [Date] = 20000701 and [Name] = 'Test'
UNION
SELECT [Date], [Name], 3 as Hour, [300] AS Price FROM [TABLE]
WHERE [Date] = 20000701 and [Name] = 'Test'

....all the way to 24

is this the only/most efficient way to do this? it works, just slowly. there is a lot of data to sort through, but that can't be helped. any suggestions would be appreciated - thanks.

-Nick

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-08-14 : 13:02:34

You can try UNION ALL instead of UNION, that will eliminate the need for the query to eliminate dupes (which won't be generated anyway)

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-08-14 : 13:15:55

Shame the optimizer can't spot the literals are all different. Can't have everything I suppose...

You should find that the same plan gets generated irrespective of where you put the WHERE conditions. This sort of thing is usually a possibility -- and less irritating to generate:


SELECT [Date], [Name], Hour, Price
FROM (
    SELECT [Date], [Name], 1 as Hour, [100] AS Price FROM [TABLE] 
    UNION ALL
    SELECT [Date], [Name], 2 as Hour, [200] AS Price FROM [TABLE] 
    UNION ALL
    SELECT [Date], [Name], 3 as Hour, [300] AS Price FROM [TABLE] 
    etc...
  ) AS A
WHERE Date = '20000701' and Name = 'Test'

A radically different way to do it is like this:


SELECT [Date], [Name], h AS Hour,
  CASE h
    WHEN 1 THEN [100]
    WHEN 2 THEN [200]
    WHEN 3 THEN [300]
    WHEN 4 THEN [400]
    WHEN 5 THEN [500]
    WHEN 6 THEN [600]
    WHEN 7 THEN [700]
    WHEN 8 THEN [800]
    WHEN 9 THEN [900]
    WHEN 10 THEN [1000]
    WHEN 11 THEN [1100]
    WHEN 12 THEN [1200]
    WHEN 13 THEN [1300]
    WHEN 14 THEN [1400]
    WHEN 15 THEN [1500]
    WHEN 16 THEN [1600]
    WHEN 17 THEN [1700]
    WHEN 18 THEN [1800]
    WHEN 19 THEN [1900]
    WHEN 20 THEN [2000]
    WHEN 21 THEN [2100]
    WHEN 22 THEN [2200]
    WHEN 23 THEN [2300]
    WHEN 24 THEN [2400]
  END AS Price
FROM [TABLE]
CROSS JOIN (
    SELECT 1 AS h UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
    UNION SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
    UNION SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
    UNION SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16
    UNION SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20
    UNION SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24
  ) AS Hours
WHERE Date = '20000701' and Name = 'Test'

Which means there should be one scan / seek of [TABLE] instead of 24 -- not that this means it will necessarily run faster!

Edited by - Arnold Fribble on 08/14/2002 13:17:12

Edited by - Arnold Fribble on 08/14/2002 13:27:27

wu88
Starting Member

2 Posts

Posted - 2002-08-14 : 13:32:27

thanks guys...i appreciate you lending your expertise.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources