How To Change Output With Dynamic Pivot

Apr 2, 2015

This is my table.

CREATE TABLE tpivot
([col1] varchar(80), [col2] varchar(80), [col3] varchar(80), [col4] varchar(80), [col5] varchar(80)) ;
INSERT INTO tpivot
([col1], [col2], [col3], [col4], [col5])
VALUES

[code]...

My goal is to turn the table so that the output look like this.

col1 col2 col3 col4 col5
Datum 01.12.2014 02.12.2014 03.12.2014 04.12.2014
EC -204.9 -352.9 -105 -371
Mastercard -88 0 -182 -112.9
Postfinance 0 -79.9 0 -751
VISA -19 -20 -436 -346

I need to have a dynamic pivot since i import the table from a csv that could have different amount of columns each time. I can't even get a static pivot to work.

View 1 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: Change Column Order In Dynamic Pivot?

Sep 17, 2014

I am creating dynamic pivot and my column order is as below

[2015-02],[2015-04] [Prior] ,[2014-08],[2014-11]

but i want to display as below:

[Prior],[2014-08],[2014-11],[2015-02],[2015-04]

View 1 Replies View Related

SQL Server 2008 :: Dynamic Query Pivot Values Change

Aug 6, 2015

I have below script

CREATE TABLE dbo.TestPivot(
CollectionDate DATETIME,
Products VARCHAR(40),
ItemCount INT
)
INSERT INTO dbo.TestPivot
SELECT '4/1/2015','Benz' , 20

[Code] ....

-- Original Output
ProductsApr 2015May 2015Jun 2015
Benz10-800NULL
Toyota5NULL-180

****Required output where ever we have negative values we need to display message Invalid out put message for those negative rows

ProductsApr 2015May 2015 Jun 2015
Benz10Invalid NULL
Toyota5NULL Invalid

View 2 Replies View Related

T-SQL (SS2K8) :: How To Add Inline TVF With Dynamic Columns From CRL Dynamic Pivot

Mar 9, 2015

I have tried building an Inline TVF, as I assume this is how it would be used on the DB; however, I am receiving the following error on my code, I must be missing a step somewhere, as I've never done this before. I'm lost on how to implement this clr function on my db?

