Crosstab Query help required SQL 2000

Question

Crosstab Query help required SQL 2000

MaxJam

Valued Member

Points: 58
More actions
July 9, 2009 at 10:32 am

#202114

Hi all,
Ive a table which has the following columns
EmployeeID
CostCategory
Cost
you can have more than one record per employee
I Need to put it into a table in this format
EmployeeID
CostCategory1_Cost
CostCategory2_Cost
CostCategory3_Cost
No totalling or anything, i basically just need the same as before only with costs going across the table under different costcategory headings.
I assume its a crosstab query but i cant get it to work!
It has to be dynamic as cost categories are contained in a cost category table and can be added to.
any help is appreciated.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Melo Old Hand Points: 329 More actions · Answer 1

Hi,

Here's a way I developed to create dynamic SQL while maintaining some level of readability in the code. My example will create a test table and populate it with test data (two thing you should have provided by yourself, along with the real DDL) and will then generate and execute a script to switch your data. I've assumed that the table you describe is defining the Cost for an EmployeeId for a CostCategory, and therefore placed my primary key on (EmployeeId, CostCategory).

Please note, when I concatenate the strings of the dynamic part, I'm using a group by on every CostCategory present in the TestTable because you didn't provide any DDL for me to work with. It would probably work a lot better using the table with the definition of said CostCategory (much less overhead than working with a table that might be huge by comparison).

-- First, make sure the test table doesn't exist

IF OBJECT_ID('TempDB..#TestTable') IS NOT NULL

DROP TABLE #TestTable

-- Then, create your test table

CREATE TABLE #TestTable

(

EmployeeID int,

CostCategory int,

Cost money,

PRIMARY KEY(EmployeeID, CostCategory)

)

-- Populate the test table with some test data

INSERT #TestTable

(

EmployeeID,

CostCategory,

Cost

)

SELECT 1, 1, 10.55 UNION ALL

SELECT 1, 3, 84.96 UNION ALL

SELECT 2, 1, 28.34 UNION ALL

SELECT 3, 1, 43.37 UNION ALL

SELECT 4, 1, 117.45 UNION ALL

SELECT 5, 1, 18.12 UNION ALL

SELECT 5, 2, 10.46 UNION ALL

SELECT 5, 4, 14.77

-- Check the current state of the test data

SELECT * FROM #TestTable

-- Prepare the template for the dynamic part of the query

DECLARE @sql varchar(8000), @CostCategoryTemplate varchar(300), @CostCategories varchar(8000)

SELECT

@CostCategoryTemplate = ', Sum(CASE CostCategory WHEN |@CostCategoryId| THEN Cost ELSE 0 END) AS CostCategory|@CostCategoryId|_Cost',

@CostCategories = ''

-- Concatenate the strings of the dynamic part of the query

SELECT

@CostCategories = @CostCategories + Replace(@CostCategoryTemplate, '|@CostCategoryId|', CostCategory)

FROM

#TestTable

GROUP BY

CostCategory

-- Format the dynamic part into the static part of the query

SELECT @sql = Replace('SELECT EmployeeID|@CostCategories| FROM #TestTable GROUP BY EmployeeID', '|@CostCategories|', @CostCategories)

-- Print for Debug

PRINT @sql

-- Execute the query

EXEC (@SQL)

-- Finally, make sure you drop the test table

IF OBJECT_ID('TempDB..#TestTable') IS NOT NULL

DROP TABLE #TestTable

MaxJam Valued Member Points: 58 More actions · Answer 2

Thanks, this has been very helpful.

however, what is the meaning of the "|" character around some parameters?

Also these parameters havent been defined anywhere so how are they used?

thanks

Jonathan Melo Old Hand Points: 329 More actions · Answer 3

This is my own little system to make dynamic SQL more readable.

When you design the query, you put placeholders in the form of |@value| where some dynamic part will be inserted. When you've completed the creation of the dynamic part, you simply use Replace to put your dynamic string into the static part. It's not a variable per se, it's only really a placeholder, and I use the | just to make sure they'll stand out and never end up where I wouldn't want them to (suppose I make a more complex example where I'd have @Rocket and @Rockets, when I'll replace @Rocket, I'll affect @Rockets too, and I don't want that).

It's a lot easier to read (to me, at least) than doing it the old fashioned way, with concatenation and all, meaning the code gets cut in the middle to add your other string. When your query gets really long, it helps that it doesn't go switching from red to black all the time. Bonus points, every dynamic part of the query is grouped at the end, so it's easier to manage. For a quick example, compare these :

DECLARE @sql varchar(8000)

-- The way that makes code switch colors and concatenate everything

SELECT @sql = 'SELECT

MyCol1'+@MyDynamicCols+'

FROM

MyTable

INNER JOIN MyOtherTable

ON MyTable.MyCol1 = MyOtherTable.MyCol1

GROUP BY

'+@MyDynamicGroupBy+'

ORDER BY

MyCol1'

--The way I prefer, where color doesn't switch and inserted values are all grouped together at the end

SELECT @sql = Replace(Replace('SELECT

MyCol1|@MyDynamicCols|

FROM

MyTable

INNER JOIN MyOtherTable

ON MyTable.MyCol1 = MyOtherTable.MyCol1

GROUP BY

|@MyDynamicGroupBy|

ORDER BY

MyCol1',

'|@MyDynamicCols|', @MyDynamicCols),

'|@MyDynamicGroupBy|', @MyDynamicGroupBy)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

Jonathan Melo (7/9/2009)
Hi,
Here's a way I developed to create dynamic SQL while maintaining some level of readability in the code.

Looks real familiar... especially the part about dynamic and static sections...

http://www.sqlservercentral.com/articles/cross+tab/65048/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)