Advanced query question

Hi
I have a tre table setup in order to support dynamic attributs of
users:
user table:
| *userid* | *username*
| 1 | joe
| 2 | james
attributetype table:
| *attributetypeid* | *attributename* |
| 1 | height
| 2 | weight
userattribute table:
| *userattributeid* | *userid* | attributetypeid* |*text*
| 1 | 1 | 1 | 80
| 2 | 1 | 2 | 180
| 1 | 2 | 1 | 90
| 2 | 2 | 2 | 160
(the userid and attributetypeid are fk to the user and attributetype
tables.)
Now i would like to create a sql statement that returns something like:
| *userid* | *username* | *height* | *weight*
| 1 | joe | 80 | 180
| 1 | joe | 90 | 160
The should support that I add an additional attributetype, say "IQ"
and then it should return something like:
| *userid* | *username* | *height* | *weight* | *IQ*
| 1 | joe | 80 | 180 | null
| 1 | joe | 90 | 160 | null
I hope someone knows the answer to this or can at least provide
a different solution.
Regards,
Rasmus Oudal EdbergSelect UAT.UserId, U.UserName
, Min(Case When UAT.AttributeTypeId = 1 Then UAT.Text End) As Height
, Min(Case When UAT.AttributeTypeId = 2 Then UAT.Text End) As Weight
From Users As U
Join UserAttributes As UAT
On U.UserId = UAT.UserId
Group By UAT.UserId, U.UserName
When You add AttributeTypeId = 3 for IQ you would need to adjust the query l
ike
so:
Select UAT.UserId, U.UserName
, Min(Case When UAT.AttributeTypeId = 1 Then UAT.Text End) As Height
, Min(Case When UAT.AttributeTypeId = 2 Then UAT.Text End) As Weight
, Min(Case When UAT.AttributeTypeId = 3 Then UAT.Text End) As IQ
From Users As U
Join UserAttributes As UAT
On U.UserId = UAT.UserId
Group By UAT.UserId, U.UserName
There is no way to dynamically add columns to this crosstab type result with
out
using dynamic SQL or an outside reporting tool.
Thomas
"Rasmus Oudal Edberg studentz.dk>" <rasmus@.<nospam> wrote in message
news:OKjdW%23PTFHA.2336@.TK2MSFTNGP12.phx.gbl...
> Hi
> I have a tre table setup in order to support dynamic attributs of
> users:
> user table:
> | *userid* | *username*
> | 1 | joe
> | 2 | james
> attributetype table:
> | *attributetypeid* | *attributename* |
> | 1 | height
> | 2 | weight
> userattribute table:
> | *userattributeid* | *userid* | attributetypeid* |*text*
> | 1 | 1 | 1 | 80
> | 2 | 1 | 2 | 180
> | 1 | 2 | 1 | 90
> | 2 | 2 | 2 | 160
> (the userid and attributetypeid are fk to the user and attributetype table
s.)
> Now i would like to create a sql statement that returns something like:
> | *userid* | *username* | *height* | *weight*
> | 1 | joe | 80 | 180
> | 1 | joe | 90 | 160
> The should support that I add an additional attributetype, say "IQ"
> and then it should return something like:
> | *userid* | *username* | *height* | *weight* | *IQ*
> | 1 | joe | 80 | 180 | null
> | 1 | joe | 90 | 160 | null
> I hope someone knows the answer to this or can at least provide
> a different solution.
>
> Regards,
> Rasmus Oudal Edberg
>|||what you're looking for is xtab.
HOW TO: Rotate a Table in SQL Server
http://support.microsoft.com/defaul...b;EN-US;q175574
Dynamic Crosstab Queries
http://www.sqlmag.com/Articles/Inde...ArticleID=15608
xp_ags_crosstabExtended Stored Procedure
http://www.ag-software.com/xp_ags_crosstab.asp
and our RAC crosstab and pivoting utility
http://www.rac4sql.net
-oj
"Rasmus Oudal Edberg studentz.dk>" <rasmus@.<nospam> wrote in message
news:OKjdW%23PTFHA.2336@.TK2MSFTNGP12.phx.gbl...
> Hi
> I have a tre table setup in order to support dynamic attributs of
> users:
> user table:
> | *userid* | *username*
> | 1 | joe
> | 2 | james
> attributetype table:
> | *attributetypeid* | *attributename* |
> | 1 | height
> | 2 | weight
> userattribute table:
> | *userattributeid* | *userid* | attributetypeid* |*text*
> | 1 | 1 | 1 | 80
> | 2 | 1 | 2 | 180
> | 1 | 2 | 1 | 90
> | 2 | 2 | 2 | 160
> (the userid and attributetypeid are fk to the user and attributetype
> tables.)
> Now i would like to create a sql statement that returns something like:
> | *userid* | *username* | *height* | *weight*
> | 1 | joe | 80 | 180
> | 1 | joe | 90 | 160
> The should support that I add an additional attributetype, say "IQ"
> and then it should return something like:
> | *userid* | *username* | *height* | *weight* | *IQ*
> | 1 | joe | 80 | 180 | null
> | 1 | joe | 90 | 160 | null
> I hope someone knows the answer to this or can at least provide
> a different solution.
>
> Regards,
> Rasmus Oudal Edberg
>|||You are mixing dat and metadata in the schema. This is a serious
design flaw. Get a book on basic data modeling before you go any
further.|||On Fri, 29 Apr 2005 17:38:49 -0700, --CELKO-- wrote:

> You are mixing dat and metadata in the schema. This is a serious
> design flaw. Get a book on basic data modeling before you go any
> further.
Sounds like a correct observation to me.
However going through some of my DB books has
not given me an answer to an alternative datamodel
that would support the dynamic attributes I described
in my problem.
If however someone could point me in the right
direction that would help me a lot.
/Rasmus|||Hi oj,
Could you give me an example of this query using your rac4sql.
/Rasmus
"oj" <nospam_ojngo@.home.com> wrote in message
news:eNUOtYQTFHA.2996@.TK2MSFTNGP15.phx.gbl...
> what you're looking for is xtab.
> HOW TO: Rotate a Table in SQL Server
> http://support.microsoft.com/defaul...b;EN-US;q175574
> Dynamic Crosstab Queries
> http://www.sqlmag.com/Articles/Inde...ArticleID=15608
> xp_ags_crosstabExtended Stored Procedure
> http://www.ag-software.com/xp_ags_crosstab.asp
> and our RAC crosstab and pivoting utility
> http://www.rac4sql.net
> --
> -oj
>
> "Rasmus Oudal Edberg studentz.dk>" <rasmus@.<nospam> wrote in message
> news:OKjdW%23PTFHA.2336@.TK2MSFTNGP12.phx.gbl...
>|||Here is an example.
create table #users(userid int, username sysname)
insert #users select 1,'joe' union all select 2,'james'
create table #attributetypes(attributetypeid int,attributename sysname)
insert #attributetypes select 1,'height' union all select 2,'weight'
create table #userattributes(userattributeid int,userid int,attributetypeid
int,[text] int)
insert #userattributes select 1,1,1,80 union all select 2,1,2,180 union all
select 3,2,1,90 union all select 4,2,2,160
go
exec rac
@.transform='max(ua.[text])',
@.rows='u.userid & u.username',
@.pvtcol='a.attributename',
@.from='#userattributes ua join #users u on ua.userid=u.userid join
#attributetypes a on ua.attributetypeid=a.attributetypeid'
,@.shell='n'
-- ,@.row_totals='n'
-- ,@.grand_totals='n'
-- ,@.printagg='n'
,@.defaults1='y'
go
insert #attributetypes select 3,'iq'
insert #userattributes select 5,1,3,100 union all select 6,2,3,129
go
exec rac
@.transform='max(ua.[text])',
@.rows='u.userid & u.username',
@.pvtcol='a.attributename',
@.from='#userattributes ua join #users u on ua.userid=u.userid join
#attributetypes a on ua.attributetypeid=a.attributetypeid'
,@.shell='n'
-- ,@.row_totals='n'
-- ,@.grand_totals='n'
-- ,@.printagg='n'
,@.defaults1='y'
go
drop table #userattributes,#users,#attributetypes
go
--RESULT--
userid username height weight
-- -- -- --
1 joe 80 180
2 james 90 160
(1 row(s) affected)
(2 row(s) affected)
userid username height iq weight
-- -- -- -- --
1 joe 80 100 180
2 james 90 129 160
-oj
"Rasmus Oudal Edberg studentz.dk>" <rasmus@.<nospam> wrote in message
news:%23OKxKIdTFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Hi oj,
> Could you give me an example of this query using your rac4sql.
> /Rasmus
>|||>> an alternative data model that would support the an alternative
datamodel that would support the dynamic attributes I described in my
problem. <<
There are no "dynamic attributes" in a properly designed RDBMS. The
idea of data modeling is that you know what the world looks like before
you build the model. You enforce the rules, define the entities and
their attributes. You do not let an unknown user, present or future,
create anything in the model on the fly. Your users are users, not
magicians that can make elephants fall out of the sky.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1114959059.283784.264640@.g14g2000cwa.googlegroups.com...
> Your users are users, not
> magicians that can make elephants fall out of the sky.
>
Which would have scared the heck out of Hannibal as well as the Romans, BTW.