Error:
Msg 156, Level 15, State 1, Procedure clrDynamicPivot, Line 18
Incorrect syntax near the keyword 'external'.
CREATE FUNCTION clrDynamicPivot
(
-- Add the parameters for the function here
@query nvarchar(4000),
@pivotColumn nvarchar(4000),

[code]....

View 1 Replies View Related

SQL 2012 :: Creating Dynamic SSIS File Format - Dynamic CSV File As Output

Mar 2, 2014

I am trying to create an ssis package with dynamic csv file as output. and out format contains query output.

sample file name:

Unique identifier + query output + systemdate();

The expression is looking like this.

@[User::FilePath] + @[User::FileName] + ".CSV"

-- user filepath is a variable from ssis package. File name is the output from SQL query. using script task i have assigned the values to @[User::FileName] .

When I debugged the script task the value getting properly but same variable am using for Flafile destination. but its not working.

View 3 Replies View Related

Dynamic Pivot

Oct 30, 2006

Hi

Is there a way for using the PIVOT synatx with a dyamic/unknown list of columns? In most PIVOT examples ther columns are "hard" coded but if for example, you are pivoting a sales order table where u don't know which Fiscal Years the orders cover, how could you do this?

Thanks







View 3 Replies View Related

Pivot Table In HTML Output

Jan 21, 2008

Hi Friends,
I am new to reporting services . In Excel reports we can create pivot tables and manipulate data easily. Is it possible to create the pivot table in HTML output in SQL Server 2005 without changing the existing procedure or function? I wonder if there is any drag and drop facility to do the same in HTML report. Hope , some one might have dealt with this.

Regards,
Bhushan

View 1 Replies View Related

Pivot On Dynamic Columns

May 11, 2006

I have a table with 40k terms and I need to map these to a set of objects where each object is represented as a column(tinyint). The object/column name is represented as a guid and columns are added/removed dynamically to support new objects for a set of terms.

I can get the rows needed:

guid1guid2guid3guid4guid5
================================
01100
01101

I think I need to then convert this set of rows to a table which I can join to the object runtime table to start these objects if the column has a count/sum greater than 0. This is the table I think I need in order to join on guids to the runtime table:

NAME Count
===========
guid10
guid22
guid32
guid40
guid51

I don't know how to construct this table for the former table. I think it may be a pivot table, but I don't know. I have the column names:

SELECT NAME
FROM SYSCOLUMNS
WHERE ID = OBJECT_ID(#Temp)
ORDER BY COLID

NAME is a sysname, which doesn't seem to cast into a guid, also a problem when joining the runtime table with this #Temp table.

I also don't want to use a cursor to construct a table.


Thanks for any help,
James

View 1 Replies View Related

Transact SQL :: Add A Grand Total To Pivot Output?

Oct 9, 2015

I have a SP that will generate a pivot output. I want to add a grand total at the end row to sum up the counts for each column.

the SP is as below :

/* COLUMN HEADERS*/
DECLARE
@columnHeaders NVARCHAR (MAX)
SELECT
@columnHeaders  =  COALESCE ( (@columnHeaders)  + ',[' + [Date] + ']', '[' + [Date] + ']')

[code]....

I am getting the below error:

Invalid column name 'Grand Total'.

Msg 205, Level 16, State 1, Line 16

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

View 10 Replies View Related

SQL Server 2012 :: Dynamic Value On Pivot

Sep 21, 2015

Can we pass dynamic values while pivoting?

Here is example

Declare @a date, @b date
set @a='2015-09-08 22:19:29.330'
set @b='2015-09-17 22:19:29.330'
create table #DateTemp(Full_Date_Text_YYYY_MM_DD datetime,Full_Date date)
insert into #DateTemp(Full_Date_Text_YYYY_MM_DD,Full_Date)
select '2015-09-09 00:00:00.000','2015-09-09'

[Code] ......

View 3 Replies View Related

Power Pivot :: Dynamic Row Numbering

Nov 12, 2015

I am having trouble making this DAX formula work. This is my test data including the column "rank" which is a row_number with partition by generated using T-SQL. (The nature of the data is that the "Measure" will always increase cumulatively per month and I want the maximum "measure" to equal a rank of 1).What I need is a second RANK column which updates dynamically so that if I select just some of the Months, they will rank from 1 +. E.g:

View 6 Replies View Related

Pivot Table How To Add Day Name Dynamic + In My Language

Jan 17, 2008

hi need help
how to add to pivot table the day name dynamic for all the month(but i need a short name) like instead Sunday =sun, monday=mon
1-sun 2-mon 3-Tue


+ how can i add the day name in my language

like instead Sunday ="ר?", monday="ש "
03/2008




empid

1-st

2-sun

3-mon

4-Tue

5 -wen

6-Thur

7 -fr

8-st

9 -mon

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31


11111

2

3

4

5

6

7

8

1

2

3

4

5

6

7

8

1

2

3

4

5

6

7

8

1

2

3

4

5

6

7

8





Code Block
DECLARE @Employee TABLE (ID INT, Date SMALLDATETIME, ShiftID TINYINT)
DECLARE @WantedDate SMALLDATETIME, -- Should be a parameter for SP
@BaseDate SMALLDATETIME,
@NumDays TINYINT
SELECT @WantedDate = '20080301', -- 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], p.[29], p.[30], p.[31]
FROM (
SELECT ID,
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], [31])
) AS p
END





TNX



View 2 Replies View Related

Help With A Pivot Table (dynamic Columns)

Feb 15, 2008

I have the following Pivot Table:



Code Snippet
Declare @tblEquipment Table
(
numEquipmentID INT,
txtManufacturer nvarchar(30),
txtModel nvarchar(30)
)
Declare @tblEquipmentFields Table
(
numFieldNameID INT,
txtFieldName nvarchar(25)
)
Declare @tblEquipmentDetails Table
(
numEquipmentDetailsID INT,
numEquipmentID INT,
numFieldNameID INT,
txtFieldValue nvarchar(30)
)
Insert INTO @tblEquipment Values(23, 'Dell', 'Optiplex 270')
Insert INTO @tblEquipment Values(26, 'Dell', '1705FP')
Insert INTO @tblEquipment Values(42, 'Dell', 'Optiples 745')
Insert INTO @tblEquipmentFields Values(1, 'Monitor Size')
Insert INTO @tblEquipmentFields Values(2, 'Processor Type')
Insert INTO @tblEquipmentDetails Values(1077, 23, 2, 'P4M')
Insert INTO @tblEquipmentDetails Values(1146, 26, 1, '17')
Insert INTO @tblEquipmentDetails Values(1026, 42, 2, 'P4 Dual Core')
Select numEquipmentID As EquipmentID, [Monitor Size], [Processor Type]
From
(Select a.numEquipmentID, txtManufacturer, txtModel, txtFieldName, txtFieldValue
From @tblEquipment a JOIN
@tblEquipmentDetails b ON
a.numEquipmentID = b.numEquipmentID
JOIN @tblEquipmentFields c ON
b.numFieldNameID = c.numFieldNameID
) As SourceTable
Pivot
(
Max(txtFieldValue)
For txtFieldName IN ([Monitor Size], [Processor Type])
) As PivotTable






