Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Pivot?

Status
Not open for further replies.
gbaughma

gbaughma

IS-IT--Management
Staff member
Joined
Nov 21, 2003
Messages
4,773
Location
US
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
 
Sort by date Sort by votes

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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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)

Code: 
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:

Code: 
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
 
Upvote 0 Downvote
Can you try something like this:

Code: 
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
 
Upvote 0 Downvote
Oh yeah.... one more thing. Notice that I ordered the Analytes alphabetically, which deviates from your desired results. I hope this is ok. [smile]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Something like this:

Code: 
<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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Brianfree
  • Locked
  • Question Question
Simple SQL Pivot
Replies
9
Views
740
Chris Miller
Chris Miller
ahmedsa2018
  • Locked
  • Question Question
How to make pivot for features dynamically based on feature name and feature value ?
Replies
1
Views
463
TamarGranor
TamarGranor
grnzbra
  • Locked
  • Question Question
Pivot Table Without Numbers
Replies
12
Views
710
SkipVought
S
malaygal
  • Locked
  • Question Question
Pivot rows into columns
Replies
1
Views
254
dhookom
dhookom
aliahad
  • Locked
  • Question Question
How to Use a PIVOT
Replies
1
Views
197
fredericofonseca
fredericofonseca

Part and Inventory Search

Sponsor

Back
Top