SQL Query to read two prces from table.

[Jouster]

I've been trying to figure this out all morning with little results. Maybe someone here can help me.

Have a table (IPrices) like so:

Item Code, Price List , Price
10001 1 100.00
10001 2 200.00

I need an output to show both prices on one row like:

ItemCode , Price1, Price2
10001 100.00 200.00

Any help is appreciated,

Rick

 

[simian336]

Are there always only 2 items or can there be several?

Simi

 

[Jouster]

I am only looking at 2 price lists at the moment, but there are like 15 price lists available to use in this software.

Thanks,
Rick

 

[Peager]

I've done something like this using a Pivot Table and some dynamic SQL. This code is probably overkill but I am pressed for time right now so I offer it if it can help....

-- =============================================
-- Author:	Paul E Richmond
-- Create date: April 4, 2008
-- Description:	Produces a pivot price table based on length
-- EXEC SS_PivotPriceList 78
-- =============================================
ALTER PROCEDURE [dbo].[SS_PivotPriceList]
	@PriceList Integer
AS
BEGIN
    SET NOCOUNT ON;

    -- We have to instantiate the price list to run dynamic SQL against it.
    CREATE TABLE #PrcTbl(Product CHAR(8), nLen SMALLINT, Price DECIMAL(6,2));
    DECLARE @Lengths VARCHAR(MAX),
	    @SQL	 NVARCHAR(MAX)

    -- This variable is used when working on the procedure as straight SQL
--    DECLARE @PriceList	 INT;
--    SET @PriceList = 78;

    -- This creates a variable holding all the lengths used in the price history file
    SET @Lengths = STUFF(
	(SELECT ',' + QUOTENAME(L) --AS [TEXT('')]
	 FROM (SELECT DISTINCT CONVERT(INT,I.Nom_Length) As L 
	       FROM Prc_Hist H JOIN ItemData I
		    ON I.Item_No = H.Item_No
	       WHERE H.Prc_List_ID = @PriceList) As L
	 ORDER BY L  
	 FOR XML PATH('')),1,1,'');
    --Select @Lengths	;

    -- We create two in-line tables in memory....
    --  ... The first one holds the most recent price 
    --      (This is where you would constrain to a passed in date)
    WITH LastPrices AS 
	(
	    SELECT DISTINCT H.Item_No, MAX(H.Effective_Date) AS Effective_Date
	    FROM Prc_Hist H
	    WHERE H.PRC_List_ID = @PriceList
	    GROUP BY H.Item_No
	 ),   
	-- This InLine CTE table holds the Item Number and the Price from the prior table.
    -- It is also constrained to leave out any prices that are suppressed on the price list.
	ItemPrices AS 
	(   
	    SELECT L.Item_No, H.Item_Price
	    FROM LastPrices L JOIN Prc_Hist H
				    ON H.Prc_List_ID    = @PriceList AND
				       H.Item_No	= L.Item_No AND
				       H.Effective_Date = L.Effective_Date 
			      JOIN Prc_Item I 
				    ON I.Item_No = L.Item_No 
			      JOIN PRC_Dtl D
				    On I.Prc_Dtl_Id = D.Prc_Dtl_Id
			      JOIN Prc_Grp G
				    On D.Prc_Grp_ID = G.Prc_Grp_ID
	    WHERE D.Prc_List_ID = @PriceList AND
		  D.Suppress_Print <> 'Y' AND
		  G.Suppress_Print <> 'Y'
	)
    -- OK now we stuff the results of the second table above into the temporary disk based table.
    INSERT INTO #PrcTbl
    SELECT I.Product, I.Nom_Length, IP.Item_Price
    FROM ItemPrices IP Join ItemData I
		      ON I.Item_No = IP.Item_No ;
    -- Then we build the dynamic SQL script that will perform the pivot table.  @Lengths holds the 
    -- dynamic part of the script used to determine the columns.
    SET @SQL = 'SELECT * 
		 FROM (SELECT  Product, nLen, Price
		       FROM #PrcTbl) AS PT
		 PIVOT(Avg(Price) FOR nLen IN (' + @Lengths + ')) As P';
    -- This executes the dynamic script above.
    EXEC sp_Executesql @SQL;
    -- And then we drop our temporary table.
    DROP TABLE #PrcTbl;
END
[code/]