Crosstab in SQL Server

[mattpont]

Hi everybody,

I need some SQL code, and I cannot think how I would go about it, or even if it is even possible.

I shall explain what I would like to do...

I have a table with a structure like this:
tbl_products:-
- productID
- productName
- productPrice
- productWidth

Now in this table I have many products with the same name, but with different prices and widths.

i.e.
NAME | PRICE | WIDTH
test1 | 20 | 5
test1 | 30 | 10
test1 | 40 | 15
test2 | 10 | 4
test2 | 20 | 8
test2 | 30 | 10

I would like to produce a results table like this:
NAME | PRICE1 | PRICE2 | PRICE3
test1 | 20 | 30 | 40
test2 | 10 | 20 | 10

I used crosstabbing to achieve this in Access, but now I need to do the same thing in SQL Server (which doesn't seem to support cross tabbing or pivot tables).

Is this possible?
Thanks in advance for any help...

 

[hmscott]

There are some 3rd Party tools that will do this (

http://www.rac4sql.com

and another that I'm having difficulty remembering right now), but there is no native T-SQL to create the type of recordset you want.

The CASE statement may help you if you have a defined number of headers (columns), or you can do the work in whatever programming language your front-end is written in.

Regards,

hmscott

 

[DawleyWeb]

This article is a little old but does reference the RAC tool mentioned in hmscott's post and provide additional informatio you may find useful to solve your delima.

http://www.fawcette.com/vsm/2002_04/online/online_eprods/c_rjennings_4_29/