Pivot Table

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

Power Pivot :: Auto Refresh Excel Table (Not Pivot Table) Using Data Source

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

SSMS Express: Using PIVOT Operator To Create Pivot Table - Error Messages 156 && 207

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

Power Pivot :: Force Measure To Be Visible For All Rows In Pivot Table Even When There Is No Data?

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

Power Pivot :: ALL DAX Function Not Overriding Filter On Pivot Table

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

Power Pivot :: How To Apply Min Formula Under New Measure Within A Pivot Table

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

Power Pivot :: Displaying Cumulating Numbers In A Pivot Table When There Is No Value

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

Power Pivot :: Measures Not Reflected In Pivot Table

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

Power Pivot :: Difference Between Two Pivot Table Sums?

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

Power Pivot :: Slicer And Pivot Table Value Order

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

Power Pivot :: Printing From Pivot Table With Slicers

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

Power Pivot :: Pivot Table Loses Text Wrapping For Text Data Upon Refresh

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

Power Pivot :: Temp Table Or Table Variable In Query (not Stored Procedure)?

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

Power Pivot :: Measure Results Limited By Fact Table Dates Instead Of Date Table

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

Pivot One Of The Table

Dec 24, 2013

View 5 Replies View Related

Pivot Table

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

Need Help With A Pivot Table

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

Pivot Table

Jun 12, 2007

I'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

View 1 Replies View Related

Not Exactly Pivot Table

Oct 31, 2007

I 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.

View 3 Replies View Related

Where Clause With Pivot Table?

Nov 3, 2003

I 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!

View 2 Replies View Related

Dynamically Pivot A Table?

Mar 30, 2012

How do I dynamically pivot a table? On my example below, the STORE changes.

View 2 Replies View Related

Pivot Table / CUBE ?

Jan 17, 2004

Please see the attached file

View 5 Replies View Related

Cross Tab/Pivot Table/UDF?

Mar 27, 2004

I have the following tables:

tbTemplateShapeProperties
fkTemplate | fkProperty | PropertyValue
-----------------------------------------------
1 | 1 | 192
1 | 2 | 36
1 | 3 | 4
1 | 4 | 5
1 | 5 | 2

tbShapeProperties
Property | PropertyName | fkShape
----------------------------------------------
1 | Width | 1
2 | Height | 1
3 | Flange | 1
4 | Avg. Leg Width | 5
5 | Leg Count | 2

From the above I wanted to create a pivot table, from there I want to pass the column values through to a UDF

XSection (Width, Height, Flange, Leg, LegCount)

I tried the following to get a pivot table but it does not give a single row but 5.

SELECT CASE sp.PropertyName WHEN 'Width' THEN tsp.PropertyValue ELSE 0 END AS Width,
CASE sp.PropertyName WHEN 'Height' THEN tsp.PropertyValue ELSE 0 END AS Height,
CASE sp.PropertyName WHEN 'Flange' THEN tsp.PropertyValue ELSE 0 END AS Flange,
CASE sp.PropertyName WHEN 'Avg. Leg Width' THEN tsp.PropertyValue ELSE 0 END AS Leg,
CASE sp.PropertyName WHEN 'Leg Count' THEN tsp.PropertyValue ELSE 0 END AS LegCount
FROM tbTemplateShapeProperties AS tsp INNER JOIN tbShapeProperties AS sp
ON tsp.fkProperty = sp.Property
WHERE tsp.fkTemplate = 1

The following results are returned:

Width | Height | Flange | Leg | LegCount
----------------------------------------------
192 | 0 | 0 | 0 | 0
0 | 36 | 0 | 0 | 0
0 | 0 | 6 | 0 | 0
0 | 0 | 0 | 5 | 0
0 | 0 | 0 | 0 | 2

The desired result as you could guess is:

Width | Height | Flange | Leg | LegCount
----------------------------------------------
192 | 36 | 6 | 5 | 2

So, this leaves me with one question, even if I was to get this to work, is is possible to then extract the values and pass them through to the UDF within the same stored proc?

Any hints?

Mike B

View 1 Replies View Related

Creating Pivot Table

Apr 19, 2004

Hallo,

I have a table with 3 columns:
table tbl_user_class
===============
user_id (int) PK
class_id (varchar(20)) PK
class_value(varchar(100))

values are like so:

user_id class_id class_value
======================
1 firstname Rogier
1 lastname Doekes
2 firstname Mary
3 lastname Smith
.....

I would like to create result set in the following format

user_id firstname lastname
====================
1 Rogier Doekes
2 Mary Smith
......

How do I accomplish this? I tried using CASE WHEN statements but the best I could come up with was this:
1 Rogier null
1 null Doekes
2 Mary null
2 null Smith

when I did the following t-SQL statement:
select userID,
CASE WHEN classID = 'firstname' THEN classvalue END as 'firstname',
CASE WHEN classID = 'lastname' THEN classvalue END as 'lastname'
FROM tbl_user_class

Thanks for any help,

-Rogier Doekes

View 2 Replies View Related

Another Complex Pivot Table

May 12, 2008

How go I pivot this data? Field MNGR should became columns and GATE used as data for pivot.

CustomerID CaseID DeptID MNGR GATE
1 278 58 SMITH JAX
1 278 58 GREEN ATL
1 278 58 PITTMAN LBC
1 278 20 COLE MCH
1 278 20 MANN ATL

It should look like this:

CustomerID CaseID DeptID SMITH GREEN PITTMAN COLE MANN
1 278 58 JAX ATL LBC null null
1 278 20 COLE MCH null MCH ATL

View 2 Replies View Related

SQL Pivot Table Problem

May 14, 2008

I have this query that I need to make into a pivot table.

The only fields I need from this query is below, but I need to add a couple cloumns that says 1st Quarter 07, 2nd Quarter 07, 3rd Quarter 07 and 4th Quarter 07. How can I make that possible?

Select

Claim Number,
Total Charges,
Adjusted Savings,
Percent of savings,

FROM vw_Claims_Settlement_Rptdata_DONE_DUP_DUPL

View 6 Replies View Related

Table With Two Columns To Pivot

Aug 20, 2014

I have a table with this data structure (Before section)

I have the query below to generate date as in (After section)

But I would like to have (Desired section) view where start and end tasks are next to each other

Here is the query
-----------------------------

SELECT
ID
,[Final] AS [Final Start]
,[Edit] as [Edit Start]
,[Proof] as [Proof Start]
,[Stage] as [Stage Start]
,[Marketing] as [Marketing Start]

[Code] ....

View 2 Replies View Related

How To Get Pivot Table To Report

Oct 6, 2014

I have a table that stores inventory across different warehouses. My current Pivot table is able to report on the warehouses themselves, but not the stock IN the warehouse! This is what I have so far. I tried changing this:

sum(WarehouseID)
FOR WarehouseID IN ([44],[51],[63],[64],[73],[74],[76],[77],[78])
To this:
sum(PhysicalInventoryQty)
FOR WarehouseID IN ([44],[51],[63],[64],[73],[74],[76],[77],[78])

Which is the last step, but SQL will not allow it.

Current Result
ProductIDPhysicalNYCDropshipFBAFBA CAFBA EUHackensackIn - TransitQuantum CertifiedReturns44
03-KX65-ZSM4NULL000000000NULL
03-Q96D-ZVTDNULL000000000NULL
04-3X1R-013E044735107464006344

[code]....

View 4 Replies View Related

SQL Code Pivot Table

Feb 29, 2008

I am looking into SQL Code Pivot Tables. Does anyone know of any good sites to take a look at?

Let me know.

Thanks

Wendy

View 2 Replies View Related

NAMED SET / PIVOT TABLE !! HELP ME

Sep 29, 2005

NEED KNOW IF SOMETHING KNOW CERTANLY .....ABOUT THIS :************************************************** ********************************PLACE A NAMED SET INTO A PIVOT TABLE : ALL ABOUT THIS..FORMULAS...FORMS...EXAMPLES.... ALL !ANOTHER : GET RELATIONSHIP ABOUT THE FIRST TROUBLE : SEE A NAMED SET FROM ACUBE ( OLAP)************************************************** ************************************************REALLY NEED ALL ABOUT YOU CAN SEND ME.AND WRITE ME ....A OLNLY FIN MANYQUESTIONS IN FORUMS....BUT NO ONE RESPONSES.....APARENTLY TOO MUCH PEOPLEHAVE THIS TROUBLE ...... I KNOW ....NO EVERY DAY .MEMBERS THAT POST IN THISWEBSITE..TAKE REPLY...BUT........, REALLY NEED INFO ABOUT .AS SOON ASPOSSIBLE....MI E-MAIL :Join Bytes!THANKS !.MY BEST WISHES...--hi all !

View 1 Replies View Related

Pivot Table Without Aggregation?

Mar 29, 2007

Hello, I have a resultset as follows:fields: Name, RankIDvalues:Prorduct A, 4Product B, 33Product C, 221(etc)Name is always unique. RankID may not be.I want to take that result set and basically pivot it to have the Namevalues as columns, and the RankID of each one as the data. So youwould end up with only one row like:Product A | Product B | Product C | etc4 | 33 | 221 | etcIs this possible? I do not want to sum the data or anything, simplyrotate it sort of.Any advice is appreciated.

View 3 Replies View Related

A Pivot Table Question

Mar 17, 2008

Here's the recordset I achieved so far:

Category Microchipped Count
-------------------------------------------
Dog 1 20
Dog 0 5
Dog NULL 10
Cat 1 30
Cat 0 8
...

Here's what I want for the final result:

Category Microchipped Non-Microchipped Unknown
----------------------------------------------------------------------------
Dog 20 5 10
Cat 30 8 0
...

I'm using MS SQL Server 2005. Should I use the pivot table function?

View 1 Replies View Related

How To Use Order By In Pivot Table ?

Mar 10, 2008

heed help
i have a PROCEDURE that generate PIVOT table
how to use ORDER BY

in this part
how to do this ?
ORDER BY unit desc, Fname

Code Snippet
SELECT p.ID,p.new_unit,p.mhlka_id,p.mhlka,
p.[1] , p.[2],p.[3], p.[4], p.[5], p.[6], p.[7], p.[8], p.[9], p.[10], p.[11],
p.[12], p.[13], p.[14], p.[15], p.[16], p.[17], p.[18], p.[19], p.[20], p.[21],
p.[22], p.[23], p.[24], p.[25], p.[26], p.[27], p.[28], p.[29], p.[30]
FROM (

SELECT ID,new_unit,mhlka_id,mhlka,
DATEPART(DAY, Date) AS theDay,
ShiftID
FROM v_Employee
WHERE Date >= @BaseDate
AND Date < DATEADD(MONTH, 1, @BaseDate)

ORDER BY unit desc, Fname

) AS y
PIVOT (
min(y.ShiftID) FOR y.theDay IN ([1], [2], [3], [4], [5], [6], [7],[8] , [9], [10], [11],
[12], [13], [14], [15], [16], [17], [18], [19], [20], [21],
[22], [23], [24], [25], [26], [27], [28], [29], [30])
) AS p
END

the all code

DECLARE @Employee TABLE (ID INT, Date SMALLDATETIME, ShiftID TINYINT)

DECLARE @WantedDate SMALLDATETIME, -- Should be a parameter for SP

@BaseDate SMALLDATETIME,

@NumDays TINYINT

SELECT @WantedDate = '20080401', -- User supplied parameter value

@BaseDate = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @WantedDate), '19000101'),

@NumDays = DATEDIFF(DAY, @BaseDate, DATEADD(MONTH, 1, @BaseDate))

IF @NumDays = 28

BEGIN

SELECT p.ID,

p.[1], p.[2], p.[3], p.[4], p.[5], p.[6], p.[7], p.[8], p.[9], p.[10], p.[11],

p.[12], p.[13], p.[14], p.[15], p.[16], p.[17], p.[18], p.[19], p.[20], p.[21],

p.[22], p.[23], p.[24], p.[25], p.[26], p.[27], p.[28]

FROM (

SELECT ID,

DATEPART(DAY, Date) AS theDay,

ShiftID

FROM v_Employee

WHERE Date >= @BaseDate

AND Date < DATEADD(MONTH, 1, @BaseDate)

) AS y

PIVOT (

COUNT(y.ShiftID) FOR y.theDay IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11],

[12], [13], [14], [15], [16], [17], [18], [19], [20], [21],

[22], [23], [24], [25], [26], [27], [28])

) AS p

END

ELSE IF @Numdays = 30

BEGIN

SELECT p.ID,p.new_unit,p.mhlka_id,p.mhlka,

p.[1] , p.[2],p.[3], p.[4], p.[5], p.[6], p.[7], p.[8], p.[9], p.[10], p.[11],

p.[12], p.[13], p.[14], p.[15], p.[16], p.[17], p.[18], p.[19], p.[20], p.[21],

p.[22], p.[23], p.[24], p.[25], p.[26], p.[27], p.[28], p.[29], p.[30]

FROM (

SELECT ID,new_unit,mhlka_id,mhlka,

DATEPART(DAY, Date) AS theDay,

ShiftID

FROM v_Employee

WHERE Date >= @BaseDate

AND Date < DATEADD(MONTH, 1, @BaseDate)

) AS y

PIVOT (

min(y.ShiftID) FOR y.theDay IN ([1], [2], [3], [4], [5], [6], [7],[8] , [9], [10], [11],

[12], [13], [14], [15], [16], [17], [18], [19], [20], [21],

[22], [23], [24], [25], [26], [27], [28], [29], [30])

) AS p

END

View 3 Replies View Related