Pivot Table Without Aggregation?

Mar 29, 2007

Hello, I have a resultset as follows:

fields: Name, RankID

values:
Prorduct A, 4
Product B, 33
Product 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 Name
values as columns, and the RankID of each one as the data. So you
would end up with only one row like:

Product A | Product B | Product C | etc
4 | 33 | 221 | etc

Is this possible? I do not want to sum the data or anything, simply
rotate it sort of.

Any advice is appreciated.

View 3 Replies


ADVERTISEMENT

Pivot Table With No Numeric Aggregation?

Aug 4, 2006

I'm trying to pivot the data in a table but not aggregate numeric data, just rearrange the data in columns as opposed to rows.

For example, my initial table could be represented by this structure:







ID
Label
Value

1
a
val1

1
b
val2

1
c
val3

1
d
val4

1
e
val5

2
a
val6

2
b
val7

2
c
val8

2
d
val9

2
e
val10

and I am trying to get it into the following structure:







ID
Label a
Label b
Label c
Label d
Label e

1
val1
val2
val3
val4
val5

2
val6
val7
val8
val9
val10

The number of labels is known beforehand, so we know how many columns to make. I can get a first step at pivoting it with 'case' statements, but obviously still end up with a row for each Label/Value pair since there is no aggregate function being applied. So I'm not sure how to get it flattened down like shown above?

Thanks for any replies on this,

Eric

View 3 Replies View Related

T-SQL (SS2K8) :: PIVOT Without Aggregation

Jun 28, 2015

I have a table imported from a legacy Oracle database that stores values vertically in name/value pairs. I store it in table-type variable that is an exact copy of the structure:

DECLARE @OracleEngData TABLE
( DataSourceCHAR(8)
, [OMNI_NUMBER] INTEGER
, [TIMESTAMP] INTEGER
, [DATA_TYPE] NVARCHAR(24)
, [PARAMETER] NVARCHAR(32)
, [PARAMETER_VALUE] NVARCHAR(132));

If this information were pivoted horizontally: OMNI_NUMBER would be the primary key.

TIMESTAMP is a 10-digit integer that represents the number of seconds since 1/1/1970 UTC that requires additional conversion. DATA_TYPE is not the data type. It is a general categorization of the next two columns.PARAMTER would be the column headings if it were horizontal..PARAMETER_VALUE would be the data value in that column.

I would like to try to use PIVOT to list the PARAMETER column values as column headers. This seems to work fine. What's confusing me is that I'd like it to list the PARAMETER_VALUE column values as raw data, just as it is in the source version, without having to apply some sort of aggregate function to it. Here's a CSV sample of the data you can paste into Excel. I'm trying to transform this:

OMNI_NUMBER,TIMESTAMP,DATA_TYPE,PARAMETER,PARAMETE_VALUE
506026,1413240436,test_data,cnr,211250000,54.8
506026,1413244259,test_data,cnr,211250000,53.2
506026,1413244679,test_data,cnr,211250000,53.1
506026,1413309646,test_data,cnr,211250000,53.4
506026,1413315987,test_data,cnr,211250000,53

[code]...

But I don't want the sum of the values or the average of the values, just the values. The PIVOT syntax seems to require an aggregate operation.

View 2 Replies View Related

Power Pivot :: Parent-Child Aggregation In Report

Jun 5, 2015

In my data warehouse, I have one level of Parent-Child hierarchy in DimEmployee table - Employee and Manager, linked to each other using a column, ParentEmployeeKey. This table also has historical data stored - a slowly changing dimension of Type 2. I also have a Fact table that gives me information related to Sales - FactSales. In this table, we have SalesAmount stored at Employee level, one Employee can have multiple Sales. Besides, FactSales is also related to DimDate table using DateKey.

I have a requirement wherein I need to make a PowerPivot report out of a PowerPivot data model, that is capable of getting filtered using a timeline, and displays SalesAmount for the Sales occurred in the date range selected on the timeline for a Manager as well as his subordinates in the same report, a Manager's SalesAmount should be an aggregated sum of Sales made by the Manager himself and the Sales made by his subordinates. When the selected date range in the timeline changes, the report must reflect this change.

The approach I tried was adding the ParentEmployeeKey column to FactSales in the data Warehouse, and then adding another roleplaying dimension 'DimManager' in my data model to connect to FactSales based on ParentEmployeeKey of fact with EmployeeKey of DimManager. The problem with this approach is this will only give me Manager related aggregations and I will have to create two separate reports for subordinates and managers.

The following table is a merged view of data from DimEmployee and FactSales. On report, I need to display either of the following:

Column A (applicable SalesAmount)Columns B1 (Manager's SalesAmount) & B2 (Employee's SalesAmount)

View 9 Replies View Related

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

Aggregation Table

Jul 3, 2007

Hi all



i need to create aggregation table from 2 tables group by date, any one have any idea how to create it by using SSIS



thanks & regards

View 1 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

SQL Server 2012 :: Aggregation Table On Sliding Dates

Sep 18, 2014

currently need to re-create an aggregate table in a proc every night to aggregate purchases broken down by person/store in groups of 3, 6 12 etc months.finding the performance of it is very slow as it covers 500,000 million rows.The query looks like

SELECTCusID(int)
, StoreID(int)
, SUM(L3M) as Last3Months
, SUM(L6M) as Last6Months
, SUM(L9M) as Last9Months

[code]...

I need to make changes to this because it is using a BETWEEN on a datetime column. I was wondering though, if anyone else has made agg tables like this before an found a better way of doing them?

View 0 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 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 View Related

Pivot One Of The Table

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

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







Copyrights 2005-15 www.BigResource.com, All rights reserved