aspnetguy Posted December 4, 2006 Share Posted December 4, 2006 First off, I cannot change the structure of the tables or their fields.I have the following 4 tables and the below is the current query I am working with tvwr_ContractInvoices---------------------------InvoiceNumber INTContractNumber INTtblContractDetail---------------------------ContractNumber INTItemID VARCHARYearlyPrice FLOATtblPriceLevels---------------------------PriceLevelsKeyID INTDescription VARCHARtblPriceLevelOverride---------------------------PriceLevelOverrideKeyID INTFkPriceLevels INTFlatPrice FLOAT SELECT InvoiceNumber, c.ContractNumber, c.ItemID, YearlyPrice, FkPriceLevels, Description, FlatPriceFROM tvwr_ContractInvoices i, tblContractDetail c, tblPriceLevelOverride po, tblPriceLevels pWHERE i.ContractNumber = c.ContractNumber AND c.ItemID = po.ItemID AND po.FkPriceLevels = p.PriceLevelsKeyID AND i.InvoiceNumber = 31 GROUP BY InvoiceNumber, c.ContractNumber, c.ItemID, YearlyPrice, FkPriceLevels, Description, FlatPrice With a query like that I get results like: Inv# Con# ItemID Yr Price Price Level PL Desc Flat Price31 51 Item1 249.99 18 Price Level 18 219.9931 51 Item1 249.99 19 Price Level 19 199.9931 51 Item1 249.99 20 Price Level 20 189.9931 51 Item1 249.99 21 Price Level 21 159.99 But what I want is something like: Inv# Con# ItemID Yr Price PL 18 Price PL 19 Price PL 20 Price PL 21 Price31 51 Item1 249.99 219.99 199.99 189.99 159.99 Is this possible within the limitations I have to work? I am able to create views but I cannot modify any existing tables or views.Thanks Link to comment Share on other sites More sharing options...
jesh Posted December 5, 2006 Share Posted December 5, 2006 Any luck with this yet? The only thing I can think of is to use your first query to return multiple rows: Inv# Con# ItemID Yr Price Price Level PL Desc Flat Price31 51 Item1 249.99 18 Price Level 18 219.9931 51 Item1 249.99 19 Price Level 19 199.9931 51 Item1 249.99 20 Price Level 20 189.9931 51 Item1 249.99 21 Price Level 21 159.99 And then, if you are using .NET, generate a DataTable with the appropriate columns and then iterate through all of the records and populate your DataTable. DataTable rawData = new DataTable(); // this is the one holding your dataDataTable table = new DataTable();table.Columns.Add("Inv#", typeof(int));table.Columns.Add("Con#", typeof(int));table.Columns.Add("ItemID");table.Columns.Add("Yr Price", typeof(float));string rowname;foreach(DataRow row in rawData.Rows){ rowname = "PL " + row["Price Level"].toString() + " Price"; if(!table.Columns.Contains(rowname)) { table.Columns.Add(rowname, typeof(float)); }} Once you have all the columns, you can populate the DataTable with your data.A bit more overhead than having a single query, but it'd work. I'd be interested if you find out how to write the query. Link to comment Share on other sites More sharing options...
aspnetguy Posted December 6, 2006 Author Share Posted December 6, 2006 I did figure it out. The hard part was I needed to do this in 1 sql statement, with no code. It is for Crystal Reports to select the data.I can't post the code because it was written for the company I work for but it was a combo of a temporary table witht eh structure I wanted, I used a select statement to populate most of the temp table then used a Cursor to update the parts from the multiple rows they were in. Sorry if that makes no sense. Link to comment Share on other sites More sharing options...
andr3w Posted December 15, 2006 Share Posted December 15, 2006 You can use CASE, together with MAX to get what you need in one shot.For each Price Level the case statement gives NULL when the price level does not match, these get ignored by the MAX. SELECT InvoiceNumber, c.ContractNumber, c.ItemID, YearlyPrice, MAX(CASE WHEN fkPriceLevel=18 THEN FlatPrice END) AS PriceAt18, MAX(CASE WHEN fkPriceLevel=19 THEN FlatPrice END) AS PriceAt19FROM tvwr_ContractInvoices i, tblContractDetail c, tblPriceLevelOverride po, tblPriceLevels pWHERE i.ContractNumber = c.ContractNumber AND c.ItemID = po.ItemID AND po.FkPriceLevels = p.PriceLevelsKeyID AND i.InvoiceNumber = 31 GROUP BY InvoiceNumber, c.ContractNumber, c.ItemID, YearlyPrice, Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now