Pivot Table / CUBE ?
Jan 17, 2004Please see the attached file
View 5 Replies
ADVERTISEMENT
Power Pivot :: Aggregating Time Periods In Cube-member / Cube-value Formulas?
Aug 23, 2015I am just starting out using CUBEMEMBER/CUBEVALUE formulas in excel linked into a sql olap db - using this method for some custom reports where pivot tables are not suitable.
The time dimension values include Months, Quarters and Years and the CUBEMEMBER formulas like
=CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[1].&[1]") work fine - 1st quarter 1st month etc.
Is there a straightforward notation to aggregate months or do I need to use a plus sign to add a number of CUBEMEMBER formulas together.In other words - Is there an easier way of for say jan to july 2015 totals than
=CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[1]") + (CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[2]")) + (CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[3].&[7]"))
I haven't tested this but have assumed it works but a bit long and clumsy.
Power Pivot :: Auto Refresh Excel Table (Not Pivot Table) Using Data Source
Jul 8, 2015Is it possible to generate automatic refresh of excel 2013 table which displays some table of a power pivot model on file open?? I dont want to use pivottable (which supports this ...)
View 2 Replies View RelatedSSMS Express: Using PIVOT Operator To Create Pivot Table - Error Messages 156 && 207
May 19, 2006Hi all,
In MyDatabase, I have a TABLE dbo.LabData created by the following SQLQuery.sql:
USE MyDatabase
GO
CREATE TABLE dbo.LabResults
(SampleID int PRIMARY KEY NOT NULL,
SampleName varchar(25) NOT NULL,
AnalyteName varchar(25) NOT NULL,
Concentration decimal(6.2) NULL)
GO
--Inserting data into a table
INSERT dbo.LabResults (SampleID, SampleName, AnalyteName, Concentration)
VALUES (1, 'MW2', 'Acetone', 1.00)
INSERT €¦ ) VALUES (2, 'MW2', 'Dichloroethene', 1.00)
INSERT €¦ ) VALUES (3, 'MW2', 'Trichloroethene', 20.00)
INSERT €¦ ) VALUES (4, 'MW2', 'Chloroform', 1.00)
INSERT €¦ ) VALUES (5, 'MW2', 'Methylene Chloride', 1.00)
INSERT €¦ ) VALUES (6, 'MW6S', 'Acetone', 1.00)
INSERT €¦ ) VALUES (7, 'MW6S', 'Dichloroethene', 1.00)
INSERT €¦ ) VALUES (8, 'MW6S', 'Trichloroethene', 1.00)
INSERT €¦ ) VALUES (9, 'MW6S', 'Chloroform', 1.00)
INSERT €¦ ) VALUES (10, 'MW6S', 'Methylene Chloride', 1.00)
INSERT €¦ ) VALUES (11, 'MW7', 'Acetone', 1.00)
INSERT €¦ ) VALUES (12, 'MW7', 'Dichloroethene', 1.00)
INSERT €¦ ) VALUES (13, 'MW7', 'Trichloroethene', 1.00)
INSERT €¦ ) VALUES (14, 'MW7', 'Chloroform', 1.00)
INSERT €¦ ) VALUES (15, 'MW7', 'Methylene Chloride', 1.00)
INSERT €¦ ) VALUES (16, 'TripBlank', 'Acetone', 1.00)
INSERT €¦ ) VALUES (17, 'TripBlank', 'Dichloroethene', 1.00)
INSERT €¦ ) VALUES (18, 'TripBlank', 'Trichloroethene', 1.00)
INSERT €¦ ) VALUES (19, 'TripBlank', 'Chloroform', 0.76)
INSERT €¦ ) VALUES (20, 'TripBlank', 'Methylene Chloride', 0.51)
GO
A desired Pivot Table is like:
MW2 MW6S MW7 TripBlank
Acetone 1.00 1.00 1.00 1.00
Dichloroethene 1.00 1.00 1.00 1.00
Trichloroethene 20.00 1.00 1.00 1.00
Chloroform 1.00 1.00 1.00 0.76
Methylene Chloride 1.00 1.00 1.00 0.51
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
I write the following SQLQuery.sql code for creating a Pivot Table from the Table dbo.LabData by using the PIVOT operator:
USE MyDatabase
GO
USE TABLE dbo.LabData
GO
SELECT AnalyteName, [1] AS MW2, AS MW6S, [11] AS MW7, [16] AS TripBlank
FROM
(SELECT SampleName, AnalyteName, Concentration
FROM dbo.LabData) p
PIVOT
(
SUM (Concentration)
FOR AnalyteName IN ([1], , [11], [16])
) AS pvt
ORDER BY SampleName
GO
////////////////////////////////////////////////////////////////////////////////////////////////////////////////
I executed the above-mentioned code and I got the following error messages:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'TABLE'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AnalyteName'.
I do not know what is wrong in the code statements of my SQLQuery.sql. Please help and advise me how to make it right and work for me.
Thanks in advance,
Scott Chang
Power Pivot :: Force Measure To Be Visible For All Rows In Pivot Table Even When There Is No Data?
Oct 13, 2015Can I force the following measure to be visible for all rows in a pivot table?
Sales Special Visibility:=IF(
  HASONEVALUE(dimSalesCompanies[SalesCompany])
  ;IF(
    VALUES(dimSalesCompanies[SalesCompany]) = "Sales"
    ;CALCULATE([Sales];ALL(dimSalesCompanies[SalesCompany]))
    ;[Sales]
  )
  ;BLANK()
)
FYI, I also have other measures as well in the pivot table that I don't want to affect.
Analysis :: Hidden Cube Measure Groups Are Shown In Excel Pivot (Show Fields Related To) Drop Down
Jun 18, 2015We have hidden few measure groups in cube for time being, where Users can browse the cube with Excel pivot. But, All these measures can be seen from Excel pivot in 'Show fields related to' drop down.
Need to remove the hidden measure groups from showing in Excel pivot and to remove 'All' option in 'Show Fields related to', So that users may not get confused by seeing all the measures. Can we achieve this.
Power Pivot :: ALL DAX Function Not Overriding Filter On Pivot Table
Oct 14, 2015I have a simple pivot table (screenshot below) that has two variables on it: one for entry year and another for 6 month time intervals. I have very simple DAX functions that count rows to determine the population N (denominator), the number of records in the time intervals (numerator) and the simple percent of those two numbers.
The problem that I am having is that the function for the population N is not overriding the time interval on the pivot table when I use an ALL function to do so. I use ALL in other very simple pivot tables to do the same thing and it works fine.
The formula for all three are below, but the one that is the issue is the population N formula. Why ALL would not work, any other way to override the time period variable on the pivot table.
Population N (denominator):
=CALCULATE(COUNTROWS(analyticJudConsist),ALL(analyticJudConsist[CurrentTimeInCare1]))
Records in time interval (numerator):
=COUNTROWS(analyticJudConsist)
Percent:
=[countrows]/[denominatorCare]
Power Pivot :: How To Apply Min Formula Under New Measure Within A Pivot Table
Aug 17, 2015How can I apply "Min" formula under a "new measure" (calculated field) within a pivot table under Power pivot 2010?Can see that neither does it allow me to apply "min" formula directly "formula box" nor could find any other option.Intent formula: "=Min(1,sum(a:b))" this isn't allowed so all I can do is "=sum(a:b)".
View 3 Replies View RelatedPower Pivot :: Displaying Cumulating Numbers In A Pivot Table When There Is No Value
Mar 11, 2015I have simple pivot table (below screenshot with info redacted) that displays a population number ("N" below), this is the denominator, a cumulative numerator number (below "#") and a simple cumulative percent that just divides the numerator by the denominator. It cumulates from top to bottom. The numerator and percent are cumulative using the below functions. There are two problems with the numerator and percent:
1. When there is not a number for the numerator, there is no value displayed for both the numerator and the percent..There should be a zero displayed for both values.
2. When there has been a prior number for the numerator and percent (for a prior month interval) but there is no number for the numerator in the current month interval, the prior month number and percent are not displayed in the current month interval--see the 3rd yellow line, this should display "3" and "16.7%" from the second yellow line.Here is the formula for the numerator:
=CALCULATE(count(s1Perm1[entity_id]),FILTER(ALL(s1Perm1[ExitMonthCategory]),s1Perm1[ExitMonthCategory] <= MAX(s1Perm1[ExitMonthCategory])))
Here is the formula for the percent:
=(CALCULATE(countrows(s1Perm1),FILTER(ALL(s1Perm1[ExitMonthCategory]),s1Perm1[ExitMonthCategory] <= MAX(s1Perm1[ExitMonthCategory]))))/(CALCULATE(COUNTROWS(s1Perm1),ALL(s1Perm1[Exit],s1Perm1[ExitMonthCategory])))
Power Pivot :: Measures Not Reflected In Pivot Table
Sep 18, 2015I have data in my Powerpivot window which was generated by a sql query. This data includes a field named 'Cost' and every row shows a value for 'Cost' greater than zero. The problem is that when I display this data in the pivot table all entries for Cost display as $0. At first I thought that maybe Cost was set to a bogus data type (such as 'text) but it is set to ''Decimal Number' so that's not the problem.Â
What is happening and how do I fix it so that my pivot table reflects the values for 'Cost'?
Power Pivot :: Difference Between Two Pivot Table Sums?
Nov 23, 2015I have a data table that contains budget and actual data by month. Â I use the data to create a pivot that shows actual results next to budgeted results. Â I need a column that shows that variance between those columns. Â I think my issue is that the "Type" field contains actual and Budget. Â I sum on "Type". Â I can't seem to create a sum since those items are in the same field or am I missing something?
Table design
Month|Division|Subdivision|Type|Dept|Rate|Units|Amount
October|DC|Day|Budget|125|10.00|100|1000
October|DC|Day|Actual|125|10.00|110|1100
Output Design
DC
DAY
Actual
Budget
125 AvgOfRate
AvgOfRate
SumOfUnits
SumOfUnits
SumOfAmt
SumOfAmt
Power Pivot :: Slicer And Pivot Table Value Order
Oct 9, 2015How to get a list of values to actually display in correct order in either a slicer or when on an axis on a pivot table?
I currently have the below list and have tried to add a preceding numeric (ex. "1. <=0") or preceding blank space, neither of which is visually great. Is there another way?
<= 0
1 - 6
7 - 12
13 - 18
19 - 24
25 - 30
31 - 36
37 - 42
43 - 48
49 - 54
55 - 60
61 - 66
67 - 72
73 - 78
79 - 84
85 - 90
91 - 96
97 - 102
> 102
Power Pivot :: Printing From Pivot Table With Slicers
Apr 13, 2015I am using excel 2010 and creating pivot table from Power Pivot. I created a pivot table with department slicers. All is good, the problem I am having is whilst in an unfiltered position (ALL) of the slicers (departments) I get 200 pages, now when I click on a given department with say 10 pages, I still get the same 200 pages with the first 10 pages showing the data from the clicked department and 190 blank pages.
All I want is to get a WYSIWYG (What you see is what you get) of what is on the screen as my print, but I am getting extra blank pages right after the data. Â How do I resolve this.
Below are the steps I go thru to printÂ
1. Select slicers in unfiltered position (ALL)
2. Select entire pivot table
3. Select Page layout and select print area.
4. Save
5. Click on Print Preview to preview the print
6. Click on a given department in the slicer and repeat item 5, but this gives me blank pages after the data.
Do I need any other step?Â
Power Pivot :: Pivot Table Loses Text Wrapping For Text Data Upon Refresh
Apr 29, 2015I have a pivot table that connects to our data warehouse via a PowerPivot connection. Â The data contains a bunch of comment fields that are each between 250 and 500 characters. Â I've set the columns in this pivot table to have the 'Wrap Text' set to true so that the user experience is better, and they can view these comment fields more clearly.
However, whenever I refresh the data, the text wrapping un-sets itself. Â Interestingly, the 'Wrap Text' setting is still enabled, but I have to go and click it, then click it again to actually wrap the text. Â This is very burdensome on the user, and degrading the experience.
Any way to make this text wrapping stick so that I don't have to re-set it every time I refresh the data?
Power Pivot :: Temp Table Or Table Variable In Query (not Stored Procedure)?
Jul 19, 2012I don't know if it's a local issue but I can't use temp table or table variable in a PP query (so not in a stored procedure).
Environment: W7 enterprise desktop 32 + Office 2012 32 + PowerPivot 2012 32
Simple example:
   declare @tTable(col1 int)
   insert into @tTable(col1) values (1)
   select * from @tTable
Works perfectly in SQL Server Management Studio and the database connection is OK to as I may generate PP table using complex (or simple) queries without difficulty.
But when trying to get this same result in a PP table I get an error, idem when replacing table variable by a temporary table.
Message: OLE DB or ODBC error. .... The current operation was cancelled because another operation the the transaction failed.
Power Pivot :: Measure Results Limited By Fact Table Dates Instead Of Date Table
Sep 17, 2015I cannot create a measure that returns results for dates that do not exist in the fact table despite the fact that the components included in the measure contain valid results for these same dates.Creature a measure that counts the number of days where the "stock qty" is below the "avg monthly sales qty for the last 12 months" (rolling measure).Here is the DAX code I have tried for the measure (note that filter explicitly refers to the date table (called Calendar) and not the fact table):
StkOutCnt:=CALCULATE (
COUNTROWS ( VALUES ( Calendar[DateKey] ) ),
FILTER (
Calendar,
[Stock qty] < [Avg Monthly Sales Qty L12M@SKU]
)
)
Below you can see the sub measures (circled in red) are giving results for all days in the calendar.Highlighted in yellow are dates for which the StkOutCnt measure is not returning a result. Having investigated these blank dates, I am pretty confident that they are dates for which there are no transactions in the fact table (weekends, public holidays etc...).why I am getting an "inner join" with my fact table dates despite the fact that this is not requested anywhere in the dax code and that the two sub measures are behaving normally?
Analysis :: Creating Cube With AMO - Cube Has No Measure Groups?
May 19, 2015I have problems creating a cube with AMO.
I can add the cube to the database object and fill it with dimensions and a measuregroup (see code below).
If I call cube.Update() it says something like "Error in meta data manager. Cube has no measuregroups." (getting the message in german language)
The error in Microsoft.AnalysisServices.OperationException.Results.Messages is -1055653629
I can't find any documentation about this (or any other) error code in Microsoft documentation.
Here's my Code:
Cube newCube = database.Cubes.Add("MyCube","MyCube");
newCube.Language = 1031;
newCube.Collation = "Latin1_General_CI_AS";
CubeDimension dim = newCube.Dimensions.Add("dim1","dim1","dim1");
CubeAttribute attrib = dim.Attributes.Find("dim1Attr1");
[code]....
Cube With More That One Facts Table
Apr 1, 2007Hello,
I made an illustration, that describes the szenario. There is one Master, one dimension and a second facts table. The Master Table holds up all objects. Sometimes I have additional Information for some objects in Master Table. They are stored in extra tables like the table Optional_facts_for_villas.
My question: How can I combine these two fact tables in Cube Designer without doing a Full outer Join in my Database?
The reason why I want it this way, not all optional_fact_tables are necessary now for OLAP, but maybe later. So, I thought, easiest way would be, adding them later to the cube, without changing the database.
Sorry for my english, its not the best :)
Silas
Fact Table In OLAP Cube ?
Jan 1, 2004hello,
Iam trying to build OLAP cubes in MS SQL Server 2000.But all the tutorials/docs mention about fact tables & dimensions.
Can I get some good tutorials on how to create fact tables to build OLAP cube ?
Also, which OLEDB provider to be used for MS SQL Server while creating OLAP Datasource ?
Thanks in advance & wishing u a prosperous new year too.
Analysis :: Create Cube With 2 Table
May 6, 2015i want to create cube withe 2 fact tables. is it possible?? if it is could you show me how !!
View 2 Replies View RelatedAnalysis :: Only One Fact Table Per Cube?
Nov 22, 2015Is it correct to say that for each cube you can have only one Fact Table? I am having a funny dispute just now.Â
According my experience I never built cubes with more than one fact table, if I want take data from more than one table I write a view and I use it as Fact table...but a cube with two or three fact tables? Never tried..
GetSchemaDataset For Cube Restriction With Space In The Cube Name
May 27, 2008When I make a call to GetSchemaDataset with a restriction of a cube name with a space in the name of the cube the call fails. Following is a sample of the code: adoRestriction = new AdomdRestriction("CATALOG_NAME", "Contoso Telecom_Contoso"); adoRestrictions.Add(adoRestriction); dataSet = conn.GetSchemaDataSet("MDSCHEMA_CUBES", adoRestrictions); I am running SQL Server 2005 Analysis Services SP2. Is there some way to qualify the cube name in the restriction or is this just a bug? Thanks.
View 1 Replies View RelatedExporting Table And Column Descriptions From Database To Cube
May 22, 2008
Can somebody tell me how to transfer table and column descriptions from SQL 2005 database to dimensions and attributes in Analysis Services?
Thanks!
Analysis :: SSAS Multidimensional Cube On A Single Table
May 7, 2015I have only 1 denormalized table that is being used in a SSAS Tabular model(which is about 3GB). I am doing a POC to convert it into a SSAS Multidimensional and explore it.
Table1
-----------------------
StoreName
StoreDesc
ItemName
ItemDesc
Qty1
Cost1
Price1
Amount1
1st Question) I am seeing that there is no Primary Key(unique key) in the current denormalized table. (Tabular Model didnt require any primary key). But i think for Multidimensional the key is mandatory? Should i generate a composite key myself in a Named Query based on this table(in the DSV)?
2nd Question) What is the best way to design my Multidimensional Cube/Dimensions based on this single table?
Say if i comeup with a Composite Primary Key called (PK_ID) . Should i be splitting up my facts / dimensions in my DSV using Named Queries similar to below(Using the same PK for my dimension tables also?)
a) FactTable = Select PK_ID, Qty1,Cost1,Price1,Amount1 from Table1
b) StoreDim = Â Select PK_ID, StoreName,StoreDese from Table1
c) ItemDim  =  Select PK_ID, ItemName,ItemDesc from Table1
Would this work?
Do I Need To Fully Process The CUBE, If Structural Change To Fact Table Happens
Mar 31, 2007I have a requirement.
I have a CUBE in SQL 2000. I need to change the structure of Fact Table and i need to add one more dimension to my CUBE.
What are the problems will arise if i do this. i need to Fully process the CUBE?
PLS help me
Reg:SSIS Package To Transfer Data Between SQL Table And Olap Cube
Apr 2, 2008
Hi All,
can anybody help me in creating the SSIS package to transfer the data from SQL table in database engine to OLAP cube in Analysis services
Thanks in Advance.
Archana
Pivot Table
Apr 22, 1999Perhaps I was dreaming, but I thought I remembered reading in some SQL Server 7 propaganda that PIVOT TABLE was added. The idea is, a cool syntax that would turn a number of rows into the same number of columns, and automatically transpose the values for you.
This is not a data-mining application. It's more a hyper-normalizing operation. We define products as a collection of attributes that are stored in rows, not columns. A single product is therefore one row from a Product Header table and N rows from the Product Attributes table. Products can have widely differing numbers of attributes.
I know you can write a query that will grab N rows and turn them into N columns, but I don't want to have to write one for every product type (meaning for N, O, P, Q, R and S attributes).
I just looked in one of my SQL Server 7 books and found nothing on Pivot Table, so maybe I was dreaming.
Failing this cool new command, do you know of a general solution to this type of problem?
Thanks in advance,
Arthur Fuller
Pivot One Of The Table
Dec 24, 2013I have to pivot one table to get the result in correct format. Here is an example as I am not sure how to explain it. If there is another way instead of pivot, let me know.
Table1
ID | Name |
1 | Joe |
2 | Ron |
Table2
Table1_ID | Language
1 | English
1 | ASL
2 | Spanish
2 | English
2 | French
I need the two tables joined and table2 to be pivoted to get the desired result as shown below. Third column for language is left off as I am limited to two columns.
Result
ID | Name | Language 1| Language 2
1 | Joe | English | ASL
2 | Ron | Spanish | English
Pivot Table
Sep 8, 2006I have read RobVolks article on creating a Pivot table. I have copied his code into a stored procedure. He then explains how to call this code see below. But how do I actually run the EXECUTE statement in my asp.net webpage ?
EXECUTE(crosstab) 'select title from titles inner join sales on (sales.title_id=titles.title_id)
group by title', 'sum(qty)','stor_id','stores'
http://www.sqlteam.com/item.asp?ItemID=2955
Need Help With A Pivot Table
Feb 1, 2008Hello All. I have a Pivot query that I am working on, and I have it working, however I want to add a 'totals' type column at the end. For some reason I can't get it to work
Here is my code
SELECT [July] AS July, [August] AS August,
[September] AS September, [October] AS October, [November] AS November, [December] AS December,
[January] AS January
FROM
(SELECT TimeStamp FROM tMaster WHERE ServicesArea = 'US') AS P
PIVOT
(Count(TimeStamp)
FOR TimeStamp IN
([July], [August], [September], [October], [November], [December], [January]))
AS Pvt
Any Thoughts?
Also, I was wondering if there was a way, I could add different calculations per row?
For instance, the first row would be counts per month of total # of good orders and the second row would be the total # of bad orders, the 3rd row being a total count of incomplete orders etc etc. Is there a way to do that?
Pivot Table
Jun 12, 2007I'm trying to extract some data from an ssas 2005 cube to build a report with ssrs 2005.
The report should have a variable number of columns and a fixed number of rows ... so I think I cannot use a table control but I must use a matrix control ...
So I would group the column for the fiscal month and the row for the measure name or measure caption ... and put the measure value inside the matrix.
Like the following
month 1
month 2
measure 1
xxx
xxx
measure 2
xxx
xxx
measure 3
xxx
xxx
To do that I should run a query to extract data in the following form ...
fiscal month
mesaure name
measure value
month 1
measure 1
xxx
month 1
measure 2
xxx
month 1
measure 3
xxx
month 2
measure 1
xxx
month 2
measure 2
xxx
month 2
measure 3
xxx
The problem is ... when running an mdx query on reporting services I need to put the meausure only on the columns ...
so any idea on how can I extract data from ssas in that form ??
Cosimo
Not Exactly Pivot Table
Oct 31, 2007I have the following table:
UserID Pet (Range: Cat, Dog, Fish)
------------------
1 Cat
1 Dog
1 Fish
2 Dog
3 Fish
......
I want the following result:
UserID(as PK) Cat Dog Fish
----------------------------------
1 Cat Dog Fish
2 (null) Dog (null)
3 (null) (null) Fish
.......
Does this have anything to do with pivoting table? How can I achieve this? Thank you.
Where Clause With Pivot Table?
Nov 3, 2003I am using this article for a dynamic pivot table: http://www.sqlteam.com/item.asp?ItemID=2955
I am trying it in Query analyzer and when I try to put a Where clause into the query I get an error: Invalid column name 'CCFIF'.
I run just the Select statement and it returns values without error.
What am I doing wrong??
declare @select varchar(8000), @sumfunc varchar(100),@pivot varchar(100), @table varchar(100)
select @select='SELECT dbo.ActionCodes.Name AS Action FROM dbo.Productivity_CCF LEFT OUTER JOIN dbo.ActionCodes ON dbo.Productivity_CCF.ActionID = dbo.ActionCodes.ID
LEFT OUTER JOIN dbo.UserInfo ON dbo.Productivity_CCF.UserID = dbo.UserInfo.ID WHERE (dbo.UserInfo.LocationID = CCFIF) GROUP BY dbo.UserInfo.UserName, dbo.ActionCodes.Name'
select @sumfunc= 'COUNT(ActionID)'
select @pivot='UserName'
select @table= 'UserInfo'
DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE '
+ @pivot + ' Is Not Null')
SELECT @sql='', @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )
SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )
WHEN 0 THEN '' ELSE '''' END
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pivot'
SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' +
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN '
+ @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM ##pivot
DROP TABLE ##pivot
SELECT @sql=left(@sql, len(@sql)-1)
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')
EXEC (@select)
SET ANSI_WARNINGS ON
Thanks!