Pivot?

[gbaughma]

Does SQL Server (2008 express, in my case) do Pivot tables?

If so, what is the syntax? I'm not finding anything in my book on it...

Thanks 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

 

[gmmastros]

http://msdn.microsoft.com/en-us/library/ms177410.aspx

In my opinion, it is usually best to pivot in your front end. What I mean is.... return the data un-pivoted and write code in your front end (ASP ?) to do the pivoting.

-George

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

 

[markros]

Why do you think so, George?

Yes, SQL Server 2005 and up supports PIVOT command. Just google on SQL Server PIVOT you should find plenty of examples, see,

http://www.setfocus.com/technicalarticles/sql-server-2005-tsql.aspx

(I cheated, though, I searched on 'Kevin Goff PIVOT')

 

[RiverGuy]

I agree to pivot on the front end. Otherwise, if the columns returned in your resultset vary from one query to the next, your report could quickly break.

 

[gmmastros]

I don't know, I just do.

Depending on the circumstances, sometimes you don't know how many columns you return. I'm used to not knowing the number of rows, but the number of columns?

Also, I suspect pivoting is an expensive operation (in terms of performance). Seems it would be best to push that performance hit on to the client (instead of the database server). This is especially true if it's a simple matter of displaying rows as columns and columns as rows.



-George

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

 

[gbaughma]

Well, that's the way I did it initially.....

Unfortunately, on my PIVOTs there are not a "set" number of columns....

And, while it DOES work on the front end, it can be hundreds, if not thousands, of queries to make it work.

I'm sure someone has a better way....

Essentially, (in asp) I do a "Select distinct" to get my column headers, then I manually populate each cell from there on out with a SELECT statement. It's painful...

Like I said, I'm sure there's a better way.



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]

Can you show some sample data and expected results?

-George

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

 

[gbaughma]

Let me give a better example of what I'm trying to accomplish... perhaps someone would like to take a stab at it.

Here's a "sample" data set... (names/values changed for privacy)

Location     Collection Date    Analyte     Results
Well-1       01/01/2008         Arsenic     <.01
Well-1       02/01/2008         Arsenic     <.01
Well-2       01/01/2008         Lead        <.01
Well-3       01/01/2008         Copper      <.01

So, there can be any number of Location / Collection Date combinations, and I usually prompt the user for a collection date range.

The output that I need is like this:

Analyte      Well-1       Well-1       Well-2      Well-3
             01/01/2008   02/01/2008   01/01/2008  01/01/2008
Arsenic      <.01         <.01         n/a         n/a
Lead         n/a          n/a          <.01        n/a
Copper       n/a          n/a          n/a         <.01

.... As you can see, it's a perfect demonstration of a PIVOT table, or a cross-tab query. There can be any number of location/date combinations, and endless analytes.

So, any thoughts on how to efficiently generate this table? Right now, I'm doing a query for each cell... it works, but it's painful.... and I had to do it that way because there may not be results (n/a's) for certain 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

 

[gmmastros]

Can you try something like this:

Select A.Location, A.CollectionDate, B.Analyte, Coalesce(T.Results, 'n/a') As Results
From   (
       Select Location, CollectionDate 
       From   YourTableName
       Group By Location, CollectionDate
       ) As A
       Cross Join (
         Select Distinct Analyte From YourTableName
         ) As B
       Left Join YourTableName T
         On A.Location = T.Location
         And A.CollectionDate = T.CollectionDate
         And B.Analyte = T.Analyte
Order By A.Location, A.CollectionDate, B.Analyte

Your results will come back like this:

[tt][blue]
Location CollectionDate Analyte Results
-------- -------------- -------- -------
Well-1 2008-01-01 Arsenic <.01
Well-1 2008-01-01 Copper n/a
Well-1 2008-01-01 Lead n/a
Well-1 2008-02-01 Arsenic <.01
Well-1 2008-02-01 Copper n/a
Well-1 2008-02-01 Lead n/a
Well-2 2008-01-01 Arsenic n/a
Well-2 2008-01-01 Copper n/a
Well-2 2008-01-01 Lead <.01
Well-3 2008-01-01 Arsenic n/a
Well-3 2008-01-01 Copper <.01
Well-3 2008-01-01 Lead n/a
[/blue][/tt]

Then, when you are displaying your results, keep track of the location and collection date. When they change (from one row to the next) create another column in your report. Alternatively, you can check the number of distinct analytes. This result set will always have a multiple of them. So, if you know there are 3 analytes, then every 3rd row should start a new column.

The benefit here is that you have just a single query to get all your data and relatively simple logic for pulling it out of the result set.

Let me know if this will work for you.

-George

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

 

[gmmastros]

Oh yeah.... one more thing. Notice that I ordered the Analytes alphabetically, which deviates from your desired results. I hope this is ok.

-George

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

 

[gbaughma]

That's not bad.... however, when I'm building my tables in ASP/HTML, I have to build them across, not down.

But, that's a start (flipping through my book and reading about COALESCE)...



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 if your table has multiple columns but a single row? In the rows, you could put a <br/> tag between the items so they appear on another row (without actually being in another row).

Just a thought.

-George

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

 

[gmmastros]

Something like this:

<table><tr>
  <td><br /><br /><br />Arsenic<br />Copper<br />Lead</td>
    <%
    Set RS = GetRecordset("YourQueryHere")
	
    AnalyteCount = 3
		
    While Not RS.Eof
      Data = RS("Location") & "<br />" & RS("CollectionDate") & "<br/><br/>"
      For i = 1 to AnalyteCount
        Data = Data & RS("Results") & "<br/>"
        RS.MoveNext
      Next
      Response.Write("<td>" & Data & "</td>")
    Wend

   %>    
		
</tr></table>

-George

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

 

[gbaughma]

George:

I see where you're going with that.... a "tall" cell in the table. That's unique, I'll admit.... it won't work for my application, but it's unique.

Yeah... I know they say that doing pivot tables on the SQL Server side isn't the best way to do things... but it has to be better than hundreds of individual queries....

Even building a temporary table on the SQL backend, then grabbing that recordset for the output... that might work...



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