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

 

[gmmastros]

What version of SQL Server?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Here is a version that works with SQL2000 and up.

Declare @Temp Table(SampleDate DateTime, Location VarChar(20), Analyte VarChar(20), Result Decimal(10,5))

Insert Into @Temp Values('01/01/2000','Well 1','Benzene',.02)
Insert Into @Temp Values('01/01/2000','Well 1','Toluene',.01)
Insert Into @Temp Values('01/01/2000','Well 2','Benzene',.01)
Insert Into @Temp Values('01/01/2000','Well 2','Toluene',.01)
Insert Into @Temp Values('01/20/2000','Well 1','Benzene',.01)
Insert Into @Temp Values('01/20/2000','Well 1','Toluene',.01)
Insert Into @Temp Values('01/20/2000','Well 2','Benzene',.01)
Insert Into @Temp Values('01/20/2000','Well 2','Toluene',.01)

Select Analyte,
       Min(Case When Location = 'Well 1' And SampleDate = '01/01/2000' Then Result End) As [Well 1, 01/01/2000],
       Min(Case When Location = 'Well 1' And SampleDate = '01/20/2000' Then Result End) As [Well 1, 01/20/2000],
       Min(Case When Location = 'Well 2' And SampleDate = '01/01/2000' Then Result End) As [Well 2, 01/01/2000],
       Min(Case When Location = 'Well 2' And SampleDate = '01/20/2000' Then Result End) As [Well 2, 01/20/2000]
From   @Temp
Group BY Analyte

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gbaughma]

SQL Server 2008 (lite) on the one I'm doing this with.

gmmastros:
Ummm... did I mention that the locations and dates can change? So it needs to be a dynamic pivot off of the two columns..



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]

With dynamic PIVOT, you need to construct your PIVOT dynamically.

See today's thread on MSDN with a similar problem (but in your case you'll need to first construct values for the PIVOT):

http://social.msdn.microsoft.com/Fo...l/thread/081a58ac-d7f4-4333-bcd9-3d06e16c50a5

PluralSight Learning Library

 

[gbaughma]

I understand about creating the pivot dynamically...

What I don't understand is pivoting on two columns.

In my example above, the Location and the Date made up a single column. So you could have:

Location 1           Location 1         Location 2
Date 1               Date 2             Date 1

... requiring the column headers on the pivot to be unique with a combination of Location and Date.

I can't imagine it being any different from a table that looked like this:

Chicago            Chicago         Chicago       New York
Quarter 1          Quarter 2       Quarter 3     Quarter 1

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

 

[gbaughma]

Mmm... I think I figured out part of it....

SELECT DISTINCT Location + CAST(CollectionDate AS VarChar) AS LocDate, Location, CollectionDate FROM dbo.dbase WHERE ProjectID='1234'

... gives me distinct headers (called LocDate), as well as the two columns that make up that header (Location and CollectionDate).

... now I just have to figure out how to get it to pivot on those two combined columns.



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]

Right. I think you need to use this as a field in your source table to do the dynamic pivot, e.g. select the exact expression and do a pivot on it. BTW, is it possible to have headers with char(13) inside them?

PluralSight Learning Library

 

[gbaughma]

Since I'm building the table in a web page, I can use a <BR> to split off the location and the date.

I think, perhaps, the secret is to build two dynamic variables, one with the locations, and one with the dates.... then, pivot TWICE... once on the locations, and then again on the dates, to merge the two columns together.

I think.

My brain hurts.


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]

No, I think George is on the righ track - you don't need a pivot on two columns separately, you need one pivot command on this combined column.

PluralSight Learning Library

 

[gbaughma]

I'm close...

The secret (I think) is to concatenate the two fields together..... This code is (almost) working as of 4:05 p.m. CST... I just can't get it to show the results... but I'm sure I'll figure it out.

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[spVic1]    Script Date: 03/15/2010 13:56:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[spVic1](

@sProject varchar(30) = '1519',
@dStartDate datetime = '01/01/1901',
@dEndDate datetime = '12/30/2100'
)
AS

DECLARE @column1 VARCHAR(MAX)
SELECT @column1 =
COALESCE(@column1 + ',[' + CAST(RTRIM(LTRIM(WebSample.Location)) AS varchar) + '<br>' + CONVERT(VarChar,CollectionDate,1) + ']',
	'[' + cast(RTRIM(LTRIM(WebSample.Location)) as varchar) + '<br>' + CONVERT(VarChar,CollectionDate,1) + ']')
	FROM NtsWeb.dbo.WebSample 
	WHERE ProjectID= @sProject
	GROUP BY Location, CollectionDate


DECLARE @query VARCHAR(MAX)

SET @query = 'SELECT Analyte, ' + @column1 + ' FROM (
SELECT Analyte, Units, Result, Location + RTRIM(LTRIM(Location))+ ''<br>'' + 
CONVERT(VARCHAR,CollectionDate,1) AS LocDate FROM NtsWeb.dbo.WebSample 
WHERE (NtsWeb.dbo.WebSample.ProjectID= ' + @sProject + '
AND Matrix=''Aqueous'')) tWebSample
PIVOT
(Max(Result) for [LocDate] IN (' + @column1 + ')) AS PivotTable1'

EXECUTE(@query)

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]

Did you try

print (@query) ?

Does it look correct?

PluralSight Learning Library

 

[gbaughma]

I got it to return results.

Darn it... I was looking for a command like that... I knew in VB it was Debug.Print... lol

Thank you.



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

 

[gbaughma]

Well, back to the drawing board.

Here's a recap.

I got the routine to work, pivoting on two combined columns (Location and Date).

However......

Because of the large datasets, the concatenated SQL query gets too long. Huge. Like 9000+ characters, by the time it concatenates the long column names and so forth.

So, I guess it's back to the drawing board. I could do it server-side and create a temporary table and output that, I suppose... but it's going to end up being looped through the records and filling the table (which is what I was trying to avoid).



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

 

[gmmastros]

This may not completely solve your problem, but....

You can split up your 9000+ character query in to smaller strings and then execute them all together. Something like this....

Declare @Sql1 varchar(8000)
Declare @Sql2 varchar(8000)
Declare @Sql3 varchar(8000)
Declare @Sql4 varchar(8000)

Set @Sql1 = 'Select '
Set @Sql2 = 'Column1, Column2, Column3 '
Set @Sql3 = 'From MyFavoriteTable '
Set @Sql4 = 'Where Anything = ''Something'''

Execute (@Sql1 + @Sql2 + @Sql3 + @Sql4)

If you do this, just be careful that none of the @Sql's are null because concatenating them together will cause the whole thing to be null, and executing a null isn't particularly helpful.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gbaughma]

George:

I'll give it a try. Right now, my brain hurts, so I'm going to do another project for a day or two....

Thank you though!


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]

You can declare it as varchar(max) - does it work?

PluralSight Learning Library

 

[gbaughma]

I did declare it as VARCHAR(MAX), but evidently it's the SQL statement that's concatenated itself that is too long for SQL to swallow all at once.



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]

Did you try

execute sp_ExecuteSQL @SQL ?

PluralSight Learning Library

 

[NastiaMurzin]

something like that:
use tableschema;
go
create procedure firsttable
as
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