Db Logic - Cant put it back together

Hi there!

Hope somebody got some better insight into this problem than i have. I'm struggling with some db logic.

Overview:

I have a client who is a publisher. They would like to post all their advert-size specs for each magazine to their web site. My first thought was that ill be able to build one table and populate it with the information for each magazine. I was wrong. Each magazine comes in a different size. Thus each magazine will have different advert-sizes as well.

Layout for a magazine will look something like this:

Size Trim Type Bleed

Full Page 280x440 270x430 290x450

Half Page 140x220 130x210 150x230

etc...

Some mags will not have values for Half pages since they dont print half pages and others will not have specs for Bleed.

Because of this - as an easy way out I created a table per magazine. It works but i dont think its very smart.

Break it down!

Ok so what im trying as a solution is to have three tables. The 1st table will hold the magID and Size values (e.g Half Page). The 2nd table will also have the magID and the Trim, Type, Bleed info. The 3rd table holds magID, sizeID, specID and the actual value (140x220).

I thought that this would be better because within these three tables i can store the information for each magazine regardless of their differences. Brilliant!

The Problem.

The problem comes when i have to put it all back together again. I need to represent this data in a table so i can bind it to a datagrid. I have NO idea how to do this. What i THINK i need to have is some temp table created on the fly. The row names for this temp table will come from the 1st table. The column names will come from the 2nd table and the values for each field will come from the 3rd table bound by foreign keys.

I've somewhat managed to do this with INNER JOINS. But it doesn't give the desired result. I need to set row and column NAMES using tables 1 & 2 then populate the columns with table 3, then bind to a datagrid.

Any ideas on how i could manage this?

If you made it tis far through my question then thanks anyways! I hope you can help me out!

Ta

You'll need to use SQL Servers new PIVOT function. Assuming your tables are set up as such:

MagSize: ID, Size
MagType: ID, TypeName
MagAttribute: SizeID, TypeID, Value

You generate your desired results with:

WITH TempTable
AS (
SELECT Size, TypeName, Value FROM
MagSize s
INNER JOIN MagAttribute v ON s.ID = v.SizeID
INNER JOIN MagType t ON t.ID = v.TypeID
)
SELECT * FROM TempTable
PIVOT (
MAX(VALUE)
FOR TypeName IN ([Trim],[Type],[Bleed])
) AS PVT

MSDN Article|||

Hi northside!

Thanks for your reply!

Ok seems like im gonna be stuck since i only have access to a SQL 2000 server.

I was just wondering from your query where you used the Trim, Type, Bleed values in your second last line: Because i dont know if every magazine will have three attributes (some might only have Trim and Bleed) Is there some way i can 1) replace Trim, Type, Bleed with variables and 2) use some array maybe to generate the quantity of attributes?

See I recon I can do this if I can create DDL based on the results created by the SELECT query. But my logic tells me ill need to store results in an array and i dunno if t-sql caters for that. So far i couldn't find anything.

Thanks once again!

|||

You can still do pivot queries in SQL Server 2000, it's just a bit more convoluted. The query below does the equivalent to the above:

SELECT s.Size,

MAX(CASE WHEN TypeName = 'Trim' THEN Value END) Trim,

MAX(CASE WHEN TypeName = 'Type' THEN Value END) Type,

MAX(CASE WHEN TypeName = 'Bleed' THEN Value END) Bleed

FROM

MagSize s

INNER JOIN MagAttribute v ON s.ID = v.SizeID

INNER JOIN MagType t ON t.ID = v.TypeID

GROUP BY Size

Unfortunately, neither the above query and SQL Server 2005s pivot function are dynamic - you must know in advance what columns you want to pivot. If you want a more dynamic report, you have to write an ad-hoc query. Something like the one below should hopefully get you started:

CREATE PROCEDURE [dbo].[MagazinePivot]

@.ID int

AS

BEGIN

SET NOCOUNT ON;

DECLARE @.pivot VARCHAR(2000)

SELECT @.pivot = COALESCE(@.pivot + ', ', '') + 'MAX(CASE WHEN TypeName = '''+ TypeName + ''' THEN Value END) ' + TypeName

FROM MagType WHERE ID IN (

SELECT TypeID FROM MagAttribute WHERE SizeID = @.ID

)

DECLARE @.sql VARCHAR(2000)

SET @.sql = '

SELECT s.Size, ' + @.pivot + '

FROM

MagSize s

INNER JOIN MagAttribute v ON s.ID = v.SizeID

INNER JOIN MagType t ON t.ID = v.TypeID

GROUP BY Size'

EXEC (@.sql)

END

|||

northside!

Dude quality post!

Gonna take me a while to work through this one though.

Thanks bud