PIVOT TABLE Query !!
Nov 7, 2006
hi ,
is it possible to do a pivot , where the number of columns is dynamic...i.e
i dont know how many rows will be selected , and i want to pivot them and insert into
a new (temp/tabletype)table...obv i dont know how many columns i need....
somethin like the example of books online pasted below , consider here that i need data for
all employees (distinct empid) , then pivot it, for that i'll need 'select distinct empid
from emp' in the pivot syntax 'FOR EmployeeID IN' .
pls tell me if such thing is possible or there is a turnaround for my problem...
SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY VendorID
View 8 Replies
ADVERTISEMENT
Jul 19, 2012
I 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.
View 11 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 16, 2008
I have a pivot table query I am running and wanted to find out if there was a way to pull in the dates like getdate() - 12 months, getdate() - 11 months, etc. instead of hard coding the dates.
Here is my query
SELECT Client, [4/1/2007 12:00:00 AM] AS Month1, [5/1/2007 12:00:00 AM] AS Month2, [6/1/2007 12:00:00 AM] AS Month3, [7/1/2007 12:00:00 AM] AS Month4,
[8/1/2007 12:00:00 AM] AS Month5, [9/1/2007 12:00:00 AM] AS Month6, [10/1/2007 12:00:00 AM] AS Month7, [11/1/2007 12:00:00 AM] AS Month8,
[12/1/2007 12:00:00 AM] AS Month9, [1/1/2008 12:00:00 AM] AS Month10, [2/1/2008 12:00:00 AM] AS Month11, [3/1/2008 12:00:00 AM] AS Month12,
[4/1/2008 12:00:00 AM] AS Month13, Engineer
FROM (SELECT Client, DollarsBilled, SlipDates, Engineer
FROM dbo.MonthlyClientBillables) p PIVOT (SUM(DollarsBilled) FOR SlipDates IN ([4/1/2007 12:00:00 AM], [5/1/2007 12:00:00 AM],
[6/1/2007 12:00:00 AM], [7/1/2007 12:00:00 AM], [8/1/2007 12:00:00 AM], [9/1/2007 12:00:00 AM], [10/1/2007 12:00:00 AM], [11/1/2007 12:00:00 AM],
[12/1/2007 12:00:00 AM], [1/1/2008 12:00:00 AM], [2/1/2008 12:00:00 AM], [3/1/2008 12:00:00 AM], [4/1/2008 12:00:00 AM])) AS pvt
View 31 Replies
View Related
Jun 17, 2015
I pulled some examples of using a subquery pivot to build a temp table, but cannot get it to work.
IF OBJECT_ID('tempdb..#Pyr') IS NOT NULL
DROP TABLE #Pyr
GO
SELECT
vst_int_id,
[4981] AS Primary_Ins,
[4978] AS Secondary_Ins,
[code]....
The problems I am having are with the integer data being used to create temp table fields. The bracketed numbers on line 7-10 give me an invalid column name error each. In the 'FOR', I get another error "Incorrect syntax near 'FOR'. Expecting '(', or '.'.". Â The first integer in the "IN" gives me an "Incorrect syntax near '[4981]'. Expecting '(' or SELECT". Â I will post the definitions from another effort below.
CREATE TABLE #Pyr
(
vst_int_idINTEGERNOT NULL,
--ivo_int_idINTEGERNOT NULL,
--cur_pln_int_idINTEGERNULL,
--pyr_seq_noINTEGERNULL,
[code]....
SQL Server 2008 R2.
View 3 Replies
View Related
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
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
Apr 8, 2014
I want to convert the data from Original Table to Reporting View like below, I have tried but not get success yet.
Original Table:
================================================================
Id || Id1 || Id2 || MasterId || Obs ||Dec || Act || Status || InstanceId
================================================================
1 || 138 || 60 || 1 || Obs1 ||Dec1 || Act1 || 0|| 14
2 || 138 || 60 || 2 || Obs2 ||Dec2 || Act2 || 1|| 14
3 || 138 || 60 || 3 || Obs3 ||Dec3 || Act3 || 1|| 14
4 || 138 || 60 || 4 || Obs4 ||Dec4 || Act4 || 0|| 14
5 || 138 || 60 || 5 || Obs5 ||Dec5 || Act5 || 1|| 14
View For Reporting:
Row Header:
Id1 || Id2 || MasterId1 || Obs1 ||Desc1 ||Act1 ||StatusId1||MasterId ||Obs2 ||Desc2 ||Act2 ||StatusId2 ||MasterId3||Obs3 ||Desc3 ||Act3 ||StatusId3||MasterId4||Obs4||Desc4 ||Act4 ||StatusId4 ||MasterId5||Obs5 ||Desc5 ||Act5 ||StatusId5||InstanceId
Row Values:
138 || 60 || 1 || Obs1 ||Desc1 ||Act1 ||0 ||2 ||Obs2 ||Desc2||Act2 ||1 ||3 ||Obs3||Desc3 ||Act3 ||2 ||4||Obs4||Desc4 ||Act4 ||0 ||5 ||Obs5 ||Desc5 ||Act5 ||1 ||14
View 6 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
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
Sep 17, 2015
I 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?
View 6 Replies
View Related
Feb 19, 2008
consider the following table:
name , TaskDate
john , 01/01/2006
john , 01/03/2005
steve , 01/05/2006
i want to build a select statement that gives result like the following:
name , JanuaryTotal , FebruaryTotal
john , 150 , 110
steve , 170 , 50
so the result will be total tasks in specific month, is it doable in one select or should i turn around using hash tables ???
View 6 Replies
View Related
Mar 8, 2004
Hi, I have the following query that kinda does what i want
SELECT ABTANumber, TourOperator, ReportStatus,
COUNT(*) AS Counter
FROM (SELECT ABTANumber, TourOperator, r.ReportStatus FROM bookingdetails bd LEFT JOIN report r ON bd.Id = r.BookingDetailsId) a
GROUP BY ABTANumber, TourOperator, ReportStatus
This is what it displays
TourOp|ReportStatus|Count
JMC..... Fail.............. .10
JMC..... Pass..............621
JMC..... Warn.............5
SET..... Fail.............. .12
SET..... Pass..............621
SET..... Warn.............3
But what i want to display is this
TourOp|Pass|Fail|Warn
JMC......621 ..10....5
SET..... 621...12....3
I'm really stuck on this and would appreciate any help
thanks
Mark
View 2 Replies
View Related
Mar 30, 2006
hi,
I've table TAB_QUESTION:
ID_QUESTION..........VALUE_ID..........NUM_ANSWER. .....DESC_ANSWER
XB1.................1...................0......... .....YES
XB1.................2...................0......... .....NO
XB1.................3...................1......... .....GOOD
XB1.................4...................0......... .....SUFF
XB1.................5...................1......... .....NO_GOOD
XB1.................6...................0......... .....NR
XB1.................7...................0......... .....NN
YB1.................1...................1......... .....YES
YB1.................2...................2......... .....NO
YB1.................3...................3......... .....GOOD
YB1.................4...................0......... .....SUFF
YB1.................5...................3......... .....NO_GOOD
YB1.................6...................2......... .....NR
YB1.................7...................1......... .....NN
ZC1.................1...................0......... .....YES
ZC1.................2...................0......... .....NO
ZC1.................3...................0......... .....GOOD
ZC1.................4...................0......... .....SUFF
ZC1.................5...................0......... .....NO_GOOD
ZC1.................6...................0......... .....NR
ZC1.................7...................1......... .....NN
TC1.................1...................1......... .....YES
TC1.................2...................1......... .....NO
TC1.................3...................1......... .....GOOD
TC1.................4...................1......... .....SUFF
TC1.................5...................1......... .....NO_GOOD
TC1.................6...................0......... .....NR
TC1.................7...................0......... .....NN
.................................................. ........
.................................................. ........
I've always JUST 7 (seven) DESC_ANSWER (YES,NO,GOOD,SUFF,NO_GOOD,NR,NN)
Now I'd like to have ID_QUESTION like columns and DESC_ANSWER like rows.
like this:
DESC_ANSWER..........XB1........YB1............ZC1 ........TC1
YES.........................0..........1.......... ....0...........1
NO...........................0..........2......... .....0...........1
GOOD.......................1..........3........... ...0...........1
SUFF........................0..........0.......... ....0...........1
NO_GOOD..................1..........3............. .0...........1
NR...........................0..........2......... .....0...........0
NN...........................0..........1......... .....1...........0
How Can I write this query to get this output??
Thanks in advance!
View 2 Replies
View Related
Mar 24, 2008
SELECT
[R].[PaymentMonth],
[S].[RegionCode],
[S].[CmsStateShortName],
[P].[Attribute1] AS [FinalProduct],
[Membership] = SUM([R].[Membership])
FROM
[RptMMRSummary1] [R] INNER JOIN [RefCmsState1] [S]
ON [R].[CmsStateCode] = [S].[CmsStateCode]
INNER JOIN [RefPlanBenefitPackage1] [P]
ON [R].[PlanBenefitPackageID] = [P].[PlanBenefitPackageID]
WHERE
[R].[PaymentMonth] IN ('200712', '200711', '200612')
-- [P].[Attribute1] IN ('HMO', 'PPO', 'PFFS', 'SNP', 'EVCSNP')
GROUP BY
[R].[PaymentMonth],
[S].[RegionCode],
[S].[CmsStateShortName],
[P].[Attribute1]
-------------------------------------------------------------------
How do we use the pivot query for the above script. Layout as below
200801 Month / Year Selection
-------------------------------------------------------
StateShortname | Attribute | Attribute | Attribute | Total
----------------------------------------------------------
AL Values Values Values
OL Values Values Values
ZW Values Values Values
WEST Group By Region Code Total
BK Values Values Values
MN Values Values Values
EAST Group By Region Code Total
200712 Always Previous Month for the above selection Month /Year
-------------------------------------------------------
StateShortname | Attribute | Attribute | Attribute | Total
----------------------------------------------------------
AL Values Values Values
OL Values Values Values
ZW Values Values Values
WEST Group By Region Code Total
BK Values Values Values
MN Values Values Values
EAST Group By Region Code Total
200612 Always Previous year End Month for the above select Month / Year
-------------------------------------------------------
StateShortname | Attribute | Attribute | Attribute | Total
----------------------------------------------------------
AL Values Values Values
OL Values Values Values
ZW Values Values Values
WEST Group By Region Code Total
BK Values Values Values
MN Values Values Values
EAST Group By Region Code Total
Output :
[RegionCode],
[CmsStateShortName],
CurMonth_HMO, CurMonth_PPO, CurMonth_PFFS, CurMonth_SNP, CurMonth_EVCSNP,
PrevMonth_HMO, PrevMonth_PPO, PrevMonth_PFFS, PrevMonth_SNP, PrevMonth_EVCSNP,
PrevYrEndMonth_HMO, PrevYrEndMonth_PPO, PrevYrEndMonth_PFFS, PrevYrEndMonth_SNP, PrevYrEndMonth_EVCSNP
View 4 Replies
View Related
Dec 27, 2007
I€™ve a table with 2 columns with the following data
Col 1 Col2
---------------------------
Vista 2005
Distinguish 2005
ColonialVoice 2006
Vista 2006
Vista 2007
SuperiorCard 2007
Distinguish 2007
I would like to get the output in the following format using PIVOT function
2005 2006 2007
---------------------------------------------------------
Vista ColonialVoice Vista
Distinguish Vista SuperiorCard
Distinguish
Help me to write the query€¦
--
ash
View 3 Replies
View Related
Dec 27, 2007
I€™ve a table with 2 columns with the following data
Col 1 Col2
---------------------------
Vista 2005
Distinguish 2005
ColonialVoice 2006
Vista 2006
Vista 2007
SuperiorCard 2007
Distinguish 2007
I would like to get the output in the following format using PIVOT function
2005 2006 2007
---------------------------------------------------------
Vista ColonialVoice Vista
Distinguish Vista SuperiorCard
Distinguish
Help me to write the query€¦
--
ash
View 3 Replies
View Related
Sep 12, 2007
Hi,
I have a table EmpProject with following:
=========================
unitid projectid employees
----------- --------- -----------------------
1 CCT1 Ravi,Raja,Kanna
2 CCT1 John,Vijay,Nithya
1 CCT2 Senthil,Lee,Suresh
2 CCT2 Ram,Krish,Latha
2 CCT3 Raja,Vijay,Ram
2 CCT4 Sankar
TSql to create the above :
create table empproject (unitid int, projectid varchar(4), employees varchar(1000))
insert empproject select 1,'CCT1', 'Ravi,Raja,Kanna'
insert empproject select 2,'CCT1', 'John,Vijay,Nithya'
insert empproject select 1,'CCT2', 'Senthil,Lee,Suresh'
insert empproject select 2,'CCT2', 'Ram,Krish,Latha'
insert empproject select 2,'CCT3', 'Raja,Vijay,Ram'
insert empproject select 2,'CCT4', 'Sankar'
I would like to have it in the following format
========================================================
unitid CCT1 CCT2 CCT3 CCT4
----------------------------------------------------------------------------------------------------------------
1 Ravi,Raja,Kanna Senthil,Lee,Suresh
2 John,Vijay,Nithya Ram,Krish,Latha Raja,Vijay,Ram Sankar
Is there any way to query?
Thanks in Advance,
Nithyapriya
View 6 Replies
View Related
Mar 30, 2006
Hi!
I have a table Items(id, name, section, id_format, price) and a table Format(id, format,order)
What I want to do is display: Name, Format1, Format2, Format3.
How can I acheive that result??
THanks!!
-JTM
View 2 Replies
View Related
Sep 26, 2007
I have tbWarehouseStock, contain
WHCode Item Stock
------ ------ -----
WH001 Pencil 10
WH001 Pen 10
WH002 Pencil 5
WH003 ruler 100
How to make pivot query like this, I am thinking of dynamic SQL but dont know how to do this
Item WH001 WH002 WH003 WH004 WH005 ....
------ ----- ----- ----- ----- -----
Pencil 10 5 0
Pen 10 0 0
Ruler 0 0 100
thanks
View 3 Replies
View Related
Feb 27, 2008
(SQL Server 2005)
I have the following query (trying to execute in Managment Studio):
SELECT Rate, Lender, Pricing, Max(Pricing) AS Maximum, Min(Pricing) As Minimum, Avg(Pricing) As Median
FROM [10_Tier].[dbo].[Temp10Tier_1000]
PIVOT (Sum(Pricing) For Lender)
I get the error: Incorrect syntax near ')' Microsoft SQL Server Error 102
I've been looking all over and have found many examples but I cannot get any of them to work.
What I want to accomplish is to be able to create a pivot query and use it in the "Query Builder" when designing a report for the reporting services, so that I can have a table that mimicks a "Matrix" - so that I can have multiple value columns.
View 3 Replies
View Related
Dec 4, 2007
I have a table with following structure:
ProductID OrderType Value
1001 BaseSales 2000
1001 Incremental 1000
1001 TotalSales 3000
1002 BaseSales 2002
1002 Incremental 1003
1002 TotalSales 3005
I would like to get the data in following format:
ProductID BaseSales Incremental TotalSales
1001 2000 1000 3000
1002 2002 1003 3005
Thanks for any help
Regards
Josh
View 1 Replies
View Related
Aug 21, 2007
Hello,
Is it possible to create a named query in the DSV that is the result of a pivot (e.g. cross tab?). The number of columns as a result of the pivot are based the number of records in one of the driving tables - in other words, it is not fixed.
View 1 Replies
View Related
Apr 22, 1999
Perhaps 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
View 1 Replies
View Related
Dec 24, 2013
I 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
View 5 Replies
View Related
Sep 8, 2006
I 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
View 7 Replies
View Related
Feb 1, 2008
Hello 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?
View 4 Replies
View Related
