Need Help with Crosstab Pivot Table Query
-
I have a "Rate" table that looks like:
Rate
From_City To_City Cost
Los Angeles Los Angeles 5
Los Angeles Miami 50
Los Angeles New York 40
Los Angeles Las Vegas 30
Miami Miami 5
Miami Los Angeles 50
Miami New York 35
Miami Las Vegas 45
New York New York 5
New York Los Angeles 40
New York Miami 35
New York Las Vegas 33
etc..
This table has duplicate records because there are 2 records for the the cost between two cities. Note, there is also a cost when the two city are the same. Someone else maintains this table and this is how it was setup. Whenever they enter a new cost for a new city, they have to add a whole lot of records to this table. They also maintain an Excel spreadsheet that is easier to read.
I need to produce a query that looks the Excel file:
City Los Angeles Miami New York Las Vegas
Los Angeles 5 50 40 30
Miami 50 5 35 45
New York 40 35 5 33
Las Vegas etc..
I'm having a real hard time producing this kind of crosstab/pivot table. If possible, I need the T-SQL to work when they enter a new city as well.
Does anyone know how to write this T-SQL query? Is it even possible? Thanks so much for any input/suggestions.
-
The table is named "Rate" with 3 fields:
1) From_City varchar(50)
2) To_City varchar(50)
3) Cost int
It's a simple table, but is giving me a very big headache...
If you need more info, just let me know.
-
That query returns the same thing as:
SELECT * FROM Rate
I've gotten it to resemble the Excel file using:
SELECT From_City,
SUM(CASE To_City WHEN 'Los Angeles' THEN Cost ELSE 0 END) AS 'Los Angeles',
SUM(CASE To_City WHEN 'Miami' THEN Cost ELSE 0 END) AS 'Miami',
SUM(CASE To_City WHEN 'New York' THEN Cost ELSE 0 END) AS 'New York',
etc..
FROM RATE
GROUP BY From_City
However, it doesn't work when they remove a City or add a new City. Normally, I know the exact columns of these crosstab queries such as using the Month (Jan, Feb, etc..) or Quarters (Q1, Q2, etc..). However, this time, the number of columns (City) isn't fixed. This is the sticky problem I'm running into.
I'm tempted to write a trigger to e-mail myself when they add a new City or delete a City so that I can go and change the above SQL code whenever it happens...
-
Have you looked at this script to see the approach used?
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=594
Francis
-
Correct. I can hardcode the columns and it works just fine. I just need it to be more dynamic so that it'll also work when a City is added/deleted.
fhanlon's link to the "proCrossTabMulti" sp works like a charm. Thanks!
-
IF you need the query to respond to new city additions I think you're stuck with writing dynamic SQL. This involves either using a cursor or a temp table. I usually prefer the temp table way.
--First make a temp table to hold each city name and populate it:
Create Table #tmpCity (ID INT IDENTITY, City Varchar(255))
INSERT #tmpCity (city)
SELECT DISTINCT to_city city FROM RATE
--Then create your sql:
DECLARE
@ctr INT
, @max-2 INT
, @val Varchar(255)
, @sql Varchar(8000)
SET @ctr = 0
SET @sql = ''
SET @max-2 = (SELECT MAX(ID) FROM #tmpCity)
WHILE @ctr < @max-2
BEGIN
SET @ctr = @ctr + 1
SET @val = (SELECT city FROM #tmpCity WHERE ID = @ctr)
SET @sql = @sql + 'SUM(CASE to_city WHEN ''' + REPLACE(@val , '''', '''''')
+ ''' THEN cost ELSE 0 END) AS [' + @val + '] '
IF @ctr < @max-2
SET @sql = @sql + ', '
SET @sql = @sql + CHAR(10)
END
--Then add in the 'rest' of the SQL statement...
SET @sql = 'SELECT from_city,' + CHAR(10) + @sql + 'FROM RATE ' + CHAR(10) + 'GROUP BY from_city'
--Finally run it and clean up...
EXEC(@sql)
DROP TABLE #tmpCity
Note that if you ever get a city name that has an apostrophe in it the code would break if not for the REPLACE clause. If you ever get LOTS of cities the SQL can exceed 8000 characaters and it'll break. You can bypass that by writing a much more complex code that uses multiple @sql variables (@sqlA, @sqlB, @sqlC for example) and then concatenate them whe you run:
EXEC (@sqlA + @sqlB + @sqlC)
I've had to concatenate up to 8 of them at times. I've never figured out a way to dynamically declare the variables, though so always make enough of them !
Also, the CHAR(10) is just there to put in carraige returns. Not strictly necessary, but if you have to troubleshoot your SQL statement (you will!) it's a LOT easier if you've put them in.
By the way, I find this method to be MUCH easier and faster than using cursors.
-
Try using this simple crosstab SP :
Usage: Exec crosstab <Entire Select statement as you would write to get a group by on rate>, <the field to sum (rate in this case)> , <the field in your group by clause>, <the table name>
Happy scriping.
-- THE CROSSTAB
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
Create PROCEDURE crosstab
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100)
AS
DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' into tmpBOB FROM ' + @table + ' WHERE '
+ @pivot + ' Is Not Null')
SELECT @sql='', @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )
SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )
WHEN 0 THEN '' ELSE '''' END
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pivot'
SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' +
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN '
+ @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM ##pivot
DROP TABLE ##pivot
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tmpBOB]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Drop Table tmpBOB
SELECT @sql=left(@sql, len(@sql)-1)
--select @select=stuff
print @select
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')
print @select
EXEC (@select)
SET ANSI_WARNINGS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
- For whatever reason, my 'pivot' table records are quite big and it exceeded 8000 for @select argument. it truncated and failed to execute. Please help!
J827
================================================== ========
CREATE PROCEDURE crosstab
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100)
AS
DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE '
+ @pivot + ' Is Not Null')
SELECT @sql='', @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )
SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )
WHEN 0 THEN '' ELSE '''' END
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pivot'
SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' +
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN '
+ @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM ##pivot
DROP TABLE ##pivot
SELECT @sql=left(@sql, len(@sql)-1)
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')
EXEC (@select)
SET ANSI_WARNINGS ON
-
thanks GregLyon for the crosstab. I've been looking everywhere to try and understand crosstab. I found yours worked well and I was able to modify for my purpose.
Thanks again
Bruce
-
Happy to help, Bruce!
-
I have used this same crosstab procedure, and when I run it in Query Analyzer, it works great!!
However, when i try to call the procedure from my web page (I am using ColdFusion), I receive an error:
Incorrect syntax near the keyword ‘END’.
Any ideas what would be causing this error, and how to fix it??
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply