SQL Server 2012 :: Error In PIVOT Using CTE In VIEW?
Oct 23, 2014
I would like to have rows presented as columns. That's why I use the PIVOT function at the end.The resultset will be presented in Excel using an external connection to the view.
When I try to save the view I get the error
Msg 4104, Level 16, State 1, Procedure _TEST, Line 47
The multi-part identifier "vk.OppCode" could not be bound.
Code (restricting the columns that I actually have to the relevant columns only):
USE [DBTest]
GO
/****** Object: View [dbo].[_TEST] Script Date: 23-10-2014 17:24:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
I have written a script to pivot a table into multiple columns.
The script works when run on its own but gives an error when i try to create a view or aprocedure from the same script. The temporary table #.... does not work so i have converted it to a cte.
Here is a copy of the script below
-- Dynamic PIVOT IF OBJECT_ID('#External_Referrals') IS NULL DROP TABLE #External_Referrals; GO DECLARE @T AS TABLE(y INT NOT NULL PRIMARY KEY);
I am looking for a way to create a stored procedure that will show inventory availability. I would like to show the Inventory Name, The Date, and if the inventory is "checked out" using the ID name of the person who has the item.
For example it would look like this:
-------------------------------------------------------------------------------------------------- Inventory Name | 10/24/2015 | 10/25/2015 | 10/26/2015 | 10/27/2015 | 10/28/2015 -------------------------------------------------------------------------------------------------- Laptop | Tom | Tom | Tom | Avail | Avail Projector | Avail | Avail | Avail | Avail | Bob Air Card | Bob | Bob | Bob | Bob | Bob
It seems like I want to do a pivot table but there really is no aggregate so I am not sure what to use.
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 )
I'd like to get a extract table result, with a reference id primary key, showing the maximum dates for events and who was responsible for them. I can get the max(date) field in columns using PIVOT, but can't see a way to get the 'who' field without lots of LEFT JOINs.
Here's some test data and code which shows the principle:
CREATE TABLE #t ( ref INT , id INT , who VARCHAR(10) , checkin DATE
[Code] ....
The result set is:
ref 1 who1 2 who2 3 who3 4 who4 123 2014-01-18 carol 2014-01-18 andy 2014-01-16 bill 2014-01-17 carol 456 NULL NULL 2014-01-17 NULL NULL NULL NULL NULL
Is there some way to avoid all the LEFT JOINs, maybe by using another PIVOT, to produce the same result?
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?
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).
I currently have data stored in a temporary table and I would like to transpose the data into a better format. I would like for the query to be dynamic since one of the tables currently has over 500 columns.
The attached file provides an example of the table structure along with sample data. Below the first set of data is the desired final format.
Code written so far. this pivots the column deck and jib_in into rows but thats it only TWO ROWS i.e the one i put inside aggregate function under PIVOT function and one i put inside QUOTENAME()
DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX); SET @columns = N''; SELECT @columns += N', p.' + QUOTENAME(deck) FROM (SELECT p.deck FROM dbo.report AS p GROUP BY p.deck) AS x;
[Code] ....
I need all the columns to be pivoted and show on the pivoted table. I am very new at dynamic pivot. I tried so many ways to add other columns but no avail!!
I have the following query that will serve as a basis for SSRS report
SELECT TOP (1000) d.Project_Name, d.Status, d.Country, d.Region, p.Period, p.Quarter, p.Year, d.Brand, d.Store_Opens_Actual, d.DA, d.DPN, d.StoreNumber, CONVERT(VARCHAR(10), CASE WHEN ISDATE(d .Store_Opens_Actual) = 1 THEN d .Store_Opens_Actual WHEN ISDATE(d .Store_Opens_Forecast) = 1 AND ISDATE(d .Store_Opens_Actual) = 0 THEN d .Store_Opens_Forecast WHEN ISDATE(d
[Code] ....
This returns a dataset, that I need to convert into a PIVOT table that should look like the attached spreadsheet.
Having trouble writing the PIVOT table query. I feel like I am missing something conceptually as I am not doing any summing or aggregation. I don't know if dynamics SQL is the solution here or which route to take. I don't know if there is such things as PIVOTING without aggregation. CROSS TAB came to my mind as well.
What I would like to end up with is a pivot table of each account, the trigger code and service codes attached to that account, and the rate for each.
I have been able to dynamically get the pivot, but I'm not joining correctly, as its returning every dynamic column, not just the columns of a trigger code. The code below will return the account and trigger code, but also every service code, regardless of which trigger code they belong to, and just show null values.
What I would like to get is just the service codes and the appropriate trigger code for each account.
SELECT @cols = STUFF((SELECT DISTINCT ',' + ServiceCode FROM TriggerTable FOR XML PATH(''), TYPE ).value('(./text())[1]', 'VARCHAR(MAX)') ,1,2,'')
Reformatting data in a PIVOT Table or find a better way to display.
--ORDERDETAIL TABLE
SKUOÂ Â QTYOÂ Â Â Â ORDERIDO
KUMÂ Â Â Â 1Â Â 12345 KUSÂ Â Â Â 2Â Â 12345 SUKÂ Â Â Â 1Â Â 12345 KHNÂ Â Â Â 4Â Â 12345 DREÂ Â Â Â 1Â Â 12345
[Code] ....
Number of SKU's in order could be over 1000.
Looking to change my current pivot table to allow an unlimited number of SKU's and add QTY.
Data I am looking to get.  MAX of 15 SKUS Per line.
ORDERIDÂ Â Â Â SKU1Â Â Â Â QTY1Â Â Â Â SKU2Â Â Â Â QTY2Â Â Â Â SKU3Â Â Â Â QTY3Â Â Â Â SKU4Â Â Â Â QTY4Â Â Â Â SKU5Â Â Â Â QTY5Â Â Â Â SKU6Â Â Â Â QTY6Â Â Â Â SKU7Â Â Â Â QTY7Â Â Â Â SKU8Â Â Â Â QTY8Â Â Â Â SKU9Â Â Â Â QTY9Â Â Â Â SKU10Â Â QTY10Â Â SKU11Â Â QTY11Â Â SKU12Â Â QTY12Â Â SKU13Â Â QTY13Â Â SKU14Â Â QTY14Â Â SKU15Â Â QTY15Â Â 12345Â Â KUM 1Â Â KUS 2Â Â SUK 1Â Â KHN 4Â Â DRE 1Â Â HGF 2Â Â FDE 1Â Â CDS 1Â Â GYT 1Â Â POI 3Â Â LKH 2Â Â TTT 4Â Â JHG 8Â Â YUI 2Â Â WQE 1Â Â 12345Â Â PMN 1Â Â BVC 1Â Â ABD 1Â Â
[Code] ....
CURRENT PIVOT ONLY GOES TO 150 - BELOW
SELECTÂ Â Â Â PKGCUSTOM4, [1] AS [SKU1], [2] AS [SKU2], [3] AS [SKU3], [4] AS [SKU4], [5] AS [SKU5], [6] AS [SKU6], [7] AS [SKU7], [8] AS [SKU8], [9] AS [SKU9], [10] AS [SKU10], Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â [11] AS [SKU11], [12] AS [SKU12], [13] AS [SKU13], [14] AS [SKU14], [15] AS [SKU15], [16] AS [SKU16], [17] AS [SKU17], [18] AS [SKU18], [19] AS [SKU19],
SELECT TOP (100) PERCENT dbo.Filteredfs_franchise.fs_franchiseid AS FranchiseId, dbo.Filteredfs_franchise.fs_brandidname AS Brand, dbo.Filteredfs_franchise.fs_franchisetypename AS [Franchise Type], dbo.Filteredfs_franchise.fs_franchisenumber AS [Franchise Number], dbo.Filteredfs_franchise.fs_transactiontypename AS [Transaction Type], dbo.Filteredfs_franchise.fs_franchisestatusname AS [Status Code],
[Code] ....
I need to pivot this so I can get one row per franchiseID and multiple columns for [Franchisee Name Entity] and [Franchise Name Individual]. Each [Franchisee Name Entity] and [Franchise Name Individual] has associated percentage of ownership.
This has to be dynamic, because each FranchiseID can have anywhere from 1 to 12 respective owners and those can be any combination of of Entity and Individual. Please, see the attached example for Franchise Number 129 (that one would have 6 additional columns because there are 3 Individual owners with 1 respective Percentage of ownership).
The question is how do I PIVOT and preserve the percentage of ownership?
How to write a Dynamic Pivot Statement to Calculate and Organize Columns like:
CREATE TABLE #mytable ( Name varchar(50), GA int, GB int, startdate DATETIME, enddate DATETIME
[Code] ...
Below is Our Sample Table Data.
Name GAGBstartdateenddate Pavan 261/1/20151/1/2015 Hema 561/1/20151/1/2015 Surya 501/1/20151/1/2015 Pavan 811/2/20151/8/2015 Hema 311/2/20151/8/2015 Surya 121/2/20151/8/2015 Pavan 1041/9/20151/15/2015 Hema 301/9/20151/15/2015 Surya 6131/9/20151/15/2015
How to write Pivot Satement to get Oupt like below:
1/1/2015 Pavan Hema Surya SumTotal Total 8 11 5 24 GA 2 5 5 12 GB 6 6 0 12
1/8/2015 Pavan Hema Surya SumTotal Total 9 4 3 16 GA 8 3 1 12 GB 1 1 2 4
1/15/2015 Pavan Hema Surya SumTotal Total 14 3 19 36 GA 10 3 6 19 GB 4 0 13 17
I am using the pivot task to to a pivot of YTD-Values and after that I use derived columns to calculate month values and do a unpivot then.
All worked fine, but now I get this error message:
[ytd_pivot [123]] Error: Duplicate pivot key value "6".
The settings in the advanced editor seem to be correct (no duplicate pivot key value) and I am extracting the data from the source sorted by month.
Could it be a problem that I use all pivot columns (month 1 to 12) in the derived colum transformation and they aren´t available at this moment while data extracting is still going on?
(sorry, I don't know how to make the copied tables 'look right' but spaces should --> tabs) Branches Table B_ID Branch 0 OLD 1 ADM 2 CL1 3 CL2 4 NEB 5 PTO 6 HOL 9 MKT 10 CL3 12 IOW 15 IND 16 PAP 18 SOU 19 NOR Value Table (Pk = Vid, FK = Bid) VID BID Emp_id Value 1 4 1 100 2 4 1 200 3 10 1 300 4 3 2 400 5 4 2 500 6 4 2 600
Desired Result View (Pivot on On Emp_id sum the value column) Emp_ID OLD ADM CL1 CL2 NEB PTO HOL MKT CL3 IOW IND PAP SOU NOR 1 0 0 0 0 300 0 0 0 0 0 300 0 0 0 2 0 0 0 400 1100 0 0 0 0 0 0 0 0 0
My current solution is to use a pivot query similar to the below (note: ..etc. used to simplify the discussion), It gets me close with the exception of presenting the null values as null rather than 0, I can make that work! SELECT Emp_id,[0] AS OLD, [1] AS AD, [2] AS CL1, [3] AS CL2, [4] AS NEB ....etc) FROM (SELECT Emp_id, value, B_id FROM Branches) P PIVOT (sum(value) FOR B_idId IN ([0], [1], [2], [3], [4].."etc.")) AS pvt "Take the data from the Branches table, sum up the values for a employee then present as a "pivoted" sql view with each branch in a column and nulls converted to 0"
Finally here is the problem/question.
I don't want to 'hard' code the branches (as the solution above does), they will change over time as branches are added and I want the query to reflect these changes. The problem, I don't know how to proceed from here, I have looked at using a SSAS solution, but that seems like too much for such a simple problem and in any case, I don't know how to integrate the SSAS cube into the current Ms Access 2000 solution (moving from Access backend data store to SQL server 2005).
Note: all the examples I have found in the BOL and forums, hard code the columns
I am frankly overwhelmed be the number of options available in SQL Server 2005, and still am trying to 'get my mind' around some of the concepts. I'm sure I don't know many of the options available, much more how to best implement them. The BOL, while good, have not really provided the enlightment I seek.
If I have to go to a CLR solution, could one provide some simple guidance, Thanks.
I have created a view thats pulling data from two different tables to combine them into one report.
table 1 lists the client code and table 2 lists the client partner and they're linked by a variable.
When running the report the result shows the client codes with their respective partner however any client codes that didn't have a partner are not displaying in the report and I need all client codes to be displayed even if there's no partner.
Is there a way I can make this display all results and if the client partner doesn't exist for it to still display as 'Null' for the partner but still display the client code?
Script:
SELECT TOP (100) PERCENT C.cltCode AS ClientCode, C.cltSortName AS SortName, C.cltTerminationDate AS [Term date], dbo.vcltAttrib6.ainTVal AS Department, C.objInstID AS ClientID FROM dbo.cdbClient AS C INNER JOIN dbo.vcltAttrib6 ON C.objInstID = dbo.vcltAttrib6.ainObjectInstID GROUP BY C.cltSortName, C.cltTerminationDate, dbo.vcltAttrib6.ainTVal, C.objInstID, C.cltCode ORDER BY ClientID
I want to give developers access to view SQL Job history, but not let them inhert permission to create local jobs like they get with the fixed server roles.
how can this be achieved? Or can it only be achieved by giving them access to the tables in msdb and querying them?
Can someone help me parsing this ms-access PIVOT sql-statement to ams-sql-server sql-statement?Many thanks in advanceTRANSFORM Count(KlantenStops.id) AS AantalVanidSELECT KlantenStops.Uitvoerder, KlantenStops.KlantFROM KlantenStopsGROUP BY KlantenStops.Uitvoerder, KlantenStops.KlantPIVOT DatePart("m",leverdatum,1,0) In("1","2","3","4","5","6","7","8","9","10","11","12");
Using PowerPivot I created a connection to a view on a SQL Server database. All fields imported correctly.A column has since been added to the view. How do I get this to appear in my PowerPivot?
I have a function that accespts a string and a delimeter returns the results in a temp table. I am using the funtion for one of the columns in my view that needs be to split and display the column into different columns. The view takes for ever to run and finally it doesn't split and doesn't display in the column.
Function: ----------------------------------- ALTER FUNCTION [dbo].[func_Split] ( @DelimitedString varchar(8000),
[Code].....
Not sure what I am missing in the above view why it doesn't split the string.
The column prod_Master.M2_Pct is defined as float.Yet for some of the rows, the value in [% of Total Target] comes up as null even though there is a number in prod.Measure column. There is valid non-null data in prod_Master.M2_Pct.
I tried LTRIM(RTRIM(prod.Measure)), but no change.
Use ProdDB SELECT TOP (100) PERCENT dbo.prod.ProdNo AS [Prod No], dbo.prod.ProdName AS [Prod Name], CASE WHEN dbo.prod.Measure = 'P1' THEN dbo.prod_Master.P1_Pct WHEN dbo.prod.Measure = 'P2' THEN dbo.prod_Master.P2_Pct WHEN dbo.prod.Measure = 'P3' THEN dbo.prod_Master.P3_Pct WHEN dbo.prod.Measure = 'P4' THEN dbo.prod_Master.P4_Pct
I have a view I've created which displays client sortname, partner and date added which displays 7 results.
When I add another table to this view to display the Industry it then only gives me 4 results as the other 3 results have no Industry instead of giving me the 7 results and showing the Industry column as empty for the other 3.
Is there a way I can make it show all 7 results and havethe column where the industry is empty display the other results instead of not displaying any results at all for them?
Script: SELECT dbo.cdbClient.cltSortName AS ClientName, dbo.vcltAttrib4.ainTVal AS ClientPartner, dbo.vcltAttrib422.ainDVal AS [Date Added], dbo.cdbAttribInst.ainTVal AS Inudstry FROM dbo.cdbClient LEFT OUTER JOIN dbo.cdbObject ON dbo.cdbClient.cltCategoryID = dbo.cdbObject.objID LEFT OUTER JOIN
[Code] ....
In the above script the cbdAttribInst table has the Industry column I need which is 'ainTVal'...
