up-to-date crosstab 'view' using sql

NoviceMe
11-02-2004, 08:50 AM
Hi,

before I start messing round with this, I wanted to know if its possible, or whether I should approach this another way (Im a completenovice with sql). I have a table which currently has a number of variables and a date column something like:

var1 var2 date
smith 25 11/11/2001
jones 11 24/11/2001
brown 8 21/12/2002
white 2 18/11/2002
white 25 11/11/2001
brown 11 24/11/2001
jones 8 21/12/2002
smith 2 18/11/2002

formatted as dd/mm/yyyy. I want to create a crosstab 'view' from this table that looks as follows:

var1 2001 2002
smith 25 2
jones 11 8
brown 11 8
white 25 8

Firstly, can you create crosstabs with sql, secondly can they be created as views? I want the resulting table to reflect the current values in the base tables, any time it is opened, so it is up-to-date.
Any pointers would be greatly appreciated.

MKoslof
11-06-2004, 11:26 AM
Cross tab queries are possible in SQL Server..it just takes some work. There are tons of resources on the web regarding this. Here is one sample to get you started:

http://www.mssqlserver.com/faq/development-crosstab.asp

NoviceMe
11-06-2004, 12:10 PM
MKoslof

thanks for pointing me in the right direction, I had given this one up as sybase sql anywhere (which I am using) would not allow me to create crosstab or pivots (like for example Access can). I created the sample table in the link you gave, and it produced a crosstab, but without any values, it did not like the word 'value' i.e. Criteria_4 = MAX(substring(Value, 1, datalength(Value)

Should I be putting something else here? It would be excellent if this works, as I ended up creating the crosstabs using a very long-winded approach - by creating lots of tables then joining them up. the code for the table in the example is as follows:


SELECT Product_Code,
Criteria_1 = MAX(substring(Value, 1, datalength(Value) * (1 -abs(sign(Criteria_Code - 1))))),
Criteria_2 = MAX(substring(Value, 1, datalength(Value) * (1 -abs(sign(Criteria_Code - 2))))),
Criteria_3 = MAX(substring(Value, 1, datalength(Value) * (1 -abs(sign(Criteria_Code - 3))))),
Criteria_4 = MAX(substring(Value, 1, datalength(Value) * (1 -abs(sign(Criteria_Code - 4)))))
FROM <tbl>
GROUP BY Product_Code


thanks in advance:)

MKoslof
11-06-2004, 01:15 PM
Value would be the field name in question :)

NoviceMe
11-06-2004, 01:23 PM
MKoslof

sorry for being thick, I have put the field names where 'value' is (was that what you meant?) but it still returns a blank table (just the headers appear).
It doesn't like it if I leave the word value in the query.

MKoslof
11-06-2004, 03:07 PM
Well because that particular example is probably not what you want..you can't just copy and paste in that particular code and expect it to work for your own data return. Read that page and look at what it is returning. You probably do not want to do a max(), you need to review the structure of the query and tweak it do what you need for your particular query.

Here is another example..basically by using the CASE statement, you can structure your return. I assume you have the northwind database or something of the like..you can use this example if so, create a table and follow this:

http://www.databasejournal.com/features/mssql/article.php/1473111

NoviceMe
11-06-2004, 03:18 PM
MKoslof,

I shall try that....thanks so much:D

SQL is still pretty much a mystery to me, just as I think I understand what its doing, and get all 'smug' over achieving the simplest of tasks (mega achievement for me) I am soon shown that I still don't have a clue :( I think it takes years to get to where you 'experts and gurus' are....but its something to strive toward :D

I shall go over what you have said so far, and take a look at the link you have provided...and hope that the mist lifts...and suddenly I understand sql :)
yes please!

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum