Converting table data into a look up matrix

Question

Converting table data into a look up matrix

DAve Broomhead

Old Hand

Points: 301
More actions
October 8, 2005 at 7:52 am

#91751

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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Farrell Keough SSCoach Points: 17414 More actions · Answer 1

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.

DAve Broomhead Old Hand Points: 301 More actions · Answer 2

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