Pivot table for Microsoft SQL Server

Question

Pivot table for Microsoft SQL Server

Viewing 15 posts - 16 through 30 (of 43 total)

You must be logged in to reply to this topic. Login to reply

Sharon Matyk SSC Eights! Points: 932 More actions · Answer 1

Hi - based on the dates of the other comments, I'm coming to the party a little late. But thanks for a very clearly-explained (and with two examples) piece of code. I too have wondered - I've been on SQL since v6.x - how to do a crosstab when the column number is variable. I'll be interested to see PIVOT and UNPIVOT on 2005.

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 2

The article was re-released today, so don't take it personal

About PIVOT and UNPIVOT in SQL Server 2005; I have more use for UNPIVOT than PIVOT...

N 56°04'39.16"
E 12°55'05.25"

Marc Brooks SSC Enthusiast Points: 195 More actions · Answer 3

You could update the table creation to get the datatype of temporary table directly from the source instead of the hard-coded types/sizes. This would be easily done with:

SELECT RowText, ColumnText, Value FROM Source WHERE 1=0 INTO #Aggregates

Which works because the 1=0 clause insures no rows are matched, but the datatypes are set according to the soruce...

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 4

1) Why would I? Then you have no control over the datatype for the aggregated column VALUE (sum).

2) The syntax is wrong. It should be SELECT .. INTO...FROM ... WHERE ...

N 56°04'39.16"
E 12°55'05.25"

rukmang rege SSC Journeyman Points: 97 More actions · Answer 5

Thanks for the post.

Very simple,perfect.Would like to see similar post from you

rukmang rege SSC Journeyman Points: 97 More actions · Answer 6

I was just going through pivot example.As you said in pivot you need to hard wire columns. Can we not built a string of column values dynamically for dynamic pivot generation.Please let me know.

Ref article:

http://www.sqlteam.com/item.asp?ItemID=2368

Jimbo Bantog Valued Member Points: 57 More actions · Answer 7

Hi Peter,

Thanks for an excellent article on pivot tables. I'm not sure if this has been asked before (there is a problem when I clicked on the Next Page link). In the example that you gave, the CellData column is MONEY type, I need this column to be numeric (9,2). I tried this but it's showing 1234 instead of 1234.15. What am I doing wrong here? Please help.

Thanks,

Jim

Wayne West SSC-Insane Points: 22586 More actions · Answer 8

Man, I love that code! I just put it to work and it did the job perfectly!

-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 9

Jimbo Bantog (5/9/2007)
I need this column to be numeric (9,2). I tried this but it's showing 1234 instead of 1234.15. What am I doing wrong here? Please help.

It's hard to tell without knowing which modifications you have made.

N 56°04'39.16"
E 12°55'05.25"

jones.justinw Say Hey Kid Points: 708 More actions · Answer 10

This is a great article. Is it possible to use this to create a view? Thanks!

larry-610474 SSC Enthusiast Points: 125 More actions · Answer 11

Peter,

Your code is so well-written and useful. Thank you!

I would like to add a column at the end for totals and a row at the bottom for totals. Can you steer me in the right direction?

Charles Wannall Ten Centuries Points: 1023 More actions · Answer 12

Outstanding post! I have been annoyed for years that what we did in MS Access with no effort whatsoever took so much fiddling in SQL Server. Talk about logical absurdity!

I didn't know enough to re-do using the column index info and create this beautiful solution.

For those who want to use this: If you just want to copy and paste the thing and use it quickly, you only have to alter the SELECT...FROM...GROUP BY... portion of the posted code and you're running.

Now THAT is good post, given the nature of this topic especially.

Thanks an enormous bunch.

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 13

Thank you!

It's nice to see someone recognize the simpicity of the algorithm structure.

You are absolutely correct about the quick start, to only change the insert thingy with SELECT .. GROUP BY portion.

I also tried to make it easy for future use to add more columns with different kind of aggregations. Just add another portion of dynamic sql and execution.

N 56°04'39.16"
E 12°55'05.25"

Krasavita SSC-Insane Points: 23138 More actions · Answer 14

I used your cross tab procedure and it work's beatifully,but how can I put results into table? Thank you

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 15

INSERT Table1

EXEC dbo.CrossTabProcedure ...

N 56°04'39.16"
E 12°55'05.25"