August 9, 2010 at 1:31 pm
I am trying to learn how to build a pivot table in SQL Server 2005. the table definition I am currently working on is:
CREATE TABLE #Mytable
[KEY_PATIENT_2] [numeric](16, 3) NULL,
[Date_Completed] [datetime] NULL,
[Print_Name] [varchar](35) NULL,
[Field] [numeric](18, 0) NULL,
[Value_Text] [varchar](30) NULL
The attachment is an excel spreadsheet with the data in two spreadsheets:
1. TestData = 25 rows of test data
2. Sheet1 = the desired result
The test contains 2 columns with information to group and 8 columns to be presented horizontally. The number of columns for either may vary in a production mode, but any help using this sample would be greatly appreciated.
Please let me know if there is any information I have omitted or if there are any questions.
Thanks in advance.
August 9, 2010 at 1:46 pm
PIVOT isn't really that great of a tool if you either have to pivot by more than one column or if your target columns aren't fixed.
You should have a look at the CrossTab article referenced in my signature to learn how to return the required (static number of) columns. Once you're familiar with the concept move on to the next article "DynamicCrossTab" and you'll see how to modify your query to handle a flexible number of target columns.
June 21, 2012 at 10:59 pm
Someone pls help me....
how to find grand total in a separate field using pivot table?
June 22, 2012 at 7:50 am
Angitha (6/21/2012)
Someone pls help me....how to find grand total in a separate field using pivot table?
You should start your own thread instead of jumping onto another one.
Before you start your thread you should take a look at the first link in my signature about best practices when posting questions. You will need to provide more details before anybody can do much to help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply