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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-12-20 : 09:23:09
|
| peter writes "HiI am having a problem with pivot tables.Maybe you can help me.I have a table that looks like this:ID UseCase FieldName Data ----------- ------------------- ------------------- ---------------1 create-Order Customer-Ref 333300000016 1 create-Order TotalAmount 350.00 1 create-Order TotalPositions 27 2 get-Offer TotalAmount 350.00 2 get-Offer Currency CHF etc...and I would to grab the table and change it to the following:for UseCase create-OrderCustomer-Ref TotalAmount TotalPositions --------------- ------------------- ------------------- 333300000016 350.00 27 for UseCase get-OfferTotalAmount Currency ------------------- ------------------- 350.00 CHF etc.My big problem is I need the column name dynamically generatedand not hardcoded.Any thoughts for a solution?Thanks a lot for your help.." |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2001-12-20 : 09:52:57
|
| See if this helps:http://www.sqlteam.com/item.asp?ItemID=2955Use Max() or Min() as a summary function, Sum, Count, Avg won't work on character values.Also search SQL Team for "cross tab" or "pivot table" for more information.Keep in mind that while the columns are dynamic, ALL pivot columns will appear for each row. If a column doesn't apply for a row (Currency or TotalPositions), a NULL will appear in that column. This won't exactly match the output you specified, and I don't know of way to provide it otherwise. |
 |
|
|
Nazim
A custom title
1408 Posts |
Posted - 2001-12-20 : 10:15:39
|
| Rob,i think your stored procedure is the standard for CrossTab query's. never found any other material near to it. have explored a lot for it.Did any1 found anything like thishttp://www.sqlteam.com/item.asp?ItemID=2955 or atleast half as good as this on Crosstab.I would like to get some more information on handling crosstab's.-------------------------"Graz's Baby is my Master:)" |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2001-12-20 : 10:49:33
|
| Actually, there is something that is really amazing:http://www.sqlteam.com/redir.asp?ItemID=2840I found out about it right after I wrote the article...the author emailed me. It's pretty extraordinary! I've got nothing to brag about, believe me! There are SO MANY features in this, you won't believe it!I don't know if he charges for it anymore, but there is a downloadable version. Check it out, you WILL NOT be disappointed! |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2001-12-22 : 00:38:26
|
They Still Charge for it  .i just downloaded it. will work on it. btw,Rob y dont u comeup with a second version of your crosstab including some more features. i tell it , its one of the biggest pullers for sqlteam.com-------------------------Graz's Baby is my Master:) |
|
|
|
|
|
|
|
|
Pivot table, Cross Table