Transact SQL :: COLUMN NAME Conflicts With Type Of Other Columns Specified In UNPIVOT List
Jul 31, 2015
I am trying to convert all columns to rows in sql, but giving an error.
SELECT Employee, Orders
FROM
(SELECT CisId, [Z_Id], [ModuleType]
FROM CIS) p
UNPIVOT
(Orders FOR Employee IN
(CisId, [Z_Id], [ModuleType])
)AS unpvt;
Error:Â The type of column "Z_Id" conflicts with the type of other columns specified in the UNPIVOT list.If I remove "Z_Id" from selection then giving same error for ModuleType also.
View 6 Replies
ADVERTISEMENT
Nov 25, 2015
I have 3 different companies that share the same ticket_types(CRMS System). I need to display the Ticket Types and the 3 company's Ticket Count:
Ticket Type | Company A Count | Company B Count | Company C Count
I can get the information individually for each company, but if a company doesn't have a ticket in one of the ticket_types, then it isn't displayed in a row. So, I tried to write the following, which isn't pulling back any data.
DECLARE @startdate date = '20150306'
DECLARE @enddate date = '20151031'
DECLARE @AcctGrp varchar(20) = '111'
;WITH TType
AS
(
SELECT ctp.description as TicketType
[Code] .....
If I run each SELECT individually from above (excluding the last SELECT), it works and I get the following:
TicketType
AR Request Credit
Availability/Rush
Cancel Order
Credit Card Payment
Expedite Order
Freight Quote
[Code] ...
How to get the query results? Am I even close to getting it right?
View 3 Replies
View Related
Sep 3, 2015
I want to use column name to be join another tables.
I have invoice table to store detail of invoice and post some column ' s record to another table .
Invoice table
Invoice_Name  |  Invoice_Amount |  Invoice_Vat | Invoice_Total
Inv001 Â Â Â Â Â Â | Â Â Â Â Â 1000 Â Â Â Â Â | Â Â Â 70 Â Â Â Â Â | Â Â 1070 Â Â Â Â Â Â Â
Account_table
Account_No | Â Account_Number | Data_Source
JV001      |   1111        |   Invoice_Amount  ---->1000
JV001      |   1112        |   Invoice_Vat     ---->   70
JV001      |   1113        |   Invoice_Total    ----> 1070Â
I want to join  Invoice table to Account_table
ON  Invoice_Amount , Invoice_Vat , Invoice_Total with Data_Source
The way i got so far I unpivot  Invoice table  column into row and join with Account_table .
The problem is ,  if the column in Invoice_table are created , I must used dynamic columns to do this in sql query.
View 7 Replies
View Related
Aug 2, 2006
Hi All,
I am working on a column chart type (stacked column sub-type) report.
Our customer requires us that the space(padding) between the columns should be a constant(including the space between the Y-axis and the first column). I know how to set the width of the columns, but I really don't know how to set the width of the space between them. The columns just varies the space between them automatically according to the number of the columns (the number of the columns is not certain).
Thanks a lot in advance!
Danny
View 2 Replies
View Related
Jul 31, 2015
the field "Expertise" contains available options "Accounting", "Language", "Engineering" and so on. How do I list all the available options for the field "Expertise" in ms sql 2014?
View 5 Replies
View Related
Mar 22, 2014
I get the following error , when I execute my CTE on SQL Server 2008 R2.CTE1 has more columns than were specified in the column list
--SQL CODE IS AS BELOW
WITH CTE1 (AUCTIONID,[Open Time], [Response SLA Broken], [Response SLA Deadline], [Response SLA Actual], [Responsetime SLA Broken Before Transfer], [Resolution SLA Broken], [Resolution SLADeadline], [Resolutiontime SLA Broken
[code]...
View 2 Replies
View Related
Feb 24, 2015
I have the following 2 Query's - case when Table has no Identity Column and other with identity Column . I am planning to make it to single Query .
Query 1:
SELECT @ColumnNamesWhenNoIdentity = COALESCE(@ColumnNamesWhenNoIdentity + ',', '') + Name +'= SOURCE.'+Name
FROM sys.columns WITH(NOLOCK) WHERE object_id =
(
SELECT sys.objects.object_id
FROM sys.objects WITH(NOLOCK)
INNER JOIN sys.schemas WITH(NOLOCK) ON sys.objects.schema_id = sys.schemas.schema_id
WHERE sys.objects.TYPE = 'U' AND sys.objects.Name = 'Testing1' AND sys.schemas.Name ='dbo'
)
Query2:
SELECT @ColumnNamesWhenNoIdentity = COALESCE(@ColumnNamesWhenNoIdentity + ',', '') + Name +'= SOURCE.'+Name
FROM sys.columns WITH(NOLOCK) WHERE is_identity != 1 AND object_id =
(SELECT sys.objects.object_id FROM sys.objects WITH(NOLOCK)
INNER JOIN sys.schemas WITH(NOLOCK) ON sys.objects.schema_id = sys.schemas.schema_id
WHERE sys.objects.TYPE = 'U' AND sys.objects.Name = 'Testing2' AND sys.schemas.Name ='dbo'
)
View 8 Replies
View Related
Jun 30, 2006
Dear All:
When i try to debug the northwindoledb example with VS2005 i get the foolwing error message.
Is there a way to configure the solution to run witn WM5 emulator.
The application run with wm5 emulator when i set the plataform to Windows Mobile 2003, but i want to use the wm5 platform to debug my application with wm5 emulator.
------ Rebuild All started: Project: northwindoledb, Configuration: Debug Windows Mobile 5.0 Pocket PC SDK (ARMV4I) ------
Deleting intermediate and output files for project 'northwindoledb', configuration 'Debug|Windows Mobile 5.0 Pocket PC SDK (ARMV4I)'
Compiling...
Employees.cpp
northwindoledb.cpp
stdafx.cpp
Generating Code...
Compiling resources...
Linking...
coredll.lib(COREDLL.dll) : fatal error LNK1112: module machine type 'THUMB' conflicts with target machine type 'ARM'
Build log was saved at "file://d:Proyectos_VS2005Northwindoledb_WM2003_SQLEW_Windows Mobile 5.0 Pocket PC SDK (ARMV4I)DebugBuildLog.htm"
northwindoledb - 1 error(s), 0 warning(s)
========== Rebuild All: 0 succeeded, 1 failed, 0 skipped ==========
View 1 Replies
View Related
Oct 22, 2015
I had to enable identity_insert on a bunch of tables and I have already done that. Now I need to modify my insert into select * from statements to include column list names along with identity columns for select as well as insert statements. The DDL is same but they are both different databases.There are almost 100 tables that it needs to be modified. Is there a way we can generate scripts for insert and select for each individual table along with their column lists including the identity column?
View 7 Replies
View Related
Sep 17, 2015
How can we get the list of clustered columnstore index in a database in sql server 2014
View 3 Replies
View Related
Jun 2, 2015
I have a table that is used to build rules. The rules point to other columns in other tables and usually contain only one value (i.e. ABC). But one of the options is to add a comma-separated list of SSNs (i.e. 123123123,012012012,112231122). I am trying to build a single query that allows me to leverage that list to get multiple rows from another table.
This obviously works:
SELECT * FROM vw_Person_Profile P (NOLOCK)
WHERE P.PrsnPIISSN_Chr IN ('123123123','012012012','112231122')
But this does not:
SELECT * FROM vw_Person_Profile P (NOLOCK)
WHERE P.PrsnPIISSN_Chr IN (
SELECT '''' + REPLACE(CONVERT(VARCHAR(4000),txtFieldValue), ',', ''',''') + ''''
FROM MassProcessing_Rules PR
WHERE PR.intRuleID = 10
)
View 5 Replies
View Related
Sep 23, 2015
I've unpivoted some data and stored it in a temp table variable
idNumFreqDtFreqrn
16100120120101M2
16100120120101M3
16100120100101M4
16100120100101M5
16100120060101M6
16100120000929Q7
16100119990101A8
16100119970101M9
Using the above data, if two rows have the same FreqDt, I want to see the record with the lowest row number.
So it should look like the below
idNumFreqDtFreqrn
16100120120101M2
16100120100101M4
16100120060101M6
16100120000929Q7
16100119990101A8
16100119970101M9
I've used the below code to accomplish it
SELECT DISTINCT
CASE WHEN t2.idNum IS NULL THEN t1.idNum ELSE t2.idNum END,
CASE WHEN t2.FreqDt IS NULL THEN T1.FreqDt else t2.FreqDt END,
CASE WHEN t2.freq is null then t1.freq else t2.freq end
FROM @tmptbl as t1 LEFT JOIN @tmptbl as t2
ON t1.idNum = T2.idNum
AND t1.FreqDt = t2.FreqDt
AND t1.rn = (t2.rn-1)
After all this, I'm supposed to condense the result set to only include sequential frequency dates with unique frequencies.should look like below (this is where I'm stuck)
idNumFreqDtFreq
16100120060101M
16100120000929Q
16100119990101A
16100119970101M
answer is below:
SELECT T1.*
FROM @t as t1 LEFT JOIN @t as t2
ON t1.idnum = T2.idnum
AND t1.freq = t2.freq
AND t1.rn = (t2.rn-1)
WHERE t2.idnum IS NULL
View 5 Replies
View Related
Nov 21, 2014
SET NOCOUNT ON;
IF OBJECT_ID('dbo.TBL_SAMPLE_DATA') IS NOT NULL DROP TABLE dbo.TBL_SAMPLE_DATA;
CREATE TABLE dbo.TBL_SAMPLE_DATA
(
ROW_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL
,Airline VARCHAR(50) NOT NULL
[Code] ....
I have to compare the Aircraft1 and Aircraft1_unsub based on condition as below:
[case
when Aircraft1='N' or Aircraft1_unsub='Y' then 0
else 1
end ]
Based on the comparision output i have to update the main table with the outputvalue for each Aircraft based on the Airline
update t set t.Aircraft1=outputvalue
from main_table t
inner join TBL_SAMPLE_DATA s
on t.Airline=s.Airline
update t set t.Aircraft2=outputvalue
from main_table t
inner join TBL_SAMPLE_DATA s
on t.Airline=s.Airline
View 7 Replies
View Related
Apr 3, 2007
Hi there,
I'm trying to unpivot a table in SSIS: The pivoted table basically looks like
ID DATE1 TEXT1 DATE2 TEXT2
----------------------------------
ID1 D1 T1 D2 T2
...
The unpivoted result looks like
ID DATE TEXT
-------------------
ID1 D1 T1
ID1 D2 T2
€¦
It works, but the one problem I am facing is: If D1 IS NULL in the pivoted table then D1 in the unpivoted table contains some strange value that is neither NULL nor a valid datetime. In a data viewer I get the in the DATE field "Fehler: Die Parameter Year, Month and Day beschreiben eine nicht darstellbare Datetime." (i. e. "Error: The parameters Year, Month and Day form an invalid datetime."). The loading of the OLE DB target yields an error because the DATE value cannot be converted to a valid datetime.
Anybody having a solution to this?
View 6 Replies
View Related
Nov 9, 2015
I just need the date, hour, and minute...not the micro seconds. Do I have to DATEPART and concatenate each or is there any way to simply truncate the milliseconds from it? Or is there a date format to put extract and report on it as...
MM/DD/CCYY HH:MM:SS AM
I see there is a format 131 that puts it in..
DD/MM/YYYY HH:MM:SS:MMMAM
View 7 Replies
View Related
Oct 15, 2015
how to split a single column into multiple columns with | delimited. For example I have column old_values in this I have a data with | delimited and I need to split this data into multiple columns.
Old_values
xxx|yyy|zzz|aaa|bbb|ccc|ddd
into
A Â Â B Â Â Â C Â Â Â Â D Â Â Â E Â Â Â Â F Â Â Â G
xxx  yyy   zzz   aaa   bbb   ccc   ddd
View 41 Replies
View Related
Mar 31, 2009
I have a situation where i need to unpivot multiple columns using ssis. The data looks like
Name Age products1 products2 orders1 orders2
abc     23   cycle       radio         12        24
as
Name  Age Products  orders
abc    23     cycle      12
abc   23      radio      24
Is it possible to do this using the unpivot task in ssisMy actual data is has 18 columns which needed to be unpivoted into one and another 18 into another one.when using unpivot task it gives an error saying only one pivotvalue key is allowed.
View 6 Replies
View Related
Jul 8, 2015
I made a select query which shows following output as shown in picture .Â
Now I want to add one more column in this query to show current bags  and Bags in these 2 columns i want to show calculation like   in first rows currentbags column (Receivedbags-DeleiveredBags) and in currentWeight column RecivedWeight+loss-gain-Deliverdweight) which is 1400 and  697.5 after that in secound row i want to add frist rows currentbags value+ second rows (Receivedbags-DeleiveredBags) and same in weight like daily stock register so output looked like below image
There is one more column common date according to which i have to make calculation like
rid   commondate  recdate  recbags     recweight       loss gain  delbags   delwght
101215109 Â Â 01/01/2015 Â 07/01/2015 Â Â Â 1400 Â Â Â Â Â Â Â Â Â 697.5 Â Â Â Â Â Â Â Â 0 Â Â Â Â Â Â Â Â Â 0 Â Â Â Â Â Â 0 Â Â Â Â Â Â 0
101215110 Â 02/01/2015 Â Â Â 08/01/2015 Â Â Â Â 560 Â Â Â Â Â Â Â Â Â Â 279.64 Â Â Â Â Â Â 0 Â Â Â Â Â Â Â 0 Â Â Â Â 0 Â Â Â Â Â Â Â 0
View 7 Replies
View Related
Jun 4, 2015
I have a requirements to make a single column using a date from multiple columns. below my DDL and sample.
Create table #Mainsample
(ItemNum nvarchar(35), ItemDate datetime, OPType int)
Insert into #Mainsample(ItemNum,ItemDate, OPType) values ('M9000000000020510095','2015-05-01 18:38:48.840',5)
Insert into #Mainsample(ItemNum,ItemDate, OPType) values ('M9000000000020510094','2015-05-02 20:38:40.850',5)
Insert into #Mainsample(ItemNum,ItemDate, OPType) values ('M9000000000020510092','2015-05-02 21:40:42.830',5)
Insert into #Mainsample(ItemNum,ItemDate, OPType) values ('353852061764483','2015-05-02 09:25:10.800',5)
[code]....
View 10 Replies
View Related
Nov 2, 2015
I have a table and one of the column have tab delimited value and I need to separate the tab delimited values and keep them in separate rows.
ID      Name                 State     Country
1      Scott, Ricky           NSW      AUS
2       Martin                  VIC        AUS
3   James, Peter,John    WA       AUS
ID column is not a primary key. I want the output columns to identify the comma (,) and put them in separate rows as below
ID      Name                 State     Country
1      Scott                    NSW      AUS
1      Ricky                     NSW      AUS
2       Martin                  VIC        AUS
3     James                     WA       AUS
3     Peter                      WA       AUS
3     John                      WA       AUS
How to strip the camma(,) in SQL?
View 3 Replies
View Related
Jul 9, 2015
I've created a SSIS package which takes a matrix from Excel file and insert into SQL table. It works perfectly! However, if I would add a new column into that matrix in Excel. Unpivot tool should take into process dynamically. Is there a way to provide this automatically?Â
View 4 Replies
View Related
Jun 17, 2015
I have a SQL query like this
select CurrencyCode,TransactionCode,TransactionAmount,COUNT(TransactionCode) as [No. Of Trans] from TransactionDetails where CAST(CurrentTime as date)=CAST(GETDATE()as date) group by TransactionCode, CurrencyCode,TransactionAmount order by CurrencyCode
As per this query I got the result like this
CurrencyCode TransactionCode TransactionAmount No.OfTrans
AEDÂ Â Â BNTÂ Â Â 1Â Â Â 1
AEDÂ Â Â BNTÂ Â Â 12Â Â Â 1
AEDÂ Â Â SCNÂ Â Â 1Â Â Â 1
AEDÂ Â Â SNTÂ Â Â 1Â Â Â 3
[Code] ....
But I wish to grt result as
CurrencyCode TransactionCode TransactionAmount No.OfTrans
AEDÂ Â Â BNTÂ Â 13Â Â Â 2
AEDÂ Â Â SCNÂ Â Â 1Â Â Â 1
AEDÂ Â Â SNTÂ Â Â 11Â Â Â 7
AFNÂ Â Â BPCÂ Â Â 8Â Â Â 6
[Code] ....
I also tried this
select CurrencyCode,TransactionCode,TransactionAmount,COUNT(TransactionCode) as [No. Of Trans]
from TransactionDetails where CAST(CurrentTime as date)=CAST(GETDATE()as date)
group by TransactionCode order by CurrencyCode
But of course this codes gives an error, but how can I get my desired result??
View 5 Replies
View Related
Jul 21, 2015
I'm wanting to create reports in SSDT 2012 which is connected to a 2008R2 database. I want to have parameters on for my reports where a user is able to select a year such as 2014. Unfortunate my table containing the data has two columns with a date value. the first is of the int type and contains an epoch formatted date. The second is a varchar type and shows the date as 2015-07-01 08:00:00. I would like to be able to write a query to return the year, monthnumber and daynumber from either of these columns.Â
View 5 Replies
View Related
Jul 7, 2015
I've got a table with 6 fields :
EmployeeAccess
(MasterID, LoginID, AccessID, Storage1, Storage2, Storage3)
that needs to be updated using the data in the following spreadsheet
NewEmployeeAccessData
(ID, MasterID, AccessID1, LoginID1)
There is a 1:1 relationship between the two tables..I'm trying to code a pair of update statements on the EmployeeAccess table (1 for LoginID, 1 for AccessID) with the following logic:
If LoginID is NULL, then Update LoginID with new LoginID1 value,
If LoginID is not null, and Storage1 is NULL then Update Storage1 with New LoginID1 values
If LoginID is not null, and Storage1 is not NULL and Storage2 is NULL then Update Storage2 with New LoginID1 values
etc etc...
The same applies when trying to populate the AccessID column
If AccessID is NULL, then Update AccessID with new AccessID1 value,
If AccessID is not null, and Storage1 is NULL then Update Storage1 with New AccessID1 values
If AccessID is not null, and Storage1 is not NULL and Storage2 is NULL then Update Storage2 with New AccessID1 values
etc etc.
I have no control over the schema of this table so I'm trying to work the logic on how to update the columns in my table only if the corresponding column data is NULL, else update the next non NULL Storage column.
View 7 Replies
View Related
Oct 19, 2015
how to do a check for 2 columns. As long as there is data for at least one of the columns I want to return rows.
Example Data
create table test
(
ID int,
set1 varchar(50),
set2 varchar(50),
[code]....
View 4 Replies
View Related
Sep 2, 2015
Below is the SQL Query i am currently having
SELECT IG_FinancialTransactionSummary.ClaimNum,IG_FinancialTransactionSummary.TransactionCode,
IG_FinancialTransactionSummary.TransactionDate,IG_FinancialTransactionSummary.Username,
FinancialTransactionSummaryTest.ClaimNum,FinancialTransactionSummaryTest.TransactionAmount,
FinancialTransactionSummaryTest.UserName--,FinancialTransactionSummaryTest.TransactionDate
[Code] ....
And here is the result dataset
ClaimNumTransactionDateUsername ClaimNum TransactionAmountUserName
2000074 20150209jerry.witt 2000074 -10000DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL
2000074 20150626DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL 2000074 -10000DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL
[Code] .....
So,if we look at the result set, we notice 2 conditions where the IG_FinancialTransactionSummary.Username is like 'Data' and if we see the transaction date then sometimes that is the max transaction date or sometimes there are transactions that happened after but that doesn't have like '%data%' in username . So, i need to add a new column to my sql query which should basically verify if the username is like '%data%' and if that is the max(transaction date) or even if there are any transactions after that doesn't have like '%data%' then YES else No.
View 13 Replies
View Related
Apr 10, 2008
Dear All,
We are trying to unpivot the columns into rows but the colunms are changing dynamically, want to know how to set the dynamic value or variable in unpivot query, query is mentioned below:
declare @aw Varchar(100)
set @aw = '1990,1991'
SELECT [name], [year], [data]
FROM (SELECT * FROM t1 where [name] != 'name') p
UNPIVOT
([data] FOR [year] IN
(@aw)
)AS unpvt
The above query while executing geting error message "Incorrect syntax near '@aw'"
please can anybody solve this issue.
Thanks,
Syed
View 6 Replies
View Related
Apr 10, 2008
Hi All,
We are trying to unpivot the columns into rows but the colunms are changing dynamically, want to know how to set the dynamic value or variable in unpivot query, query is mentioned below:
declare @aw Varchar(100)
set @aw = '1990,1991'
SELECT [name], [year], [data]
FROM (SELECT * FROM t1 where [name] != 'name') p
UNPIVOT
([data] FOR [year] IN
(@aw)
)AS unpvt
The above query while executing geting error message "Incorrect syntax near '@aw'"
please can anybody solve this issue.
Thanks,
Syed
View 4 Replies
View Related
Jan 14, 2008
I am busy extending a VB6 app to talk to SQL Server Express 2005 and have come across a naming conflict. Some of the columns in the application's Access 97 tables is "index" which obviously exists as a T-SQL keyword and therefore any queries I perform including this column throws a syntax error. I tried prefixing the columns with their table names as in TableName.Index, but this still throws up the syntax error which I thought was a bit odd. I preferably want a fix that will be Access as well as T-SQL compliant but if that's not possible I will just write a string converter that does the job based on anyone's suggestions. TIA
View 4 Replies
View Related
Oct 17, 2006
Hi all,
I have more than 1 set of columns I'm trying to normalize, can an unpivot transform handle 2 unpivots around DIFFERENT keys in the same transform?
If not, would the suggestion be unpivot into temp tables for whatever needs normalizing, than run an update query to generate the row that is desired?
Thanks in advance.
Mark
View 2 Replies
View Related
Nov 16, 2015
For our ETL process, we maintain a TransformationList table that has the source view and the destination table. Data is copied from the view into the table (INSERT INTO). I am trying to find column names in the Views that are not column names in the associated Table.
In the below example, want to end up with three records:
 1, View1, Column4
 2, View2, Column4
 2, View2, Column5
I have it almost working, except that there is a table, ChangeColPrefix table, that is used by the ETL process to change some of the view's column name prefixes. Some of the source views have column names with prefixes that do not match the destination table column names. Say view SouthBase has all the column names prefixed with SB - like SBAcct, SBName. And the Destination table of Area District has ADAcct, ADName. There would be a row in the ChangeColPrefix for SouthBase, SB, AD, 1, 2 that would be used by the ETL process to create the INSERT INTO Area District From SouthBase.
I need to use this ChangeColPreifx to find my unmatching columns between my source views and destination tables. With out that table SBAcct and SBName from SouthBase will not appear to match the columns of ADAcct and ADName, but they do match.
I want to end up with these three records as non-matching:
View1, Column4
View2, Column4
View2, Column5
View1 has Salumn2 and View2 has Salumn5, and they must be changed to Column2 and Column5 as per the ChangeColPrefix table before running the Select from INFORMATION_SCHEMA.COLUMNS EXCEPT Select from INFORMATION_SCHEMA.COLUMNS looking for unmatched columns.
/***** Set Up Test Data *****/
-- Create 2 test views
IF EXISTS(SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[View1]'))
DROP VIEW dbo.[View1]
GO
CREATE VIEW View1
AS SELECT '1' AS Column1 , '2' AS Salumn2 , '4' AS Column4;
[Code] ....
View 3 Replies
View Related
Apr 10, 2008
Dear All,
We are using UnPivot task to convert the columns into rows using the Excel File as source. But the Excel file column names are changing frequenly sometimes its having only 4 columns sometimes its 10 columns.
Everytime we are checking and unchecking the column list in Unpivot Task.
can anybody help us to solve this issue that Unpivot task should take the column name dynamically.
Thanks,
Syed
View 1 Replies
View Related
Jan 27, 2015
I am having problem with the unpivot function of sql 2012, i unpivot my column then i get the result that i wanted but the error that i was encountering was the unpivot is automatically sort the column in alphabetically order which is not I desire,
Here is my code
@syear nvarchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
[Code] ....
View 1 Replies
View Related
