Multi-Column Pivot? 1

[gbaughma]

I'm having a hard time wrapping my brain around this one.....

I need to build a pivot table (which I can do), but the pivot table can pivot on TWO columns.

Take for example, this mock recordset:

Sample Date     Location      Analyte     Result
01/01/2000      Well 1        Benzene     .02
01/01/2000      Well 1        Toluene     .01
01/01/2000      Well 2        Benzene     .01
01/01/2000      Well 2        Toluene     .01
01/20/2000      Well 1        Benzene     .01
01/20/2000      Well 1        Toluene     .01
01/20/2000      Well 2        Benzene     .01
01/20/2000      Well 2        Toluene     .01

So, I need the pivot table to look like this:

               Well 1         Well 1          Well 2       Well 2
               01/01/2000     01/20/2000      01/01/2000     01/20/2000
Benzene        .02            .01             .01            .01
Toluene        .01            .01             .01            .01

So, as you can see, I'm pivoting on the Well and the Date, then giving results for the Analyte..... but I can't figure out how to pivot on two fields.

TIA!


Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[NastiaMurzin]

now that's even better.
use tableschema;
go

create procedure firsttable
as
if tableschema.actualfirsttable is not null
drop table tableschema.actualfirsttable;
go

create table tableschema.actualfirsttable
(id int as primary key not null auto_increment,
column_well1 varchar(50) null,
column_well2 varchar(50) null,
column_well3 varchar(50) null,
column_well4 varchar(50) null,
column_date date not null);
go

insert into tableschema.actualfirsttable
values(.02,.01,.01,.01, cast'01/01/2009' as date);
go
insert into tableschema.actualfirsttable
values(.01,.01,.01,.01, cast'01/01/2009' as date);
go
Select * from tableschema.actualfirsttable;
go

 

[gbaughma]

Ow ow ow! Brain cramp!

Well, to follow up, I have to re-work this... mainly because the options of making a HUGE HUGE table is there....

I need to rework the input so that it's limiting the number of potential columns you can pick.

I have had this query choke Excel with too many columns, which pretty much makes it unusable anyway.

The pivot did work on two fields by concatenating the fields together; but the issue with the length of the SQL statement reared its head when there were a LOT of columns, and the concatenated column names had to be specified twice in the statement.

Back to the drawing board (and figuring out a way to keep the number of columns down, but still give the users what they need). Either that or I have to manually pivot with a mess of queries again.



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[markros]

Check Brad Schulz answer in this thread

http://social.msdn.microsoft.com/Fo...l/thread/82399639-a1ff-44a6-a785-4581a3e513f0

PluralSight Learning Library

 

[NastiaMurzin]

Try that.

Select tablename1.analyte,
sampledate, location, result
from (select * from tablename1)
as aliasnamefortablename1
pivot(
tablename1.analyte
for tablename1.location
in sampledate, location, result
)
as aliastablepivotedname2
order by aliastablepivotedname2.column1;