Crosstabbing (or rotating) a sql table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

pompair - 04 Feb 2008 19:11 GMT

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-

Reply to this Message

Erland Sommarskog - 04 Feb 2008 22:45 GMT

> I have a table DEFINITION with this content:
>
[quoted text clipped - 19 lines]
> 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/ArticleID/94268/sql_server_94268.html.

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Reply to this Message

Anith Sen - 04 Feb 2008 23:57 GMT

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.

Signature

Anith

Reply to this Message

DA Morgan - 05 Feb 2008 16:34 GMT

> 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
[quoted text clipped - 3 lines]
> 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.

Signature

Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)

Reply to this Message

mr.mcgoo@gmail.com - 27 Feb 2008 05:33 GMT

> > 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
[quoted text clipped - 17 lines]
> University of Washington
> damor...@x.washington.edu (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

Reply to this Message