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 |
|
mufasa
Yak Posting Veteran
62 Posts |
 Posted - 2003-12-05 : 10:11:55
|
| Is there an easy way to create cross tabs in T-SQL or ViewerI have a style file where I need to put sizes in columnsEach record now is a size, color and styleI have 22 size groups and average 6 sizes for groupI would have to create apx 132 coulmns x2 as I need one for sales and one for stock.The way I presently do it now, is as followsSELECT Month,Week,CLASS,STYLE,COLOR,Season, SUM(CASE SizeDesc WHEN 'XS' THEN QTY ELSE 0 END) AS 'XS', SUM(CASE SizeDesc WHEN 'S' THEN QTY ELSE 0 END) AS 'S', SUM(CASE SizeDesc WHEN 'M' THEN QTY ELSE 0 END) AS 'M', SUM(CASE SizeDesc WHEN 'L' THEN QTY ELSE 0 END) AS 'L', SUM(CASE SizeDesc WHEN 'XL' THEN QTY ELSE 0 END) AS 'XL', SUM(CASE SizeDesc WHEN 'XXL' THEN QTY ELSE 0 END) AS 'XXL', SUM(CASE SizeDesc WHEN 'XS' THEN OH ELSE 0 END) AS 'XSS', SUM(CASE SizeDesc WHEN 'S' THEN OH ELSE 0 END) AS 'SS', SUM(CASE SizeDesc WHEN 'M' THEN OH ELSE 0 END) AS 'MS', SUM(CASE SizeDesc WHEN 'L' THEN OH ELSE 0 END) AS 'LS', SUM(CASE SizeDesc WHEN 'XL' THEN OH ELSE 0 END) AS 'XLS', SUM(CASE SizeDesc WHEN 'XXL' THEN OH ELSE 0 END) AS 'XXLS'into Size1nUSales FROM dbo.DetailSalesWhere CCTD.dbo.DetailSales.SizeGroup='U' or CCTD.dbo.DetailSales.SizeGroup='1'GROUP BY Month,Week,CLASS,STYLE,COLOR,SeasonThanks for any helpMufasa |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-12-05 : 13:50:50
|
| Question --Why does this have to be done in SQL Server? how are you displaying the results of this to the user? i.e, in what Application -- Excel, Crystal, ASP, etc.- Jeff |
 |
|
|
mufasa
Yak Posting Veteran
62 Posts |
Posted - 2003-12-05 : 14:31:20
|
I am doing it in Crystal Reports, but I find it hard to work with their cross tabbing.It is easy to do in Access, but I don't want to use to many programs for this project.The inventory column is where I had problems, as it can not be added due to the fact that it shows the same qty for each transaction in that size.Ex--------------small--------medium---------large-----------Inv---Sold----Inv---Sold------Inv--SoldWeek1------10----5-------0-----0--------10---2Week2-------0----0-------12----3--------10---6Week3------10----1-------0-----0---------0---0As you can see I can not add the Inv column.But this report is broken down by color and style,so I need to get the total inventory by style as well.My solution was to create two crosstab files, one using the inventory file and one using the sales file.I had to create 81 differnet columns to cover all the different sizes in each crosstabThen I linked the two files using style, color.I can now use this file in Crystal reports to achieve my results
|
|
|
|
TimChenAllen
Starting Member
45 Posts |
Posted - 2003-12-06 : 01:32:37
|
| This is actually a very interesting problem which should lend itself to a general solution.Mufasa, have you considered building the SQL statement you created originally using dynamic SQL? It looks like you could do this doing a "SELECT DISTINCT SizeDesc WHERE Month = ...." and then putting the results of that into a SQL string and executing that.I'm just afraid that the solution you came up with with the 81 columns will break when they come up with a new color!--Timothy Chen Allenemail me if you have a job in New Orleans for me[url]http://www.timallen.org[/url] |
|
|
|
mufasa
Yak Posting Veteran
62 Posts |
Posted - 2004-01-07 : 16:47:17
|
| Hi TimDid not realize I got another hit on this topic.I did do something similar to what you suggested, one very large tsql statement.I created temp tables, that stored some of the information, then created the tables that I needed.The columns will only grow if they add more sizes, which mamnagement has promised will not happen ;-{It is still going to be long in Crystal Reports, as I need calculations across columns.Well at least I'll be busy for a whileTake care |
|
|
|
|
|
|
|
|
Cross Tabs