Concatenate Rows into Columns

I have a table 2 columns (Object and Weight) with data like below:

Object Weight(lb)
table 5
Chair 6
Computer 3
Computer 5
TV 20
TV 15
Radio 10
Computer 10

Question: I would like to create a new table with one column that would join the above two columns like below

Object
table 5
Chair 6
Computer 3, 5, 10
TV 20, 15
Radio 10

Thanks for your help in advance.

SELECT t3.Object, MAX(case t3.seq when 1 then t3.Weight end)

+ MAX(case t3.seq when 2 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 3 then ', ' + t3.Weight else '' end) AS Weight

FROM ( SELECT Object, Weight, (SELECT COUNT(*) FROM mergeTable1$ AS t2 WHERE t2.Object = t1.Object and t2.Weight <= t1.Weight) AS seq

FROM mergeTable1$ AS t1

) as t3

GROUP BY t3.Object

ORDER BY t3.Object

|||

What if I do not know the Max number of Weights for each object. eg. an object may have n number of weights in the table.

Thanks

|||

I would guess a maximum possible number in this solution. For example:

SELECT t3.Object, MAX(case t3.seq when 1 then t3.Weight end)

+ MAX(case t3.seq when 2 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 3 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 4 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 5 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 6 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 7 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 8 then ', ' + t3.Weight else '' end) AS Weight

FROM ( SELECT Object, Weight, (SELECT COUNT(*) FROM mergeTable1$ AS t2 WHERE t2.Object = t1.Object and t2.Weight <= t1.Weight) AS seq

FROM mergeTable1$ AS t1

) as t3

GROUP BY t3.Object

ORDER BY t3.Object

By the way, are you using SQL Server 2005? There are other solutions to handle this one.

|||

Yes, I am using sql server 2005

Thanks

|||

In SQL 2005, you can try like this

Create table tbl(Object varchar(100), Weight int)

insert tbl

Select 'table', 5 union all

Select 'Chair', 6 union all

Select 'Computer', 3 union all

Select 'Computer', 5 union all

Select 'TV' , 20 union all

Select 'TV' , 15 union all

Select 'Radio' , 10 union all

Select 'Computer' ,10

with CTE (Object,Weight1) as

(

select Object,Weight1=cast(Weight as varchar(50)) from tbl

union all

select a.Object,Weight1=convert(varchar(24),Weight1)+','+convert(varchar(25),a.Weight)

from tbl a inner loop join CTE b

on a.Object=b.Object and patindex('%'+convert(varchar(50),a.Weight)+'%',Weight1)<1

)

select distinct Object, max(Weight1) from CTE

Group by Object

|||

--We can use CTE in SQL Server 2005. The recursive function will take care of the number of records for the same object in your table.

With MyCTE(Object, Weight, Weights, myNum) AS

(SELECT a.Object, CONVERT(varchar(50), MIN(a.Weight)) as col1, CONVERT(varchar(50),(a.Weight)) as Weights, 1 as myNum

FROM mergeTable1$ a GROUP BY a.Object, CONVERT(varchar(50),(a.Weight))

UNION ALL

SELECT b.Object, CONVERT(varchar(50), b.Weight), CONVERT(varchar(50), (c.Weights + ',' + b.Weight)), c.myNum+1 as myNum

FROM mergeTable1$ b INNER JOIN MyCTE c ON b.Object=c.Object

WHERE b.Weight>c.Weight

)

SELECT a.Object, Weights FROM MyCTE a INNER JOIN (SELECT Max(a1.myNum) as myNumMax, a1.Object FROM MyCTE a1

group by a1.Object) b on a.Object=b.Object AND a.myNum= b.myNumMax ORDER BY a.Object

|||

Yet another way to do it, using data from one of the other posts, and the 2005 techniques from:

http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html

Create table tbl(Object varchar(100), Weight int)

insert tbl

Select 'table', 5 union all

Select 'Chair', 6 union all

Select 'Computer', 3 union all

Select 'Computer', 5 union all

Select 'TV' , 20 union all

Select 'TV' , 15 union all

Select 'Radio' , 10 union all

Select 'Computer' ,10

SELECT
Object,
Weights = LEFT(o.list, LEN(o.list)-1)
FROM
(select distinct object from tbl) as tbl --if these values are defined in another related table it is more clear
CROSS APPLY
(
SELECT CONVERT(VARCHAR(12), Weight) + ',' AS [text()]
FROM tbl as t
WHERE t.object = tbl.object
ORDER BY weight
FOR XML PATH('')
) o (list)

|||If you don't mind, what does MYCTE stand for ?|||CTE (Common Table Expression) is a new function in SQL Server 2005. You can look it up from Book Online. MyCTE is a name like you would call a table mytable in your database. I like Louis's solution a lot. It uses another new CROSS APPLY funtion in SQL Server 2005 too.|||

For some ideas, see:

http://www.projectdmx.com/tsql/rowconcatenate.aspx

--

Anith

|||

i belive this can help

well use this function to get retrive the string that cotaians the rows of the specific ID.

CREATE FUNCTION dbo.ConRow(@.JID int)

RETURNS VARCHAR(8000)

AS

BEGIN

DECLARE @.Output VARCHAR(8000)

SELECT @.Output = COALESCE(@.Output+', ', '') + CONVERT(varchar(20), JP.a)

FROM [E_JobPending] JP

WHERE JP.JobID = @.JID

RETURN @.Output

END

select dbo.ConRow(jobid), vE_Job.*

from

vE_Job

DROP FUNCTION dbo.ConRow

Concatenate Rows into Columns

I have a table 2 columns (Object and Weight) with data like below:

Object Weight(lb)
table 5
Chair 6
Computer 3
Computer 5
TV 20
TV 15
Radio 10
Computer 10

Question: I would like to create a new table with one column that would join the above two columns like below

Object
table 5
Chair 6
Computer 3, 5, 10
TV 20, 15
Radio 10

Thanks for your help in advance.

SELECT t3.Object, MAX(case t3.seq when 1 then t3.Weight end)

+ MAX(case t3.seq when 2 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 3 then ', ' + t3.Weight else '' end) AS Weight

FROM ( SELECT Object, Weight, (SELECT COUNT(*) FROM mergeTable1$ AS t2 WHERE t2.Object = t1.Object and t2.Weight <= t1.Weight) AS seq

FROM mergeTable1$ AS t1

) as t3

GROUP BY t3.Object

ORDER BY t3.Object

|||

What if I do not know the Max number of Weights for each object. eg. an object may have n number of weights in the table.

Thanks

|||

I would guess a maximum possible number in this solution. For example:

SELECT t3.Object, MAX(case t3.seq when 1 then t3.Weight end)

+ MAX(case t3.seq when 2 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 3 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 4 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 5 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 6 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 7 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 8 then ', ' + t3.Weight else '' end) AS Weight

FROM ( SELECT Object, Weight, (SELECT COUNT(*) FROM mergeTable1$ AS t2 WHERE t2.Object = t1.Object and t2.Weight <= t1.Weight) AS seq

FROM mergeTable1$ AS t1

) as t3

GROUP BY t3.Object

ORDER BY t3.Object

By the way, are you using SQL Server 2005? There are other solutions to handle this one.

|||

Yes, I am using sql server 2005

Thanks

|||

In SQL 2005, you can try like this

Create table tbl(Object varchar(100), Weight int)

insert tbl

Select 'table', 5 union all

Select 'Chair', 6 union all

Select 'Computer', 3 union all

Select 'Computer', 5 union all

Select 'TV' , 20 union all

Select 'TV' , 15 union all

Select 'Radio' , 10 union all

Select 'Computer' ,10

with CTE (Object,Weight1) as

(

select Object,Weight1=cast(Weight as varchar(50)) from tbl

union all

select a.Object,Weight1=convert(varchar(24),Weight1)+','+convert(varchar(25),a.Weight)

from tbl a inner loop join CTE b

on a.Object=b.Object and patindex('%'+convert(varchar(50),a.Weight)+'%',Weight1)<1

)

select distinct Object, max(Weight1) from CTE

Group by Object

|||

--We can use CTE in SQL Server 2005. The recursive function will take care of the number of records for the same object in your table.

With MyCTE(Object, Weight, Weights, myNum) AS

(SELECT a.Object, CONVERT(varchar(50), MIN(a.Weight)) as col1, CONVERT(varchar(50),(a.Weight)) as Weights, 1 as myNum

FROM mergeTable1$ a GROUP BY a.Object, CONVERT(varchar(50),(a.Weight))

UNION ALL

SELECT b.Object, CONVERT(varchar(50), b.Weight), CONVERT(varchar(50), (c.Weights + ',' + b.Weight)), c.myNum+1 as myNum

FROM mergeTable1$ b INNER JOIN MyCTE c ON b.Object=c.Object

WHERE b.Weight>c.Weight

)

SELECT a.Object, Weights FROM MyCTE a INNER JOIN (SELECT Max(a1.myNum) as myNumMax, a1.Object FROM MyCTE a1

group by a1.Object) b on a.Object=b.Object AND a.myNum= b.myNumMax ORDER BY a.Object

|||

Yet another way to do it, using data from one of the other posts, and the 2005 techniques from:

http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html

Create table tbl(Object varchar(100), Weight int)

insert tbl

Select 'table', 5 union all

Select 'Chair', 6 union all

Select 'Computer', 3 union all

Select 'Computer', 5 union all

Select 'TV' , 20 union all

Select 'TV' , 15 union all

Select 'Radio' , 10 union all

Select 'Computer' ,10

SELECT
Object,
Weights = LEFT(o.list, LEN(o.list)-1)
FROM
(select distinct object from tbl) as tbl --if these values are defined in another related table it is more clear
CROSS APPLY
(
SELECT CONVERT(VARCHAR(12), Weight) + ',' AS [text()]
FROM tbl as t
WHERE t.object = tbl.object
ORDER BY weight
FOR XML PATH('')
) o (list)

|||If you don't mind, what does MYCTE stand for ?|||CTE (Common Table Expression) is a new function in SQL Server 2005. You can look it up from Book Online. MyCTE is a name like you would call a table mytable in your database. I like Louis's solution a lot. It uses another new CROSS APPLY funtion in SQL Server 2005 too.|||

For some ideas, see:

http://www.projectdmx.com/tsql/rowconcatenate.aspx

--

Anith

|||

i belive this can help

well use this function to get retrive the string that cotaians the rows of the specific ID.

CREATE FUNCTION dbo.ConRow(@.JID int)

RETURNS VARCHAR(8000)

AS

BEGIN

DECLARE @.Output VARCHAR(8000)

SELECT @.Output = COALESCE(@.Output+', ', '') + CONVERT(varchar(20), JP.a)

FROM [E_JobPending] JP

WHERE JP.JobID = @.JID

RETURN @.Output

END

select dbo.ConRow(jobid), vE_Job.*

from

vE_Job

DROP FUNCTION dbo.ConRow

Computed column formulae...2000 vs 2005

My head hurts!

I have a wicked database upgrade mechanism that works broadly like this:

Master database schema is scanned into an object model then saved to an XML file.

Sometime later...XML file is loaded back into object model and target database schema is scanned into another instance of this object model

CompareSchemas routine takes both object models as input, compares them, and outputs a collection of Actions needed to bring the target in line with the master i.e. it works out all the DDL statements necessary to synchronize the database structure.

My problem is that I need to have this working seamlessly between 2000 and 2005 - our master development is still in SQL Server 2000 and will be until all our customers have moved up to 2005 (who knows how long that will take), but we have to allow new customers to run with 2005.

At the moment my upgrade mechanism always states that computed columns have changed. It seems that 2005 formats the formulae for computed columns differently to how 2000 did:

SQL 2000 version:

([bygroup] + 2 * [bysubgroup] + 4 * [byexchangecode] + 8 * [byitem])

SQL 2005 version:

((([bygroup]+(2)*[bysubgroup])+(4)*[byexchangecode])+(8)*[byitem])

Short of writing an expression parser to identify the fact that these two formulae are identical (which I REALLY don't have time for!), is there any way to get 2005 to format the formula in the same way as 2000?

Anyone?|||Any one at all? |||

I don't know of anyway to force sql2k5 to give you the sql2k format. However, wouldn't it be as simple as a replace (i.e. replace('sql2k5 format','sql2k format)).

Complex Joins

Hi,
I am unsure about the best approach to the following :
I have a database where I have to store about 60000 different Objects, and
each Object has a number of Properties attached to it (the number of
properties attached and the type of properties can vary per object).
Thus far my table structure looks like this :
Table Objects (
Id BigInt,
Descr varchar(250),
...
)
Table Properties (
Id BigInt,
Descr varchar(50)
)
Table PropertyValues (
Id BigInt,
ObjectId BigInt,
PropertyId BigInt,
PropertyValue varchar(50)
)
Now my application lets the user select specific rows from the Objects table
using selections of different combinations of propertyvalues. This leads to
rather complex queries that sometimes contain between 1 to 15 joins of the
same table (PropertyValues).
Something like this:
Select Id, Descr from Object INNER JOIN
PropertyValues A on A.PropertyId = 1 AND A.PropertyValue = 'ABC' INNER JOIN
PropertyValues B on B.PropertyId = 2 AND B.PropertyValue = 'DEF' INNER JOIN
...
PropertyValues K on K.PropertyId = 11 AND K.PropertyValue = 'AnotherValue'
I would like to know if this is the right way to create this functionality
or should I think of another table structure.
Thanks
HermanHerman
I think you designed your database well. That enforce many-many relationship
between objects and property tables.
Now regarding to queries.
I'd start to write the query with follow structure.
Select Id, Descr from Object O INNER JOIN PropertyValues PV
ON O.ObjectId=PV.ObjectId INNER JOIN Properties P
ON PV.PropertyId =P.PropertyId
WHERE PV.PropertyId IN ('DEF' .....) AND
PV.ObjectId IN (1,2....)
Note: I don't know exactly what are your business requirements ,therfore if
you something wrong try modify it for
your needs.
"Herman Lammers" <hlammers_REMOVE_THIS@.allround-ipDOTnl> wrote in message
news:uApSPyhnDHA.1676@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am unsure about the best approach to the following :
> I have a database where I have to store about 60000 different Objects, and
> each Object has a number of Properties attached to it (the number of
> properties attached and the type of properties can vary per object).
> Thus far my table structure looks like this :
> Table Objects (
> Id BigInt,
> Descr varchar(250),
> ...
> )
> Table Properties (
> Id BigInt,
> Descr varchar(50)
> )
> Table PropertyValues (
> Id BigInt,
> ObjectId BigInt,
> PropertyId BigInt,
> PropertyValue varchar(50)
> )
> Now my application lets the user select specific rows from the Objects
table
> using selections of different combinations of propertyvalues. This leads
to
> rather complex queries that sometimes contain between 1 to 15 joins of the
> same table (PropertyValues).
> Something like this:
> Select Id, Descr from Object INNER JOIN
> PropertyValues A on A.PropertyId = 1 AND A.PropertyValue = 'ABC' INNER
JOIN
> PropertyValues B on B.PropertyId = 2 AND B.PropertyValue = 'DEF' INNER
JOIN
> ...
> PropertyValues K on K.PropertyId = 11 AND K.PropertyValue = 'AnotherValue'
> I would like to know if this is the right way to create this functionality
> or should I think of another table structure.
> Thanks
> Herman
>
>|||Although it is possible to implement a generic solution for any
application with an OO design like this, don't go there. Performance
will be horrendous and it will be difficult to enforce data integrity
because all user data will be stored in the single varchar(50)
PropertyValue column. This is problematic for many reasons.
Note that I'm not saying that one should never implement this type of
design. OO design may be appropriate for infrequently accessed
configuration data, such as meta-data loaded at application startup but
should generally be avoided in a relational database. It is better to
stick with the relational model in a relational database like SQL
Server.
Consider the schema of the Northwind Orders table below and ask yourself
how you would perform the following with your schema:
- ensure OrderID is unique (primary key)
- ensure data is valid for the appropriate data type (e.g. int and
datetime)
- insert an order with a 60 character ShipAddress or unicode data
- enforce referential integrity (e.g. an order must have a customer)
- query data (e.g. print shipping labels)
- delete data for a given order
CREATE TABLE [Orders] (
[OrderID] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerID] [nchar] (5) NULL ,
[EmployeeID] [int] NULL ,
[OrderDate] [datetime] NULL ,
[RequiredDate] [datetime] NULL ,
[ShippedDate] [datetime] NULL ,
[ShipVia] [int] NULL ,
[Freight] [money] NULL CONSTRAINT [DF_Orders_Freight] DEFAULT (0),
[ShipName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[ShipAddress] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ShipCity] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[ShipRegion] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ShipPostalCode] [nvarchar] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipCountry] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
[OrderID]
),
CONSTRAINT [FK_Orders_Customers] FOREIGN KEY
(
[CustomerID]
) REFERENCES [Customers] (
[CustomerID]
),
CONSTRAINT [FK_Orders_Employees] FOREIGN KEY
(
[EmployeeID]
) REFERENCES [Employees] (
[EmployeeID]
),
CONSTRAINT [FK_Orders_Shippers] FOREIGN KEY
(
[ShipVia]
) REFERENCES [Shippers] (
[ShipperID]
)
)
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Herman Lammers" <hlammers_REMOVE_THIS@.allround-ipDOTnl> wrote in
message news:uApSPyhnDHA.1676@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am unsure about the best approach to the following :
> I have a database where I have to store about 60000 different Objects,
and
> each Object has a number of Properties attached to it (the number of
> properties attached and the type of properties can vary per object).
> Thus far my table structure looks like this :
> Table Objects (
> Id BigInt,
> Descr varchar(250),
> ...
> )
> Table Properties (
> Id BigInt,
> Descr varchar(50)
> )
> Table PropertyValues (
> Id BigInt,
> ObjectId BigInt,
> PropertyId BigInt,
> PropertyValue varchar(50)
> )
> Now my application lets the user select specific rows from the Objects
table
> using selections of different combinations of propertyvalues. This
leads to
> rather complex queries that sometimes contain between 1 to 15 joins of
the
> same table (PropertyValues).
> Something like this:
> Select Id, Descr from Object INNER JOIN
> PropertyValues A on A.PropertyId = 1 AND A.PropertyValue = 'ABC' INNER
JOIN
> PropertyValues B on B.PropertyId = 2 AND B.PropertyValue = 'DEF' INNER
JOIN
> ...
> PropertyValues K on K.PropertyId = 11 AND K.PropertyValue ='AnotherValue'
> I would like to know if this is the right way to create this
functionality
> or should I think of another table structure.
> Thanks
> Herman
>
>|||Hi Dan,
thanks for your reply. Creating this generic solution was not exactly what I
had in mind.
The model I described is just a (small) part of traditional relational
model.
The reason I came up with this model is that I wanted to give my customer
the possibility of
assigning extra fields (Properties) to (groups of) products (Objects). This
structure is then being used for product selection only.
My customer sells fasteners which can be divided into several product groups
(washers, nuts, bolts)
Apart from product descr, Product price etc. there are numerous (more then I
ever thought) other properties which describe the product, and these depend
on the type of product. (washers have an inner and outer diameter, and a
thickness, bolts have a product length, headtype etc). This variety brought
me to this model.
Any values critical to the rest of the application would be taken out of the
structure, and stored in the product table.
Thanks anyway
Herman
"Dan Guzman" <danguzman@.nospam-earthlink.net> schreef in bericht
news:ePCz1VinDHA.2080@.TK2MSFTNGP10.phx.gbl...
> Although it is possible to implement a generic solution for any
> application with an OO design like this, don't go there. Performance
> will be horrendous and it will be difficult to enforce data integrity
> because all user data will be stored in the single varchar(50)
> PropertyValue column. This is problematic for many reasons.
> Note that I'm not saying that one should never implement this type of
> design. OO design may be appropriate for infrequently accessed
> configuration data, such as meta-data loaded at application startup but
> should generally be avoided in a relational database. It is better to
> stick with the relational model in a relational database like SQL
> Server.
> Consider the schema of the Northwind Orders table below and ask yourself
> how you would perform the following with your schema:
> - ensure OrderID is unique (primary key)
> - ensure data is valid for the appropriate data type (e.g. int and
> datetime)
> - insert an order with a 60 character ShipAddress or unicode data
> - enforce referential integrity (e.g. an order must have a customer)
> - query data (e.g. print shipping labels)
> - delete data for a given order
>
> CREATE TABLE [Orders] (
> [OrderID] [int] IDENTITY (1, 1) NOT NULL ,
> [CustomerID] [nchar] (5) NULL ,
> [EmployeeID] [int] NULL ,
> [OrderDate] [datetime] NULL ,
> [RequiredDate] [datetime] NULL ,
> [ShippedDate] [datetime] NULL ,
> [ShipVia] [int] NULL ,
> [Freight] [money] NULL CONSTRAINT [DF_Orders_Freight] DEFAULT (0),
> [ShipName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [ShipAddress] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [ShipCity] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [ShipRegion] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [ShipPostalCode] [nvarchar] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [ShipCountry] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
> (
> [OrderID]
> ),
> CONSTRAINT [FK_Orders_Customers] FOREIGN KEY
> (
> [CustomerID]
> ) REFERENCES [Customers] (
> [CustomerID]
> ),
> CONSTRAINT [FK_Orders_Employees] FOREIGN KEY
> (
> [EmployeeID]
> ) REFERENCES [Employees] (
> [EmployeeID]
> ),
> CONSTRAINT [FK_Orders_Shippers] FOREIGN KEY
> (
> [ShipVia]
> ) REFERENCES [Shippers] (
> [ShipperID]
> )
> )
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> --
> SQL FAQ links (courtesy Neil Pike):
> http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> http://www.sqlserverfaq.com
> http://www.mssqlserver.com/faq
> --
> "Herman Lammers" <hlammers_REMOVE_THIS@.allround-ipDOTnl> wrote in
> message news:uApSPyhnDHA.1676@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > I am unsure about the best approach to the following :
> >
> > I have a database where I have to store about 60000 different Objects,
> and
> > each Object has a number of Properties attached to it (the number of
> > properties attached and the type of properties can vary per object).
> >
> > Thus far my table structure looks like this :
> >
> > Table Objects (
> > Id BigInt,
> > Descr varchar(250),
> > ...
> > )
> >
> > Table Properties (
> > Id BigInt,
> > Descr varchar(50)
> > )
> >
> > Table PropertyValues (
> > Id BigInt,
> > ObjectId BigInt,
> > PropertyId BigInt,
> > PropertyValue varchar(50)
> > )
> >
> > Now my application lets the user select specific rows from the Objects
> table
> > using selections of different combinations of propertyvalues. This
> leads to
> > rather complex queries that sometimes contain between 1 to 15 joins of
> the
> > same table (PropertyValues).
> >
> > Something like this:
> >
> > Select Id, Descr from Object INNER JOIN
> > PropertyValues A on A.PropertyId = 1 AND A.PropertyValue = 'ABC' INNER
> JOIN
> > PropertyValues B on B.PropertyId = 2 AND B.PropertyValue = 'DEF' INNER
> JOIN
> > ...
> > PropertyValues K on K.PropertyId = 11 AND K.PropertyValue => 'AnotherValue'
> >
> > I would like to know if this is the right way to create this
> functionality
> > or should I think of another table structure.
> >
> > Thanks
> >
> > Herman
> >
> >
> >
>|||On Wed, 29 Oct 2003 16:57:44 +0100, "Herman Lammers"
<hlammers_REMOVE_THIS@.allround-ipDOTnl> wrote:
>Hi Dan,
>thanks for your reply. Creating this generic solution was not exactly what I
>had in mind.
>The model I described is just a (small) part of traditional relational
>model.
>The reason I came up with this model is that I wanted to give my customer
>the possibility of
>assigning extra fields (Properties) to (groups of) products (Objects). This
>structure is then being used for product selection only.
>My customer sells fasteners which can be divided into several product groups
>(washers, nuts, bolts)
>Apart from product descr, Product price etc. there are numerous (more then I
>ever thought) other properties which describe the product, and these depend
>on the type of product. (washers have an inner and outer diameter, and a
>thickness, bolts have a product length, headtype etc). This variety brought
>me to this model.
Well, it's the proper theoretical normalization of the table, but it
seems, to me, like you might want groupings... especially because I
think you'll end up having a hundred different records that all say
"1/4 inch diameter"
Maybe a bolt table, a washers table, etc., all with a foreign "item
number" key - foreign, because you could autonumber it on another
table that has two columns, "item number", "item type" - and each
table would have all of the necessary columns to list the necessary
properties. If you need additional properties for select items, then
you can create another linked table.
So, basically, you have
Item Listing:
"ItemNumber", "ItemType"
Washer Properties:
"Item Number" "InnerDiameter", "OuterDiameter", "Material", "Shape",
"Thickness"
I think that you want the "ItemType" to be a direct link to the
relevant table, but I don't know how to do that (or whether it *can*
be done).
This will cut down on the number of joins and make your table
structure a lot more understandable.
(Warning: I'm a DB administrator, not a developer, and your advice is
worth half of what you paid me for it.)