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.

ADOMD.NET : dynamic parameters

Logically speaking, the two cases below should behave the same. However case 2's output is wrong. Perhaps someone knows what's wrong in case 2.

Case 1:
-

AdomdCommand cmd = new AdomdCommand();

conn.Open();

cmd.Connection = conn;

cmd.CommandText = "SELECT Cluster(), PredictCaseLikelihood()" +

" FROM [Data Validation]" +

" NATURAL PREDICTION JOIN" +

" (SELECT " +

" (SELECT @.var0 as [var] " +

" UNION SELECT @.var1 as [var] " +

" UNION SELECT @.var2 as [var]) AS [vartable]) AS t";

Case 2
-

AdomdCommand cmd = new AdomdCommand();

conn.Open();

cmd.Connection = conn;

cmd.CommandText = "SELECT Cluster(), PredictCaseLikelihood()" +

" FROM [Data Validation]" +

" NATURAL PREDICTION JOIN" +

" (SELECT " +

" (SELECT @.var0 as [var] ";

for(int i=1; i<3; i++)

{

cmd.CommandText = cmd.CommandText +

"UNION SELECT @.var" + i.ToString() + " as [var] ";

}

cmd.CommandText = cmd.CommandText + ") AS [vartable]) AS t";

Mary

Can you get a debug print of the resulting command text?

Actually, the best way to solve this problem is to use rowset parameters. There is a sample here: http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/1313.aspx

