Help with complex query

[aspnetguy]

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

[jesh]

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.

[aspnetguy]

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.

[andr3w]

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,