What I'm wondering is if it's possible to have the columns change dynamically. For example:

If lets say I only want the record with numEquipmentID of 23 to show I only want its corresponding information to show

EquipmentID ProcessorType
23 P4M

Now lets say that I want to bring back an additional record, like 23 and 26 I would like the columns to change to the following

EquipmentID ProcessorType Monitor Size
23 P4M NULL
26 NULL 17

So in essence a column will be added based on the equipmentID. Thanks in advanced.

View 4 Replies View Related

SQL Server 2012 :: Dynamic Pivot With Subgrouping

Nov 25, 2013

I am trying to pivot some data as you would normally however I am trying to also group the pivot into three sub column groups too.

Basically the scenario is that I have three sub groups Budget, Person, RenewalDate for each Service (Service being the pivot point). So for each unique service I want to display the budget person and renewal date for each service by company.

I have created two tables to illustrate the base data and the required output.

How to do this dynamically because the number of Services is unknown, i.e. it could be 4 Services or 20, each with three sub columns, budget, person and renewal date.

Please find code below. It should be quite self explanatory as to what I am trying to do.

IMPORTANT:

1. I really need it to be dynamic
2. the Services are not standardised names, they are numbered for illustration purposes only, they vary in naming convention.

create table #BaseData
(
Company nvarchar(100),
Person nvarchar(50),
[Service] nvarchar(100),
Budget int,
RenewalDate datetime
)

[Code] .....

View 4 Replies View Related

Generate Column Numbers Using Dynamic Pivot

Jul 1, 2014

I have the following SQL which i want to convert to a stored procedure having dynamic SQL to generate column numbers (1 to 52) for Sale_Week.Also, I want to call this stored procedure from Excel using VBA, passing 2 parameters to stored procedure in SQL Server
e.g,

DECLARE @KPI nvarchar(MAX) = 'Sales Value with Innovation' DECLARE @Country nvarchar(MAX) = 'UK'

I want to grab the resultant pivoted table back into excel. how to do it?

USE [Database_ABC]
GO
DECLARE @KPI nvarchar(MAX) = 'Sales Value with Innovation'
DECLARE @Country nvarchar(MAX) = 'UK'

[code]...

View 1 Replies View Related

Export Dynamic Pivot Table To Excel

Feb 26, 2015

I know that this is an Excel question, but I guess it is much more likely that an SQL person using dynamic pivot tables had stepped on this, rather than any advanced Excel user.

I am exporting a dynamic pivot table to Excel through a Stored Procedure. If the Stored Procedure that executes the dynamic pivot table returns 7 columns in one run, and 4 columns in the following update, then I have 3 orphaned columns that are still displayed in the spreadsheet. There isn't any content related to them, but the empty columns with their headers are bothering enough.

I've been trying to play with the data connection properties, but nothing deletes unused columns from former data executions.

View 1 Replies View Related

Dynamic Pivot Table With Multiple Tables

May 19, 2015

How to pass dynamic values in xml path query?

WITH TEST AS (
SELECT TL.TERMINAL_ID,T.IP_ADDRESS, T.LOGICAL_CONNECT_STATUS, SI.SCHEDULER_ID,
SI.INSTRUCTION, SI.GROUP_ID, SI.MAX_READ_RETRIES, SI.DATA_CHAR, SI.SCHEDULE_TYPE,SI.FILEPATH_FLAG,
T.STATION_NAME,T.BANK_ID FROM SCHEDULERINFO SI  
INNER JOIN TERMINALGROUP TG  ON SI.GROUP_ID = TG.GROUP_ID INNER JOIN TERMINALGROUPLINK TL  ON TG.GROUP_ID = TL.GROUP_ID

[Code] ....

I need to pass dynamic values in FOR SCHEDULER_ID COLUMN. Because I have huge data.

View 7 Replies View Related

PIVOT TABLE Dynamic Column Header?

Nov 14, 2007

I am trying to work on a database with 3 tables. To make it easier I have created a couple of temp tables to work out the syntax.

CREATE TABLE #owner
(
[NameId] tinyint IDENTITY(1,1) NOT NULL,
[Name] varchar(50) NOT NULL
)

INSERT INTO #owner VALUES ('ME');
INSERT INTO #owner VALUES ('Other');

CREATE TABLE #propertyType
(
[TypeId] tinyint IDENTITY(1,1) NOT NULL,
[Name] varchar(50) NOT NULL
)

INSERT INTO #propertyType VALUES ('Home');
INSERT INTO #propertyType VALUES ('Car');

CREATE TABLE #property
(
[NameId] tinyint NOT NULL,
[TypeId] tinyint NOT NULL,
[Value] varchar(50) NOT NULL
)

INSERT INTO #property VALUES (1,1, 'Blue');
INSERT INTO #property VALUES (1,2, 'Black');
INSERT INTO #property VALUES (2,1, 'Red');
INSERT INTO #property VALUES (2,2, 'Black');

DROP TABLE #owner;
DROP TABLE #propertyType;
DROP TABLE #property

| NameId | Name |
| 1 | ME|
| 2 | other |

| TypeId | Name |
| 1 | Home |
| 2 | Car |

| NameId | TypeId | Value |
| 1 | 1 | Blue |
| 1 | 2 | Black |
| 2 | 1 | Red |
| 2 | 2 | Black |

Where property value is some arbitrary detail. The real propertyType has 50 or 60 rows and not every property has all of the values. I am trying to create a pivot table that would look like so that I can present the data in an easier to understand format:

[Owner | Home | Car ]
[ME | Blue | Black ]
[Other| Red | Black ]

The propertyTypes are added often, and I don't really have the ability to change them. There is a unique constrant on property on nameid and typeid so there will never be two of the same property with the same owner. Any help would be very helpful.

View 9 Replies View Related

PIVOT With Dynamic Columns Names Created

Aug 3, 2007

I am trying to do a PIVOT on a query result, but the column names created by the PIVOT function are dynamic.

For example (modified from the SQL Server 2005 Books Online documentation on the PIVOT operator) :

SELECT
Division,
[2] AS CurrentPeriod,
[1] AS PreviousPeriod
FROM
(
SELECT
Period,
Division,
Sales_Amount
FROM
Sales.SalesOrderHeader
WHERE
(
Period = @period
OR Period = @period - 1
)
) p
PIVOT
(
SUM (Sales_Amount)
FOR Period IN ( [2], [1] )
) AS pvt

Let's assume that any value 2 is selected for the @period parameter, and returns the sales by division for periods 2 and 1 (2 minus 1).

Division CurrentPeriod PreviousPeriodA 400 3000 B 400 100 C 470 300 D 800 2500 E 1000 1900

What if the value @period were to be changed, to say period 4 and it should returns the sales for periods 4 and 3 for example, is there a way I can change to code above to still perform the PIVOT while dynamically accepting the period values 4 and 3, applying it to the columns names in the first SELECT statement and the FOR ... IN clause in the PIVOT statement ?

Need a way to represent the following [2] and [1] column names dynamically depending on the value in the @period parameter.

[2] AS CurrentPeriod,
[1] AS PreviousPeriod

FOR Period IN ( [2], [1] )

I have tried to use the @period but it doesn't work.

Thanks in advance.

Kenny

View 1 Replies View Related

Transact SQL :: SSMS Pivot With Dynamic Columns

Apr 23, 2015

I found this Microsoft article for creating crosstab-like queries in SSMS.Is it possible, however, to create this same query if I do not know what the values for the columns will be?  Using their example for my problem, I will not know what the values in the "IN" criteria will be because my query would be for a "rolling" 12 months (thus causing that IN criteria to change every month).I've tried declaring variables to pull in the values, but since this will eventually go into a view, I don't think that I can use declared variables.

View 3 Replies View Related

Power Pivot :: Dynamic Slicers Without Two Way Relationships?

Nov 13, 2015

So, after my last post where the issue came down to my model, I have redone my data sources as a v.2 model and split out into traditional dim/fact tables. I have 2 fact tables a few dims that are shared between the two facts and some dims that are only on one fact.

In my old model I was doing most of this de-normalisation in my sql queries to produce the fact tables (and the two fact tables were related together).

In v1 I used to get a much better context-aware set of slicers as they were directly connected to the fact that the visualisations on the page were relating to. As I selected a slicer, any other slicers items that were no longer possible would disappear.

This is no longer happening with this star-schema method. The old way was better from a user perspective. I had to remove some "both" cross filter directions from the relationship model due on the dims that were shared between both facts, so they are only now Single. I wonder if that has any bearing on this but even if it does, I don't see a solution from this angle.

I guess the only idea I have would be to create calculated measures/columns (probably columns) to materialise the interesting dim values into the fact table but this seems a bit of a time consuming process and as I don't necessarily know in advance which slicers the users may want to use, so it feels like I almost need to add all possible dimension attributes in each fact table... ?

Here is my relationship diagram. In my testing the slicer updates work ok if they are on a 2 way relationship but not on a Single way relationship.

In my case my 2 fact tables are really related (SchemeParticipantResult is a parent of ParticipantMeasureResult) but this then breaks the traditional Star schema that I was advised to follow before.

View 6 Replies View Related

PIVOT To Return Dynamic Rows Horizontally

Jan 13, 2008

Hi,

I have a client with the following table structure:
ItemNumber, Name, Description

containing the following Data (example):






ItemNumber

Name

Value


6473764

SDRAM

4GB


6473764

Xeon

2300 Mhz


6473764

Video

256 MB


6473764

Bus

1300 Mhz


6473759

SDRAM

2GB


6473759

Xeon

2000 Mhz


6473759

Video

128 MB


6473759

Bus

1066 Mhz

I am trying to use PIVOT to convert this into Columns:






Item Number

SDRAM

Xeon

Video

Bus


6473764

4GB

2300 Mhz

256 MB

1300 Mhz


6473759

2 GB

2000 Mhz

128 MB

1066 Mhz

The problem is that I do not know the names of the columns before I run the query. I retrieve them based on a a list of Item Numbers:
SELECT DISTINCT Name FROM TableName WHERE ItemNumber IN (...)

What I was trying to do is the following, but I am not exactly sure what is wrong:

WITH ProductLines (ItemNumber, ItemName, ItemValue)
AS
(
SELECT ItemNumber, Name, Value FROM ProductItems WHERE ItemNumber IN (...)
)

SELECT * FROM ProductLines -- Don't know the column names
PIVOT
(

... -- Not sure what should come here
FOR ItemNumber IN (...)
)

Thanks a lot,

Aric Levin




View 3 Replies View Related

Power Pivot :: Accumulative Change For Index

May 13, 2015

I am tracking FX changes for USD Vs other currencies. I want to allow my users to choose a Start date and an End date and see the accumulative % Change for the index in a line chart (baseline set as Start date). Additional Q - I prefer the dates slicer to be from "Timeline" type instead of 2 regular slicers. 

View 5 Replies View Related

Power Pivot :: Using VBA To Add Or Change Data Connection

Mar 4, 2014

My PowerPivot report is using quite large data that can be split by subsidiary. I’d like to let user select subsidiary. The selection would trigger change of the PowerPivot’s data connection so that the SQL would change into “SELECT

* FROM
dbo.ECSPURSL00
WHERE Subsdiary
= 'selected subsidiary'”.

The Excel 2013 has Model class that should manage the PowerPivot data sources. The object explorer shows CreateModelWorkbookConnection method, but I can't figure out the VBA syntax.

View 5 Replies View Related

SQL Server 2014 :: How To Pivot Output Data Into Multiple Rows

Nov 4, 2015

Is it possible to pivot the output data into multiple rows?

I wanted one row for deleted data and another row for Inserted data, I was looking at UNION ALL and CROSS APPLY but to no avail.

View 9 Replies View Related

Integration Services :: 2005 SSIS Pivot Transform Output

May 8, 2015

I have a Pivot Transform in SSIS (2005) working perfectly, EXCEPT for that the first column of the output (the date) repeats for each of the following columns, which are themselves falling into the correct column, but not on the same line for a particular date as the others. Snipet of result from Data Viewer is:

dbDate site1 site2

1/1/2015 0:00 0.03 NULL
1/2/2015 0:00 0.04 NULL

[code]....

View 2 Replies View Related

SQL Server 2012 :: Join Two Dynamic Pivot Tables

Dec 11, 2013

I have two dynamic pivot tables that I need to join. The problem I'm running into is that at execution, one is ~7500 characters and the other is ~7000 characters.

I can't set them both up as CTEs and query, the statement gets truncated.

I can't use a temp table because those get dropped when the query finishes.

I can't use a real table because the insert statement gets truncated.

Do I have any other good options, or am I in Spacklesville?

View 7 Replies View Related

SQL Server 2012 :: Dynamic Pivot Table Not Grouping

Mar 26, 2014

I have a query

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column
SELECT @ColumnName= ISNULL(@ColumnName + ',','')
+ QUOTENAME(name)

[Code] ....

and so on.

I've tried putting my group by everywhere but it's not working. I'm missing something but what?

View 9 Replies View Related

SQL Server 2012 :: Creating Dynamic Pivot Table

Jul 2, 2014

I am having trouble figuring out why the following code throws an error:

declare
@cols nvarchar(50),
@stmt nvarchar(max)
select @cols = ('[' + W.FKStoreID + ']') from (select distinct FKStoreID from VW_PC_T) as W
select @stmt = '
select *

[Code] ...

The issue that I am having is:

Msg 245, Level 16, State 1, Line 4
Conversion failed when converting the varchar value '[' to data type int.

I know that I have to use the [ ] in order to run the dynamic sql. I am not sure what is failing and why as the syntax seems to be clean to me (obviously it is not).

View 6 Replies View Related

SQL Server 2014 :: Pivot IN Clause - Dynamic Columns

May 12, 2015

The first select is running fine but due to extra values added to the table the list of manual difined columns must be added manualy each time new values occur.

Is it possible to make the PIVOT's IN clause dynamicly as stated in the second script (it is based on the same table #source) when running it prompts the next error;

Msg 156, Level 15, State 1, Line 315
Incorrect syntax near the keyword 'select'.
Msg 102, Level 15, State 1, Line 315
Incorrect syntax near ')'.

adding or moving ')' or '(' are not working.......

select *
into #temp
from #source
pivot ( avg(value) for drive in ([C], [D], [E], [F], [G], [H], [T], [U], [V] )) as value
select * from #temp order by .........

versus

select *
into #temp
from #source
pivot ( avg(value) for drive in (select distinct(column) from #source)) as value

select * from #temp order by .....

View 3 Replies View Related

SQL Server 2008 :: Dynamic Conversion Of Pivot Columns

Jun 15, 2015

I have attached SQL File which Gives me the below resultset Excel.xlsx

But the problem is i am not able to round off the dynamic columns in side my PIVOT, how to rewrite the dynamic query.

View 4 Replies View Related

Dynamic PIVOT Table As Data Source View

May 29, 2008

I would like to use a dynamic pivot table in my data source view. It seems that a named query can be only one sql statement. So, I cannot use my multi-statement procedure that creates a dynamic pivot table output.

What is the best course of action here? I could hard-code my pivot table query. I could maintain a redundant table in the pivot format. Do I have any good options?

KenS


Ken

View 1 Replies View Related

Including Single Quotes In Dynamic Pivot Tables?

Feb 26, 2015

I have this pivot table (I only post the static version as the problem only regards the single quotes)

SELECT * from(
select DATEPART(year,DeliverydatePackingSlip) as Year,
CASE WHEN DiffPromiseDateFirst < 0 Then '1 - too early'
WHEN DiffPromiseDateFirst = 0 Then '2 - on time'
ELSE '3 - too late' END as Delivery
from iq4bisprocess.FactOTDCustomer
WHERE OTD_Exclusion = 0)a
PIVOT ( COUNT(Year)
For Year

in ([2012],[2013],[2014],[2015])) as pvtNow, packing everything in a string parameter I always stumble over the single quotes. I tried to replace them with CHAR(39), I tried to define a parameter for each occurrence, but always get a syntax error. What am I doing wrong?declare @sql nvarchar(max)

declare @title1 nvarchar(20)
declare @title2 nvarchar(20)
declare @title3 nvarchar(20)
set @title1 = '1 - too early'
set @title2 = '2 - on time'
set @title3 = '3 - too late'

[Code] .....

exec sp_executesql @sqlThis would throw:Msg 102, Level 15, State 1, Line 3 Incorrect syntax near 'early'.

View 2 Replies View Related







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