Crosstabbing (or rotating) a sql table

[pompair]

I have a table DEFINITION with this content:

def_id game_id generaldef_id definition
========================================
1 1 1
firstname
2 1 2 lastname
3 1 3 age
4 1 4 status
5 1 5 position
6 2 6 firstname
7 2 7 lastname
8 2 8 nickname
etc...

Note: There are many possible values to the "definition" row. So the
solutions needs to be DYNAMIC!
Note 2: Don't worry too much about different game_id's, they are for
different kind of games...

Then I have another table PLAYERDEF with the following values:

playedef_id user_id generaldef_id value
======================================
1 1 1 allan
2 1 2
shearer
3 1 3 44
4 1 4 retired
5 1 5 forward
6 2 1 george
7 2 2
mitropoulos
etc...

Then I have a USER TABLE somewhere, but I leave it out now...

How can I merge these tables into this kind of RESULT_SET:

game_id user_id firstname lastname age
status position ....
================================================== ===============
1 1 allan shearer
44 retired forward
1 2 george mitropoulos 26
active goalie
etc...

So I need some kind of crosstabbing, or pivoting or rotating....
Any help appreciated.
I'm on SQL Server 2005.

-pom-

[Erland Sommarskog]

pompair (ti*******@gmail.com) writes:

I have a table DEFINITION with this content:

def_id game_id generaldef_id definition
========================================
1 1 1
firstname
2 1 2 lastname
3 1 3 age
4 1 4 status
5 1 5 position
6 2 6 firstname
7 2 7 lastname
8 2 8 nickname
etc...

Note: There are many possible values to the "definition" row. So the
solutions needs to be DYNAMIC!
Note 2: Don't worry too much about different game_id's, they are for
different kind of games...
...
So I need some kind of crosstabbing, or pivoting or rotating....
Any help appreciated.
I'm on SQL Server 2005.

While static pivoting is possible to do in SQL Server, there is nothing
built-in for dynamic pivoting. But have a look at SQL Server MVP Itzik
Ben-Gan's sp_pivot at
http://www.sqlmag.com/Article/Articl...ver_94268.html.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Anith Sen]

Consider avoiding t-SQL for such tasks primarily because it is the wrong
tool for the job. SQL has little to do with data represented or retrieved in
a varying set of columns.

A better way would be to get the resultset to the client side, use procedure
programming language to loop through the rows and create the display format
you need.

--
Anith

[DA Morgan]

Anith Sen wrote:

Consider avoiding t-SQL for such tasks primarily because it is the wrong
tool for the job. SQL has little to do with data represented or retrieved in
a varying set of columns.

A better way would be to get the resultset to the client side, use procedure
programming language to loop through the rows and create the display format
you need.

Unless of course you just happen to live in a country with laws that
regulate such matters as audit trails on financial data.

Take it out of the database, into Excel no doubt, and you have destroyed
any nuance of accountability.

The proper place for data is in the database.

The proper place for cross-tabulation is in a reporting tool.
--
Daniel A. Morgan
University of Washington
da******@x.washington.edu (replace x with u to respond)

[mr.mcgoo]

On Feb 5, 8:34 am, DA Morgan <[e-mail]:

Anith Sen wrote:

Consider avoiding t-SQL for such tasks primarily because it is the wrong
tool for the job. SQL has little to do with data represented or retrieved in
a varying set of columns.

A better way would be to get the resultset to the client side, use procedure
programming language to loop through the rows and create the display format
you need.

Unless of course you just happen to live in a country with laws that
regulate such matters as audit trails on financial data.

Take it out of the database, into Excel no doubt, and you have destroyed
any nuance of accountability.

The proper place for data is in the database.

The proper place for cross-tabulation is in a reporting tool.
--
Daniel A. Morgan
University of Washington
[e-mail] (replace x with u to respond)

Hi Daniel,
I think you misunderstoodd Anith's point. Anith meant that you should
use something else to access the data directly from the database and
loop through each relevant record generating a display as needed. For
example ASP.Net, C#, VB.Net can all access SQL directly through ADO
but you can code to present the data any way you want - not something
you can necessarily do in T-SQL. Not to export data then manipulate
the data which is how I believe you have read the suggestion.

Cheers
Chris