Using Temp Tables to turn Rows into Columns

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

Transact-SQL (2000)

Using Temp Tables to turn Rows into Columns

Author

Topic

Yutzmann
Starting Member

10 Posts

Posted - 2002-12-05 : 18:01:03

Wondering if I can get some help from the SQL experts out there.

I have a very nice query that aggregates a number of fields and across a few tables. It works very well, but unfortunately for the application I want to use the result set in, I need to turn the repeating rows into their own columns and pop the aggregate data into the proper places.

An example would be customers and products orders. Each customer needs to be a column. All the possible products will also be a column (a kind of select distinct). And for each customer/product pair (like a cartesian product) I would have the number of orders for these products.

So instead of


Company  Product  NumberSold
AAA      WidgetA  10
AAA      WidgetB  2
AAA      WidgetC  4
BTOB     WidgetA  1
BTOB     WidgetC  12

(note BTOB have not ordered any WidgetB and that's OK)

I want to see:


Product  AAA       BTOB
WidgetA  10        1
WidgetB  2         0
WidgetC  4         12

With as many columns as there are distinct companies.

I think it can be done with Temp tables and sort of schema creation on the fly but I don't know how to use these very easily. I have consulted BOL but haven't found anything that specifically addresses what I need to accomplish.

Any help is greatly appreciated.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-12-05 : 18:05:32

Cross-tabs to the rescue!

http://www.sqlteam.com/SearchResults.asp?SearchTerms=cross+tab

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-06 : 10:12:35

I'd say that if you use a dynamic SQL solution, but the # of companies in your database is constant, generate the dynamic SQL once but then save it as a regular view or stored proc.

Rob's 'crosstab' stored proc is a really neat idea, I hadn't seen that article (it's the first one returned from the search link).

But for a quick solution -- and one that will teach you how to do cross-tab type queries in general yourself -- the idea is you GROUP BY your product, and then use SUM(CASE..) commands to add up just the data you want:

SELECT Product,
SUM(CASE WHEN Company = 'AAA' THEN NumberSold ELSE 0 END) as 'AAA',
SUM(CASE WHEN Company = 'BTOB' THEN NumberSold ELSE 0 END) as 'BTOB",
..etc ...
FROM
YourTable
GROUP BY Product

Take a look at the above carefully and you'll see how it works; if you're still not sure, take away the GROUP BY and the SUM's and look at how the data is "spread out" to all the different columns.

With the above, you just would need to know exactly which companies you are dealing with. If it is completely dynamic, use dynamic sql or the stored proc from that first article.

- Jeff

Edited by - jsmith8858 on 12/06/2002 10:13:22

Yutzmann
Starting Member

10 Posts

Posted - 2002-12-06 : 17:59:59

Your help was right-on. I implemented in just over an hour after tweaking a few things. Couple comments on the sproc idea.

When the total is 0 I get zero but if a product was never associated to a customer (they have expressed no interest in it), I get a null. Normally, that would be fine, but I'd like to replace the NULL with a zero. How do I do that?

Second thing I'd like is to remove certain columns (customers) from the aggregation completely. I can do this to the @select parameter by adding a join and a criteria. When I run this query directly, I see that the customer is removed from the aggregate totals. But when I use this query as the @select parameter, I still see these customers. Any way to NOT generate a column that is a customer?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-12-07 : 09:32:38

In the comments section of the cross-tab article:

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

There is a variation that allows you to pass a WHERE clause for the pivot columns.

As far as returning a zero for null aggregates, you can wrap the CASE expressions inside a IsNull call:

replace:
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

with:
SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = IsNull(' +
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN '
+ @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', 0), ' FROM ##pivot

Edited by - robvolk on 12/07/2002 11:01:45

cez
Starting Member

37 Posts

Posted - 2002-12-07 : 10:02:32

You can create your temp table dynamically :

-- create the table to keep the properties as text values
create table #prp(id int NOT NULL, name varchar(100) NULL)
declare cur cursor for
select names of your columns
open cur
fetch cur into @name1, ..
while @@fetch_status = 0
begin
exec("alter table #prp add [" + @name1 +"] varchar(250) NULL")
fetch cur into @name1, ..
end
close cur
deallocate cur

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-12-07 : 10:26:49

The stored procedure already does that part without needing a temp table or a cursor. Altering the temp table dynamically is great for the structure, but it doesn't do anything to populate the data...you'd still need a cross-tabbed SELECT statement to do that.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-07 : 10:52:54

rob -- did you mean ISNULL instead of NULLIF?

- Jeff

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-12-07 : 11:02:29

Yep, thanks, I corrected it.

Must. Have. COFFEE.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources