Pivot Error
Aug 6, 2007
i am fallowing this link
http://sqljunkies.com/Article/705F07C3-69FE-4CAF-8CF8-CADBF145F372.scuk
last 15 th i am unable to understatnd the value to set tht pivotkey value for the output columns
INPUT COLUMNS LEGEND ID
CUSTOMER 861
PRODUCT 864
ID
OUPUT COLUMNS SOUREC COLUMN PIVOT KEY VALUE
CUSTOMER 861 ???????????????????
HamQty 864 ???????????????????
MILKQTY 864 ???????????????????
BEERQTY 864 ???????????????????
BREADQTY 864 ???????????????????
CHESSQTY 864 ???????????????????
HAMQTY CANNOT MAPPED WITH PIVOTKEY VALUE ERROR I AM GETTING
PLEASE HELP ME
View 1 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 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
Apr 21, 2006
Hi :
I am getting the following error message when I am trying to do the Pivot operation.
Msg 265, Level 16, State 1, Line 1
The column name "FirstName" specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument.
Msg 265, Level 16, State 1, Line 1
The column name "LastName" specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument.
Msg 8156, Level 16, State 1, Line 1
The column 'FirstName' was specified multiple times for 'Pvt'.
My SQL Query is:
SELECT UserID,FirstName,LastName
FROM
(
SELECT UserID,FirstName,LastName
FROM Tempreport AS ATR
PIVOT (
MAX(QuestionAnswer)
FOR QuestionText
IN ([CompanyAddress1],[CompanyAddress2],[CompanyName],[Country],[EmailAddress],[FirstName],[LastName],[PhoneNumber],[PostalCode],[Select the Category that best describes your Industry],[Select the Category that best describes your role],[Select the Category that best describes your title],[Town])) as Pvt
) Result (UserID,FirstName,LastName)
Here is the DDL:
INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)
VALUES (200253,'Veerman','Mark',2,'FirstName','Testing1')
INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)
VALUES (200253,'Veerman','Mark',2,'LastName','Testing2')
INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)
VALUES (200253,'Veerman','Mark',2,'CompanyName','Testing3')
INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)
VALUES (200253,'Veerman','Mark',2,'CompanyAddress1','Testing4')
INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)
VALUES (200253,'Veerman','Mark',2,'CompanyAddress2','Testing5')
INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)
VALUES (200253,'Veerman','Mark',2,'Town','Testing6')
INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)
VALUES (200253,'Veerman','Mark',2,'Country','Testing7')
INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)
VALUES (200253,'Veerman','Mark',2,'PostalCode','Testing8')
INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)
VALUES (200253,'Veerman','Mark',2,'PhoneNumber','Testing9')
INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)
VALUES (200253,'Veerman','Mark',2,'EmailAddress','Testing9')
INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)
VALUES (200253,'Veerman','Mark',2,'Select the Category that best describes your Industry','Testing1')
INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)
VALUES (200253,'Veerman','Mark',2,'Select the Category that best describes your role','Other')
INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)
VALUES (200253,'Veerman','Mark',2,'Select the Category that best describes your title','Other')
CREATE TABLE Tempreport
(UserID INT,
LastName VARCHAR(255),
FirstName VARCHAR(255),
OrderNumber INT,
QuestionText VARCHAR(255),
QuestionAnswer VARCHAR(255)
)
Drop table tempreport.
Can anyone please let me know what I am doing wrong and how to fix this error?. looks like I cant select the same column inside the pivot and outside the pivot. In such a case what is my alternative?.
Any help is appreciated.
Thanks
M
View 12 Replies
View Related
Jan 5, 2006
Hey, did someone try to use the pivot transform?
It's not what you would call easy to configure...
In BOL there is the following section:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/55f5db6e-6777-435f-8a06-b68c129f8437.htm
I have followed the instructions there and I get an error when I try to map the new output column to the pivotkey:
"Output column "Yellow" (69) cannot be mapped to PivotKey input column"
So I mapped the new output column to the column holding the quantity values. This worked.
May this be a bug in BOL?
Further on I got some strange behaviour of the pivot transform:
My input is the following CSV-Flatfile:
sk1;sk2;pk1;qty
A;1;Yellow;2
A;1;Green;1
B;1;Yellow;5
A;2;Blue;3
A;2;Green;9
A;1;Green;5
If I omit the last line, everything is fine.
When passing the file as is into the pivot transform the output is:
SK1 SK2 Yellow Green Blue
A 1 2 1 NULL
B 1 5 NULL NULL
A 2 NULL 9 3
A 1 NULL 5 NULL
As you can see the key a;1 is duplicate. The cols SK1 and SK2 are my primary key of the destination table.
When the input is sorted by the first two colums the pivot transform throws an Error:
Error: 0xC02020CF at Pivot w sort, Pivot [39]: Duplicate pivot key value "Green".
I expected to have a sum over all Green for the key A;1. It seems that I have to use an aggregate which in my opinion should
be obsolete here.
As a conclusion I have to say that this task is far away from perfect but not bad for a start.
Regards
Fridtjof
View 3 Replies
View Related
Oct 3, 2007
Hello,
I'm wondering if anyone could shed some light on the following error when selecting multiple items in a pivot table.
€œThe database driver does not include necessary capabilities and cannot be used with Excel. Contact your database administrator or driver vendor.€?
Your help is appreciated!
View 21 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 23, 2014
I would like to have rows presented as columns. That's why I use the PIVOT function at the end.The resultset will be presented in Excel using an external connection to the view.
When I try to save the view I get the error
Msg 4104, Level 16, State 1, Procedure _TEST, Line 47
The multi-part identifier "vk.OppCode" could not be bound.
Code (restricting the columns that I actually have to the relevant columns only):
USE [DBTest]
GO
/****** Object: View [dbo].[_TEST] Script Date: 23-10-2014 17:24:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[code]....
View 1 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
Jun 24, 2015
I am getting this exception "An error occurred while loading the model for the item or data source '<filename.extension'>. Verify that the connection information is correct and that you have permissions to access the data source."" when I click in Power View icon in Power Pivot Gallery SharePoint.I tried steps mention this TechNet article but the issue is still not resolved.URL...
View 2 Replies
View Related
Mar 21, 2006
Hi all.
I moved my OLAP DB to SQL Server 2005, installed Microsoft OLE DB Provider for Analysis Services 9.0 on the client and keep creating Pivot Tables in Excel until...
Grouping - when I try to group couple of members (or even 1 for that matter) I'm getting :
"Intrnal error: An unexpected error occurred (file"mdinfo.cpp", line 3335 function "MDInfo::Init")."
The only reference to mdinfo error I found in OLAP newsgroup where a guy was getting it trying to process the cube. My cube is processed (obviuosly). So, anybody knows what has changed with respect to grouping in PTS?
Michael
View 13 Replies
View Related
Jun 25, 2015
The thing is I can´t make update powerpivot, it raises a generic error. Tried to import into ssas tabular and worked fine.
Is there any way to get a better error detail or debug it someway?
View 4 Replies
View Related
May 21, 2015
I have created data model where I'm taking several sources of Point of Sale data (multiple retailers) and combing them using Power Pivot and a custom calendar. We get data retailer direct, which is mostly in weeks, and data from IRI which is in four week buckets. This does not allow me to use the date intelligence DAX functions. I'm brand new to DAX and my experience starts and ends with Excel. (Diagram view and link to file to come after account verification)Â .
The DAX code for calculating LY Sales is:
=
CALCULATEÂ (
    [Sales $],
    FILTER (
        ALL ( dCalendar ),
        dCalendar[IRIYearNumber]
            = VALUES ( dCalendar[IRIYearNumber] ) - 1
[Code] ....
The filters are to prevent items not on the item table from showing on the report, and the customer filter is to prevent all the sales being rolled together as and extra line (with blank customer) on the report.Â
The error happens when I select two periods that are in different years. When I select the 13 periods on 2014 all is well. But when I add a period from 2015 it throws the error below;
ERROR - CALCULATION ABORTED: Calculation error in measure 'dProducts'[LY Sales $]: A table of multiple values was supplied where a single value was expected.Â
View 5 Replies
View Related
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
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
Jul 20, 2015
I've imported a number of excel sheets into a Power Query Table. All seems to appear ok until I load the data. Of the 15k rows around 2k have a similar error where it cannot convert an integer to type string as below example
Expression.Error: We cannot convert the value 40 to type Text.
Details:
  Value=40
  Type=Type
The columns in question are all of integer type, I've looked through the M query and there is no conversion to string taking placeThe values where we don't get the error are also integers hence the intriguing question is why does the error occur on a subset and not the others. I suspect there is a limit to the number of errors also somewhere internally M query is converting the column to text for some reason.
View 2 Replies
View Related
Jun 11, 2015
When updating a Power Query Source in Power Pivot, I'm getting the following error message:
Basically saying that OLE DB or ODBC-error occured when:
- Connections have been imported from a different workbook or
- the workbook has been created in a newer Version of Excel
None of which is the case here. What can cause this?
System: Virtual machine (VMware) on Windows 2008 R2, Office 2010, 64bit, using temporary profile.
View 5 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
Sep 24, 2007
Hi,
I would like to write a sql query in sql server 2005 to derive a table called tblResult from tblData as follows:
I think I should use unpivot.
tblData
Notice the dates are fields
surname 26 Dec 27 Dec 28 Dec 02 Jan 04 Jan 05 Jan
Brown 100.2 12.65 43.76 3.54 98.12 56.76
Jackson 32.21 98.34 45.54 2.65 65.11 78.86
Peterson 32.23 65.34 88.22 8.34 12.22 87.55
This is what I would like to end up with
tblResult
Surname Date price1 Price2
Brown 26 Dec 100.2 100.2
Jackson 26 Dec 32.21 32.21
Peterson 26 Dec 32.23 32.23
Brown 27 Dec 12.65 12.65
Jackson 27 Dec 98.34 98.34
Peterson 27 Dec 65.34 65.34
Brown 28 Dec 43.76 43.76
Jackson 28 Dec 45.54 45.54
Peterson 28 Dec 88.22 88.22
.
.
.
Thanks
View 1 Replies
View Related
Feb 27, 2008
I have the follwing columns in a table and I have tried to run the pivot column over it without much success.
CustomerID
ColumnID
ColumnValue
ColumnDate
Sample Data
TableA
CustomerID ColumnID ColumnValue ColumnDate
100 1 50 10/10/2007
100 2 Today 10/11/2007
100 3 P.O.Box 10/12/2007
101 1 60 10/10/2007
101 5 77 10/11/2007
101 6 PostOffice 10/12/2007
102 1 88 10/10/2007
I have a look up table which tells me the column name as follows
TableB
ColumnID ColumName
1 Sales
2 DayValue
3 AddressType
4 SalesTarget
5 AverageSales
6 CollectionPoint
My data source cotains the following query
Select CustomerID, b.ColumnName, a.ColumnValue, a.ColumnDate
FROM TableA inner join Table B ON a.ColumnID = b.ColumnID
I then pass this through my pivot expecting the following results as a sample
CustomerID Sales SalesDate DayValue DayValueDate
100 50 10/10/2007 Today 10/11/2007
101 60 10/10/2007 Null Null
102 88 10/10/2007 Null Null
I have set up my Pivot as follows:
CustomerID: PivotUsage=1
ColumnValue: PivotUsage=3
ColumnDate: PivotUsage = 3
ColumnName PivotUsage = 2
I am not getting the following results instead;
CustomerID Sales SalesDate
100 50 10/10/2007
101 60 10/11/2007
100 Today 10/10/2007
101 Null Null
Does anyone know why?
Thanks for any help
View 5 Replies
View Related
