October 8, 2005 at 7:52 am
HI
I'm developing a large price/stock control database. One function of the database is to produce a price list. I'm using MS Access to produce the pricelist, just by creating reports from ODBC data in the SQL database.
The problem i'm having is i need to produce a different type of report. So far the report are as follows:
Size Product Price
------------------------------------
25mm Skylight £18.00
37mm Skylight £18.00
I need to produce a report as follows :
25mm 37mm
-----------------------------
Skylight £18.00 £18.00
Basically a lookup matrix.
Im running out of ideas, can anyone help ? I've been using SQL server for about 18months, and have visual studio and office at my disposal.
Sorry if this is the wrong place this is my first post.
Thanks
DAve
October 8, 2005 at 3:48 pm
Here is a bulldozer approach. Hopefully, someone will come up with something more efficient... (I added some data for a check, but I do not know how many [Size] entries you may have and hence how big your #OutPut table may get...
CREATE TABLE #Products( [Size] varchar(5),
Product varchar(10),
Price varchar(10))
INSERT INTO #Products VALUES( '25mm', 'Skylight', '£18.00')
INSERT INTO #Products VALUES( '37mm', 'Skylight', '£18.00')
INSERT INTO #Products VALUES( '25mm', 'MoonLight', '£13.00')
INSERT INTO #Products VALUES( '37mm', 'MoonLight', '£15.00')
CREATE TABLE #OutPut( Product varchar(10))
DECLARE @SQL varchar(2000)
SELECT @SQL = ( 'INSERT INTO #OutPut ' + CHAR(10) +
'SELECT DISTINCT Product ' + CHAR(10) +
'FROM #Products ')
EXEC( @SQL)
DECLARE @CurrentProduct varchar(10),
@MaxProduct varchar(10),
@CurrentSize varchar(5),
@MaxSize varchar(5)
SELECT @CurrentProduct = (SELECT MIN( Product) FROM #OutPut)
SELECT @MaxProduct = (SELECT MAX( Product) FROM #OutPut)
SELECT @CurrentSize = (SELECT MIN( [Size]) FROM #Products WHERE Product = @CurrentProduct)
SELECT @MaxSize = (SELECT MAX( [Size]) FROM #Products WHERE Product = @CurrentProduct)
WHILE @CurrentProduct <= @MaxProduct
BEGIN
WHILE @CurrentSize <= @MaxSize
BEGIN
SELECT @SQL = ('ALTER TABLE #OutPut ADD [Size ' + @CurrentSize + '] varchar(10) NULL ') + CHAR(10)
EXEC( @SQL)
--PRINT @SQL
SELECT @SQL = ('UPDATE #OutPut SET ' + CHAR(10) +
' [Size ' + @CurrentSize + '] = P.Price ' + CHAR(10) +
'FROM #Products P ' + CHAR(10) +
' INNER JOIN #OutPut O ON( P.Product = O.Product)' + CHAR(10) +
'WHERE ' + CHAR(39) + @CurrentSize + CHAR(39) + ' = P.[Size]') + CHAR(10)
EXEC( @SQL)
--PRINT @SQL
SELECT @CurrentSize = (SELECT MIN( [Size]) FROM #Products WHERE @CurrentProduct = Product AND [Size] > @CurrentSize)
END
SELECT @CurrentProduct = (SELECT MIN( Product) FROM #OutPut WHERE Product > @CurrentProduct)
SELECT @CurrentSize = (SELECT MIN( [Size]) FROM #Products WHERE @CurrentProduct = Product AND @CurrentSize > [Size])
END
SELECT * FROM #OutPut
DROP TABLE #Products
DROP TABLE #OutPut
I wasn't born stupid - I had to study.
October 10, 2005 at 1:42 am
HI,
Thanks a million this looks like the key, I'll give it a bash and if i'm still having probs i'll come back with more details on the tables etc.
Thanks again
Dave
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply