SQL help (dynamic query?)

[cotton31]

Hi all,

I have a database which has two tables Places and Attributes.

Attributes contains information about the place, such as number of bedrooms and number of floors, but only has two fields - attribute type and value.

This means that when I query the two tables, I get the following returned:

PlaceRef | Address | AttributeType | Value
---------------------------------------------
ANY123 | 123 Any St | FLOORS | 2
ANY123 | 123 Any St | BEDS | 3
ANY123 | 123 Any St | BATHS | 2
ANY506 | 506 Any St | FLOORS | 2
ANY506 | 506 Any St | BEDS | 2

I need to normalize this so I get:

PlaceRef | Address | Floors | Beds | Baths
---------------------------------------------
ANY123 | 123 Any St | 2 | 3 | 2
ANY506 | 506 Any St | 2 | 2 |

I could do this with sub selects, taking the value where the Attribute Type = 'xxx' but this would mean that I need to know each of the Attribute Types.

It would be better if I could build this dynamically so that if any Attributes are added or taken away then it automatically picks this up.

I was thinking of using a loop but not sure how I would achieve this?

Can anyone help?

Thanks,

 

[Yakyb]

there is a function called Pivot

http://www.tsqltutorials.com/pivot.php
which may (MAY!!) help with this

i think the query would be along the lines of

Select * from attributes
Pivot
(
sum(Value)
for atttributeType in (select attributetype from attributes group by attributetype)
) as p

just been reading a little more, i think the below will cover it
Dynamic PIVOT - PIVOT without specifying columns

 

[helium]

Look up EAV structure, it will make everything more complicated but it will give you the desired result.

Its a confusing concept (possibly) at first but fits perfectly with your needs.

 

[cotton31]

there is a function called Pivot

http://www.tsqltutorials.com/pivot.php
which may (MAY!!) help with this

i think the query would be along the lines of

Select * from attributes
Pivot
(
sum(Value)
for atttributeType in (select attributetype from attributes group by attributetype)
) as p

just been reading a little more, i think the below will cover it
Dynamic PIVOT - PIVOT without specifying columns

I'll give this a try, thanks!

Look up EAV structure, it will make everything more complicated but it will give you the desired result.

Its a confusing concept (possibly) at first but fits perfectly with your needs.

From what I've read, this involves changing the DB structure. I have no control whatsoever over the makeup of the DB (its a DB used by one of our programs which I just query. Thanks for the help though.