Pivot Example When You Don't Know The Exact Values To Pivot On
Sep 21, 2007
Say, I have the following temporary table (@tbl) where the QuestionID field will change values over time
Survey QuestionID Answer
1 1 1
1 2 0
2 1 1
2 2 2
I'd like to perform a pivot on it like this: select * from @tbl Pivot (min(Answer) for QuestionID in ([1], [2])) as PivotTable
...however, I can't just name the [1], [2] values because they're going to change.
Instead of naming the values like this:
for QuestionID in ([1], [2], [3], [4])
I tried something like this:
for QuestionID in (select distinct QuestionID from @tbl)
but am getting a syntax error. Is it possible to set up a pivot like this:
select * from @tbl Pivot (min(Answer) for Question_CID in (select distinct @QuestionID from @tbl)) as PivotTable
or does anyone know another way to do it?
View 3 Replies
ADVERTISEMENT
May 19, 2006
Hi 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
View 6 Replies
View Related
Jul 8, 2015
I have two data tables:
1) Production data with column headers: Key, Facility, Line, Time, Output
2) Costs data with column headers: Key, Site, Cost Center, Time, Cost
The tables have a common key named obviously as Key. The data looks like this:
Key
Facility
Line
Time
Output
Alpha
I would like to have two pivot tables which I can filter with ONE slicer based on the column Key. The first pivot table shows row labels Facility, Line and column labels Time. Value field is Output. The second pivot table shows row labels Site, Cost Center, and column lables Time. Value field is Cost.How can I do this with Power Pivot? I tried by linking both tables above to a table with unique Keys in PowerPivot and then creating a PivotTable where I would have used the Key from the Keys table.
View 5 Replies
View Related
Oct 13, 2015
Can 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.
View 3 Replies
View Related
Oct 14, 2015
I 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]
View 13 Replies
View Related
Aug 17, 2015
How 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 Related
Mar 11, 2015
I 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])))
View 24 Replies
View Related
Sep 18, 2015
I 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'?
View 3 Replies
View Related
Nov 23, 2015
I 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
View 4 Replies
View Related
Oct 9, 2015
How 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
View 8 Replies
View Related
Apr 13, 2015
I 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?Â
View 2 Replies
View Related
Nov 13, 2015
I am working with a data set containing several years' of monetary values. I have entries for past dates and the associated values, and I also have entries for future dates. I need to populate the values of the future date records with the values from the same date the previous year. Is there any way this can be done in Power Pivot?
View 6 Replies
View Related
Jul 5, 2006
I am using the pivot task to to a pivot of YTD-Values and after that I use derived columns to calculate month values and do a unpivot then.
All worked fine, but now I get this error message:
[ytd_pivot [123]] Error: Duplicate pivot key value "6".
The settings in the advanced editor seem to be correct (no duplicate pivot key value) and I am extracting the data from the source sorted by month.
Could it be a problem that I use all pivot columns (month 1 to 12) in the derived colum transformation and they aren´t available at this moment while data extracting is still going on?
any hints?
Cheers
Markus
View 3 Replies
View Related
Jul 13, 2007
Is it possible to use a expression as a pivot key value?
I need to take data from one table and pivot it into another but what pivots to where is conditional, so I'm wondering if it's possible to say something like
"where Column1 = 'A' and Column2 = 'B' " in the pivot key value.
I'm trying to get it to work but can't, just keep getting the 'No pivot Key found' error.
Thanks
View 5 Replies
View Related
Jun 28, 2006
Is there a way to pivot multiplie values in one 'run'.... In the order of ...
PIVOT ( SUM(DSH_TICKETS) FOR CPRF_NBR IN ([1], [2], [3], [4], [5])
SUM(HALL_CAPACITY) FOR CPRF_NBR IN ([1], [2], [3], [4], [5]) ) PVT
I know that there would be a problem with the headers, but that i could solve by using a second dummy for cprf_nbr and increase it with 10 (ex.)
Until knwo i did the jobg with a case statement, but it would be much nicer with a PIVOT.
View 3 Replies
View Related
Jul 8, 2015
Is 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 Related
Apr 29, 2015
I 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?
View 2 Replies
View Related
Jun 20, 2008
Hi. did know where to put it so...
I just want to know how can i renew my data from a pivot table.
I have a SP in SQL and from that i get my pivot table in excel.How would i renew the data? Do i have to make a job in SQL and if so, what will it do? Just run the SP again? Or do i have to do something with excel? Or is it done automatically when you create your pivot?
Thanks.
View 6 Replies
View Related
Feb 28, 2007
I have a pivot transform that it believe is configured correctly but is not distributing the values accross the columns on the same row. for example.
input:
id seqno codevalue
1 A red
1 B red
2 C blue
2 A green
2 B violet
3 A green
desired output:
id Seq_A Seq_B Seq_C
1 red red null
2 green violet blue
3 green null null
what I am getting:
id Seq_A Seq_B Seq_C
1 red null null
1 null red null
2 green null null
2 null violet null
2 null null blue
3 green null null
I do have the pivot usage for the id column set to 1. I have the pivot usage for seqno column set to 2 and codevalue column set to 3. I have the source column for each of the output columns set to the lineageID of the apprpriate input columns. I have the pivotKey values set for each of the destination columns. A for column Seq_A, B for column Seq_B, C for column Seq_C. All four columns have sortkey positions set; 1 for id, 2 for Seq_A, 3 for Seq_B and 4 for column SEQ_C.
It seems like the id column's pivot usage is not set to 1 like it should but when I check it is 1.
I also have several other pivot transforms in the same data flow and they are working as expected.
I have a suspicion that there is some hidden meta data that is messed up that is over-ridding my settings (just my guess) I have deleted this transform and re-done it several times, checking each configuration value, but still getting the same result.
Need some help or thoughts on making this work.
Thanks
View 8 Replies
View Related
Jan 31, 2014
I'd like to get a extract table result, with a reference id primary key, showing the maximum dates for events and who was responsible for them. I can get the max(date) field in columns using PIVOT, but can't see a way to get the 'who' field without lots of LEFT JOINs.
Here's some test data and code which shows the principle:
CREATE TABLE #t
(
ref INT ,
id INT ,
who VARCHAR(10) ,
checkin DATE
[Code] ....
The result set is:
ref 1 who1 2 who2 3 who3 4 who4
123 2014-01-18 carol 2014-01-18 andy 2014-01-16 bill 2014-01-17 carol
456 NULL NULL 2014-01-17 NULL NULL NULL NULL NULL
Is there some way to avoid all the LEFT JOINs, maybe by using another PIVOT, to produce the same result?
View 4 Replies
View Related
Jun 15, 2015
I have one table
Customer ID; Sort of Transactions;Value
1234;Kit;1
1235;Kit;1
1236;Revenue;30
1235;Kit;2
1238;Revenue;10
1235 ;Kit;1
Looking for a formula for calculated column, which gives me sum of "Kit" for Customers
1234;Kit;1;1
1235;Kit;1;4
1236;Revenue;30;0
1235;Kit;2;4
1238;Revenue;10;0
1235 ;Kit;1;4
View 2 Replies
View Related
Nov 18, 2015
I got a measure that is a calculated member Purchase-Invoice that give the NotBillable. When I query the cube in an Excel pivot I retrieve the zero as well:
I tried to create another calculated member in this way
If
([Measures].[Not_Billed]=0,blank(),[Measures].[Not_Billed])
but Error 2 MdxScript(Billing) (11, 5) The provided expression is not a valid MDX expression.  0 0Â
View 2 Replies
View Related
Sep 21, 2015
We have a client that wants to take the Max value of a count at the Product level, but then for each Brand (Parent of product), sum the Max values for that particular slice. The example below shows the Max sales per month over the last 6 months.Â
Prod1 had it's best month in Month2 (20 units) and Prod2 had its best month in Month4 (30 units). Brandx should roll up to 50 units. Can DAX handle this in a single Calc Measure?
Brandx         50
   Prod1       20
   Prod2       30
Brandy         60
   Prod5       40
   Prod6       20
View 2 Replies
View Related
Aug 6, 2015
I have below script
CREATE TABLE dbo.TestPivot(
CollectionDate DATETIME,
Products VARCHAR(40),
ItemCount INT
)
INSERT INTO dbo.TestPivot
SELECT '4/1/2015','Benz' , 20
[Code] ....
-- Original Output
ProductsApr 2015May 2015Jun 2015
Benz10-800NULL
Toyota5NULL-180
****Required output where ever we have negative values we need to display message Invalid out put message for those negative rows
ProductsApr 2015May 2015 Jun 2015
Benz10Invalid NULL
Toyota5NULL Invalid
View 2 Replies
View Related
Nov 15, 2011
I have 2 excel tables files.One table has info about sales by country, by model, by date...and the other table has units by date.Obviously, the common key is "date"....and by creating a relationship, I can add "units" to my combined resulting table.However, I can't create the relationship. Excel keeps telling me: "the relationship cannot be created because each column contains duplicate values. Select at least one column that contains only unique values".
View 15 Replies
View Related
Jan 25, 2008
I have a pivot transform that pivots a batch type. After the pivot, each batch type has its own row with null values for the other batch types that were pivoted. I want to group two fields and max() the remaining batch types so that the multiple rows are displayed on one row. I tried using the aggregate transform, but since the batch type field is a string, the max() function fails in the package. Is there another transform or can I use the aggragate transform another way so that the max() will work on a string?
-- Ryan
View 7 Replies
View Related
Oct 28, 2015
I have a power pivot with 2 multi valued report filters  student_branch & blood_group. These report filters are used to fetch the data set that contain below result set
student_branch        blood_group      count
Everything works fine. But, what i am looking for , is there any way to show the what are all the report filters that are selected currently by , separated in a separate cell ? below is the image for output reference.
View 2 Replies
View Related
Jun 27, 2007
Hi,
I've a table like,
ID Key Day Amount1 Amount2 Amount3 Amount4
1 100 1 0.00 7.00 0.00 2.00
2 100 2 8.00 0.00 0.00 0.00
3 100 3 0.00 6.00 0.00 0.00
I want to convert this table using Pivot like,
Key Day1 Day2 Day3 Amount
100 0.00 8.00 0.00 Amount1
100 7.00 0.00 6.00 Amount2
100 0.00 0.00 0.00 Amount3
100 2.00 0.00 0.00 Amount4
How can i do this? Pls explain me with query.
Thanks in advance
Jasmeeta
View 4 Replies
View Related
Apr 4, 2008
Hello all,
I am having some issues. Let me set the scene for you:
Table1:
Table2:
I hope you can get what's going on from those pics.
I'm trying to construct a datatable. the user specifies a date range, a bin no and a screen no. From that information, for each date in the date range, i want to show the added weight of each sieve for that particular bin and screen.
I hope this is at least a little clear.I really could use some help on this. thanks.
View 1 Replies
View Related
Apr 25, 2008
I have a table structure like this
Vendor Date Item
A1 04/21/2008 T1
A1 04/21/2008 T1
A1 04/21/2008 T1
C1 04/21/2008 T1
C1 04/21/2008 T1
D1 04/21/2008 T1
A1 04/11/2008 T1
A1 04/11/2008 T1
B1 04/11/2008 T1
C1 04/11/2008 T1
D1 04/11/2008 T1
D1 04/11/2008 T1
A1 04/1/2008 T1
A1 04/1/2008 T1
B1 04/1/2008 T1
B1 04/1/2008 T1
C1 04/1/2008 T1
D1 04/1/2008 T1
I want the result like
(date calculated from todays date 04/25/2008)
Vendor Count of item Count of item Count of item
last 10days last 20days last 25days
A1 3 5 7
B1 0 1 3
C1 2 3 4
D1 1 3 4
How can i write the query for this. Please help me ,
Mathew
View 11 Replies
View Related
Mar 31, 2008
hi,
how can I use a select statement in the IN part of the pivot sql instead of ([1],[2],[3])
eg,
I want to do :
PIVOT ( sum(numbers) FOR surname IN (Select surname from person)
that doesn't work though.
any ideas.?
thank you
View 1 Replies
View Related
Dec 30, 2006
Hi everyone,I have 3 tables:Telbook(Id:int,Name:char,address:char,comment:ntex t,owneruserid:int),PK:idTelNumbers(telbookid:int,telno:char,telNotype:int, syscode:int),PK:syscode,F*K:telNumbers.telbookid=t elbook.idTeltypes(teltypes:char,fk:int),FK:telnumbers.telno type=teltypes.fkThe question is here that I can create a query which results are:(id,Name,telno,telnotype,teltypes,address,comment) (4,nassa,091463738,2,Mobile,XXX,Null)(4,nassa,071163738,1,Tellphone,XXX,Nul)But,I want a query which shows the results in a way below:(id,Name,tellephone,mobile,Fax,e-mail,address,comment)(4,nassa,071163738,091463738,Null,Null,XXX,Null)I run SQL server 2005, and I want to use PIVOT but I dnt know how!.Thanks,Nassa
View 3 Replies
View Related
Apr 23, 2008
ID SUBID Count
1 4 23
1 5 10
1 6 5
2 3 4
2 2 7
Is it possible to create a pivot table to put subid where ID =1 to rows and subid where ID =2 to column
3 2
4
5
6
But I am not sure if it's possible to do count with this structure?
Any input would be appreciated..
View 5 Replies
View Related