(look for // Issue a DMX query from a rowset parameter and display a result)

For nested tables you will have to SHAPE the rowset.

|||

I've read this article:
http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/1313.aspx

The problem in my case is that I have to use the "UNION" keyword.
How to use rowset parameters for such case?

My case
--
cmd.CommandText = "SELECT Cluster(), PredictCaseLikelihood()" +
" FROM [Data Validation]" +
" NATURAL PREDICTION JOIN" +
" (SELECT " +
" (SELECT @.var0 as [var] " +
" UNION SELECT @.var1 as [var] " +
" UNION SELECT @.var2 as [var]) AS [vartable]) AS t";

The above link has only a solution for this case
-
cmd.CommandText = "SELECT Cluster(), PredictCaseLikelihood()" +
" FROM [Data Validation]" +
" NATURAL PREDICTION JOIN" +
" (SELECT " +
" (SELECT @.var0 as [var] " +
" SELECT @.var1 as [var] " +
" SELECT @.var2 as [var]) AS [vartable]) AS t";

// Suggested solution for the above case (not my case):
DataTable table = new DataTable();
table.Columns.Add("term0", System.Type.GetType("System.String"));
table.Columns.Add("term1", System.Type.GetType("System.String"));
table.Columns.Add("term2", System.Type.GetType("System.String"));
...
...

cmd.CommandText = "SELECT Cluster(), PredictCaseLikelihood()" +
" FROM [Data Validation]" +
" NATURAL PREDICTION JOIN" +
" @.InputTable as t";
cmd.Parameters.Add("InputTable", table);

Clearly, the provided solution not applicable for my case.
Please assist!

Mary

|||

So, basically, the problem is that your input is nested, right? It contains the [vartable] nested table.

You can try this:

DataTable topTable = new DataTable();
topTable.Columns.Add("K", typeof(System.Int32)); // case key

// Add one row to the case table with K=1
object[] row = new object[1];
row[0] = 1; // top key
topTable.Rows.Add(row);

DataTable nestedTable = new DataTable();
nestedTable.Columns.Add("K", typeof(System.Int32)); // foreign key
table.Columns.Add("var", System.Type.GetType("System.String"));

// Add one row to nested table for each @.var value
// each nested row has 1 as foreign key
row = new object[2];

for( ... )
{
row[0] = 1; // foreign key, alway one to match the topTable single row's key
row[1] = "aaa"; // foreign key
nestedTable.Rows.Add(row);
}

cmd.CommandText = "SELECT Cluster(), PredictCaseLikelihood()" +
" FROM [Data Validation]" +
" NATURAL PREDICTION JOIN" +
" SHAPE {@.topTable} " +
" APPEND( {@.nestedTable} RELATE K TO K) "+
" AS vartable AS T";

cmd.Parameters.Add("topTable", topTable);
cmd.Parameters.Add("nestedTable", nestedTable);

ADOMD.NET : dynamic parameters

Logically speaking, the two cases below should behave the same. However case 2's output is wrong. Perhaps someone knows what's wrong in case 2.

Case 1:
-

AdomdCommand cmd = new AdomdCommand();

conn.Open();

cmd.Connection = conn;

cmd.CommandText = "SELECT Cluster(), PredictCaseLikelihood()" +

" FROM [Data Validation]" +

" NATURAL PREDICTION JOIN" +

" (SELECT " +

" (SELECT @.var0 as [var] " +

" UNION SELECT @.var1 as [var] " +

" UNION SELECT @.var2 as [var]) AS [vartable]) AS t";

Case 2
-

AdomdCommand cmd = new AdomdCommand();

conn.Open();

cmd.Connection = conn;

cmd.CommandText = "SELECT Cluster(), PredictCaseLikelihood()" +

" FROM [Data Validation]" +

" NATURAL PREDICTION JOIN" +

" (SELECT " +

" (SELECT @.var0 as [var] ";

for(int i=1; i<3; i++)

{

cmd.CommandText = cmd.CommandText +

"UNION SELECT @.var" + i.ToString() + " as [var] ";

}

cmd.CommandText = cmd.CommandText + ") AS [vartable]) AS t";

Mary

Can you get a debug print of the resulting command text?

Actually, the best way to solve this problem is to use rowset parameters. There is a sample here: http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/1313.aspx

(look for // Issue a DMX query from a rowset parameter and display a result)

For nested tables you will have to SHAPE the rowset.

|||

I've read this article:
http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/1313.aspx

The problem in my case is that I have to use the "UNION" keyword.
How to use rowset parameters for such case?

My case
--
cmd.CommandText = "SELECT Cluster(), PredictCaseLikelihood()" +
" FROM [Data Validation]" +
" NATURAL PREDICTION JOIN" +
" (SELECT " +
" (SELECT @.var0 as [var] " +
" UNION SELECT @.var1 as [var] " +
" UNION SELECT @.var2 as [var]) AS [vartable]) AS t";

The above link has only a solution for this case
-
cmd.CommandText = "SELECT Cluster(), PredictCaseLikelihood()" +
" FROM [Data Validation]" +
" NATURAL PREDICTION JOIN" +
" (SELECT " +
" (SELECT @.var0 as [var] " +
" SELECT @.var1 as [var] " +
" SELECT @.var2 as [var]) AS [vartable]) AS t";

// Suggested solution for the above case (not my case):
DataTable table = new DataTable();
table.Columns.Add("term0", System.Type.GetType("System.String"));
table.Columns.Add("term1", System.Type.GetType("System.String"));
table.Columns.Add("term2", System.Type.GetType("System.String"));
...
...

cmd.CommandText = "SELECT Cluster(), PredictCaseLikelihood()" +
" FROM [Data Validation]" +
" NATURAL PREDICTION JOIN" +
" @.InputTable as t";
cmd.Parameters.Add("InputTable", table);

Clearly, the provided solution not applicable for my case.
Please assist!

Mary

|||

So, basically, the problem is that your input is nested, right? It contains the [vartable] nested table.

You can try this:

DataTable topTable = new DataTable();
topTable.Columns.Add("K", typeof(System.Int32)); // case key

// Add one row to the case table with K=1
object[] row = new object[1];
row[0] = 1; // top key
topTable.Rows.Add(row);

DataTable nestedTable = new DataTable();
nestedTable.Columns.Add("K", typeof(System.Int32)); // foreign key
table.Columns.Add("var", System.Type.GetType("System.String"));

// Add one row to nested table for each @.var value
// each nested row has 1 as foreign key
row = new object[2];

for( ... )
{
row[0] = 1; // foreign key, alway one to match the topTable single row's key
row[1] = "aaa"; // foreign key
nestedTable.Rows.Add(row);
}

cmd.CommandText = "SELECT Cluster(), PredictCaseLikelihood()" +
" FROM [Data Validation]" +
" NATURAL PREDICTION JOIN" +
" SHAPE {@.topTable} " +
" APPEND( {@.nestedTable} RELATE K TO K) "+
" AS vartable AS T";

cmd.Parameters.Add("topTable", topTable);
cmd.Parameters.Add("nestedTable", nestedTable);