Unpivot Transformation And Expressions
Jan 12, 2007
I am using the unpivot transformation, but I can't figure out how to use an expression in the Pivot Key Value.
The denormalized table I want to unpivot has columns like Sunday_Qty, Monday_Qty, Tuesday_Qty, etc. Just before the unpivot component, I inserted a derived column component that adds fields like DateSun, DateMon, DateTue, etc. that resolves to values like 01/07/2007, 01/08/2007, etc.
So for the various rows in the Unpivot Transformation Editor, I entered DateSun, DateMon, DateTue, etc. for the Pivot Key Value, and "EntryDate" for the pivot key value column name.
The data pipeline gets unpivoted correctly, but the rows have the literal values "DateSun", "DateMon", etc. in the EntryDate column.
How do I tell SSIS to use the DateSun column instead of the string "DateSun"?
-Larry
View 9 Replies
ADVERTISEMENT
Dec 10, 2007
Hi,
my Source-Files are not normalized, so I want some Columns unpivot in Rows. For example my base Table looks something like that
ID ... CustomerNo1 ... CustomerName1 ... CustomerPhone1 ... CustomerNo2 ... CustomerName2 ... CustomerPhone2 ...
After Unpivoting I would like to have a structure like that, where all Attributevalues keep the same, but the different Customers in its own Row
ID ... CustomerNo ... CustomerName ... CustomerPhone
So I take the Unpivot-Transformation Task and configure it with this Values:
Input Column ....... Destination Column ........ Pivot Key Value
CustomerNo1 ....... CustomerNo ................... Customer1
CustomerName1 ... CustomerName .............. Customer1
CustomerPhone1 .. CustomerPhone .............. Customer1
CustomerNo2 ....... CustomerNo ................... Customer2
CustomerName2 ... CustomerName .............. Customer2
CustomerPhone2 .. CustomerPhone ............. Customer2
My Data Flow works fine and everything is going without any errors, but my Destination is without the 3rd Attribute (CustomerPhone). Whats wrong in my configuration?
Thanks ...
Jonas
View 3 Replies
View Related
Jul 18, 2006
I totally give up.
I need to import an csv file with system monitor performance counters into a sql server 2005 database
example csv format:
"(PDH-CSV 4.0) (W. Europe Daylight Time)(-120)","Server1Memory\% Commited Bytes In Use", )","Server2Memory\%Commited Bytes In Use"
"07/18/2006 10:08:57.295","24.701095057336488","30.701095077776488"
and I want it to transform it into:
Time ServerName Memory\% Commited Bytes In Use
07/18/2006 10:08:57.295 Server1 24.701095057336488
07/18/2006 10:08:57.295 Server2 30.701095077776488
I have two problems:
I need to trim the servername from the column values
I need to insert an extra column ServerName and put those server names in it
I tried Unpivot and Fuzzy grouping in SSIS but it's al too fuzzy for me...
Any pointers someone?
Many thanks!
Regards,
Mop
View 3 Replies
View Related
Jan 23, 2006
Hi, I am trying to use the Unpivot Transformation, and I have the following mapped out, following the example from the SQL Server 2005 BOL Unpivot Transformation:
Input
Destination Column
Subject
ValueNumeric
ValueString
ValueNumeric
Pivot Key Value
RACE
OTHRRACE
GENDER
Column Name
Subject
RACE
OTHRRACE
GENDER
Data Records
99999
1
NULL
1
88888
NULL
Hispanic
2
77777
2
NULL
2
Desired Output
Pivot Key
FALSE
TRUE
Col Name
Subject
Question
ValueNumeric
ValueString
Data Records
99999
RACE
1
NULL
99999
OTHRRACE
NULL
NULL
99999
GENDER
1
NULL
88888
RACE
NULL
NULL
88888
GENDER
2
NULL
88888
OTHRRACE
NULL
Hispanic
77777
RACE
2
NULL
77777
GENDER
2
NULL
77777
OTHRRACE
NULL
NULL
This doesn't work, however, as I have multiple data types. So unless someone knows a better way, I had to split it into two Unpivot Transformations, which I later Sort and Merge together. The two Unpivot Transformations look like this:
Input
Destination Column
Subject
ValueNumeric
ValueNumeric
Pivot Key Value
RACE
GENDER
Column Name
Subject
RACE
GENDER
Data Records
99999
1
1
88888
NULL
2
77777
2
2
Output
Pivot Key
FALSE
TRUE
Col Name
Subject
Question
ValueNumeric
Data Records
99999
RACE
1
99999
GENDER
1
88888
RACE
NULL
88888
GENDER
2
77777
RACE
2
77777
GENDER
2
Input
Destination Column
Subject
ValueString
Pivot Key Value
OTHRRACE
Column Name
Subject
OTHRRACE
Data Records
99999
NULL
88888
Hispanic
77777
NULL
Output
Pivot Key
FALSE
TRUE
Col Name
Subject
Question
ValueString
Data Records
99999
RACE
NULL
99999
OTHRRACE
NULL
99999
GENDER
NULL
88888
RACE
NULL
88888
GENDER
NULL
88888
OTHRRACE
Hispanic
77777
RACE
NULL
77777
GENDER
NULL
77777
OTHRRACE
NULL
The first Unpivot works great, because the data is mandatory. However, the second is full of NULLs so the actual output from the second Unpivot, prior to the merge looks like this:
Actual Output
Subject
Question
ValueString
99999
88888
OTHRRACE
Hispanic
77777
But I expected (and need) this:
Desired Output
Subject
Question
ValueString
99999
OTHRRACE
NULL
88888
OTHRRACE
Hispanic
77777
OTHRRACE
NULL
I hope someone can give me some advice on this!
Regards,
Richard Hein
View 3 Replies
View Related
Mar 6, 2008
Hi
I have an Issue. Please find the sample of my data source
StudentID
Fee 1
Fee 1 Currency Type
Fee2
Fee 2 Currency Type
1
10
USD
20
INR
2
45
EUR
20
USD
If I need to transform this data, it should be like below Table.
ID
StudentId
FeeType
FeeAmount
CurrencyType
1
1
Fee 1
10
USD
2
1
Fee 2
20
INR
3
2
Fee 1
45
EUR
4
2
Fee 2
20
USD
I have selected columns Fee 1 and Fee 2 in Unpivot Transformation. I set the "FeeAmount" as Destination Column Name and "FeeType" as Pivot Key Value Column Name. I left the Pivot Key Value as is for all the selected columns. So far no issue that I am able to get the Fee Type and Fee Amount columns for my destination table.
But the issue is how to get the Currency Type for each corresponding coulmns? I was not able to get the currency type for each student. I hope you got my point. Please let me know If have further queries and hel me out to find this.
View 5 Replies
View Related
Sep 18, 2006
Hi,
I'm creating a custom data flow transformation in c#.
I would like to use expressions within this component in the same way as in the derived column component: specifying the expression as a custom property of an output column, then evaluating this expression for each row of the buffer and using this evaluated expression to populate my output column values.
So I've added an custom expression on my output column, and set its expression type to CPET_NOTIFY
IDTSCustomProperty90 exp = col.CustomPropertyCollection.New();
exp.Name = "Expression";
exp.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
But in the ProcessInput method I don't manage to get the evaluated expressions, when I use exp.Value I get my expression definition and not its evaluation.
Is there a way to get these evaluated expressions ?
Thanks,
Stéphane
View 6 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
Dec 11, 2007
I want to have this query insert a bunch of XML but i get this error...
Msg 128, Level 15, State 1, Procedure InsertTimeCard, Line 117
The name "ExpenseRptID" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
Msg 128, Level 15, State 1, Procedure InsertTimeCard, Line 151
The name "DateWorked" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
What am i doing wrong...Can anyone help me out!! Thanks!!
p.s I know this query looks crazy...
Code Block
IF EXISTS (SELECT NAME FROM sysobjects WHERE NAME = 'InsertTimeCard' AND type = 'P' AND uid=(Select uid from sysusers where name=current_user))
BEGIN
DROP PROCEDURE InsertTimeCard
END
go
/*********************************************************************************************************
** PROC NAME : InsertTimeCardHoursWorked
**
** AUTHOR : Demetrius Powers
**
** TODO/ISSUES
** ------------------------------------------------------------------------------------
**
**
** MODIFICATIONS
** ------------------------------------------------------------------------------------
** Name Date Comment
** ------------------------------------------------------------------------------------
** Powers 12/11/2007 -Initial Creation
*********************************************************************************************************/
CREATE PROCEDURE InsertTimeCard
@DateCreated DateTime,
@EmployeeID int,
@DateEntered DateTime,
@SerializedXML text,
@Result int output
as
declare @NewTimeCardID int
select @NewTimeCardID = max(TimeCardID) from OPS_TimeCards
-- proc settings
SET NOCOUNT ON
-- local variables
DECLARE @intDoc int
DECLARE @bolOpen bit
SET @bolOpen = 0
--Prepare the XML document to be loaded
EXEC sp_xml_preparedocument @intDoc OUTPUT, @SerializedXML
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
--The document was prepared so set the boolean indicator so we know to close it if an error occurs.
SET @bolOpen = 1
--Create temp variable to store values inthe XML document
DECLARE @tempXMLTimeCardExpense TABLE
(
TimeCardExpenseID int not null identity(1,1),
TimeCardID int,
ExpenseRptID int,
ExpenseDate datetime,
ProjectID int,
ExpenseDescription nvarchar(510),
ExpenseAmount money,
ExpenseCodeID int,
AttachedRct bit,
SubmittoExpRep bit
)
DECLARE @tempXMLTimeCardWorked TABLE
(
TimeCardDetailID int not null identity(1,1),
TimeCardID int,
DateWorked DateTime,
ProjectID int,
WorkDescription nvarchar(510),
BillableHours float,
BillingRate money,
WorkCodeID int,
Location nvarchar(50)
)
-- begin trans
BEGIN TRANSACTION
insert OPS_TimeCards(NewTimeCardID, DateCreated, EmployeeID, DateEntered, Paid)
values (@NewTimeCardID, @DateCreated, @EmployeeID, @DateEntered, 0)
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
--Now use @intDoc with XPATH style queries on the XML
INSERT @tempXMLTimeCardExpense (TimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep)
SELECT @NewTimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep
FROM OPENXML(@intDoc, '/ArrayOfTimeCardExpense/TimeCardExpense', 2)
WITH ( ExpenseRptID int 'ExpenseRptID',
ExpenseDate datetime 'ExpenseDate',
ProjectID int 'ProjectID',
ExpenseDescription nvarchar(510) 'ExpenseDescription',
ExpenseAmount money 'ExpenseAmount',
ExpenseCodeID int 'ExpenseCodeID',
AttachedRct bit 'AttachedRct',
SubmittoExpRep bit 'SubmittoExpRep')
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
-- remove XML doc from memory
EXEC sp_xml_removedocument @intDoc
SET @bolOpen = 0
INSERT OPS_TimeCardExpenses(TimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep)
Values(@NewTimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep)
select @NewTimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep
from @tempXMLTimeCardExpense
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
-- For time worked...
INSERT @tempXMLTimeCardWorked(TimeCardID, DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location)
SELECT @NewTimeCardID, DateWorked, ProjectID, WorkDescription, BilliableHours, BillingRate, WorkCodeID, Location
FROM OPENXML(@intDoc, '/ArrayOfTimeCardWorked/TimeCardWorked', 2)
WITH ( DateWorked DateTime 'DateWorked',
ProjectID datetime 'ProjectID',
WorkDescription nvarchar(max) 'WorkDescription',
BilliableHours float 'BilliableHours',
BillingRate money 'BillingRate',
WorkCodeID int 'WorkCodeID',
Location nvarchar(50)'Location')
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
-- remove XML doc from memory
EXEC sp_xml_removedocument @intDoc
SET @bolOpen = 0
INSERT OPS_TimeCardHours(TimeCardID, DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location)
Values(@NewTimeCardID,DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location)
select @NewTimeCardID ,DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location
from @tempXMLTimeCardWorked
-- commit transaction, and exit
COMMIT TRANSACTION
set @Result = @NewTimeCardID
RETURN 0
-- Error Handler
ErrorHandler:
-- see if transaction is open
IF @@TRANCOUNT > 0
BEGIN
-- rollback tran
ROLLBACK TRANSACTION
END
-- set failure values
SET @Result = -1
RETURN -1
go
View 1 Replies
View Related
Jun 5, 2006
Hi,
If you have two synchronous transformation components and the input of the second is connected to the output of the first, does the first transformation process (loop through) all rows in the buffer before outputting these rows to the second transformation? Or does the first transformation output each individual row to the second transormation as soon as it has finished processing it?
Thanks in advance,
Lawrie.
View 5 Replies
View Related
Apr 22, 2015
tell me the difference between Audit transformation and rowcount transformation.
Because audit and rowcount transformation will provide the environment variables.
Only difference i am finding is rowcount returns the count of rows its updating .
Apart from these is there any other difference?
Tell me the scenario where i need to use the audit transformation.
View 3 Replies
View Related
Apr 24, 2008
Hi, all,
Could some body give me little help on this? The UNPIVOT is new to sql05, and I did not find many useful helps out there.
My understanding is that it will normalize the data in column name (the UNPIVOT FOR part). As my sample shows here, I want to get that from one of the row.
Here is the sample, and of cause the Select PIVOT part is not working.
Also, if UNPIVOT is not the right way to go after it, what is the right way?
Thanks!
USE tempdb;
GO
If Object_ID('#pvtSales') IS NOT NULL
Drop table dbo.#pvtSales;
GO
Create Table #pvtSales (Prod_ID varchar(10), Prod_desc varchar(10), Col3 varchar(12), Col4 varchar(10), Col5 varchar(12), Col6 varchar(10), Col7 varchar(12), Col8 varchar(10))
INSERT INTO #pvtSales
Select '111', 'my goody', '1', '1.5', '0', '1.5', '1', '1.3' UNION ALL
Select '222', 'my stuff', '0', '0.5', '1', '0.5', '1', '1.0' UNION ALL
Select NULL, NUll, '2007-06-03', NULL, '2007-06-10', NULL, '2007-06-17', NULL UNION ALL
Select '333', 'my goody3', '1', '2', '0', '2', '1', '2'
Select Prod_ID, Prod_desc, Col3, Col4
FROM #pvtSales
UNPIVOT (Col4 for Col3 in (['06/03/2007'],['06/10/2007'],['06/17/2007']) AS U
The result I am looking for:
Prod_ID/Prod_desc/Sale/Cost/SaleDate
111/my goody/1/1.5/2007-06-03
111/my goody/0/1.5/2007-06-10
111/my goody/1/1.3/2007-06-17
222/my stuff/0/0.5/2007-06-03
...
View 2 Replies
View Related
Sep 7, 2007
Hi there,
I have a table with the following fields:
ID, StartDate, Day1, Day2, Day3. This table is filled with hours.
I want to unpivot this table to get the following:
ID, Date (=Start date), hours
ID, Date + 1 (=Start date + 1), hours
ID, Date + 2 (=Start date + 2), hours
The trouble I have concerns the dates. Please help.
View 2 Replies
View Related
Aug 29, 2006
HI, I have a source that looks like this:
Col1 Code1 Amt1 Col<n>.....Code2 Amt2....
ABC XY 10 FR 345
What I would like to have is this:
Col1 Code Amt Col<n> ...
ABC XY 10
ABC FR 345
I know I could achieve this by using SQL and UNION ALL:
SELECT Col1, Code1 as Code, Amt1 as Amt, Col<n>...
FROM <mySource>
UNION ALL
SELECT Col1, Code2 as Code, Amt2 as Amt, Col<n>...
FROM <mySource>
But I was wondering if the built-in unpivot transform could do the job here.
Thank you
Ccote
View 3 Replies
View Related
Dec 25, 2007
Hi,
I have data stored in a format like this...
RespondantID, Q1, Q2, Q3 ...
1 - Anonymous
1
1
1
1
1
1
1
1
1
1
1
1
1
2 - Anonymous
2
2
2
2
2
2
2
2
2
2
2
2
2
But want to convert it to something like...
SurveyID, RespondantID, QuestionID, Answer
1, 1 - Anonymous, Q1, 1
1, 1 - Anonymous, Q2, 1
...
1, 2 - Anonymous, Q1, 2
1, 3 - Anonymous, Q2, 2
...
Please can someone help.
Thanks!
View 1 Replies
View Related
Jul 24, 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 T2How do I get these results in SSIS?
View 11 Replies
View Related
Apr 24, 2008
hi need help UNPIVOT table
from this
to the example below
id
fname
val
day1
day11
day111
day2
day22
day222
day3
day33
day333
day4
day44
day444
day5
day55
day555
111
aaaa
2
1
2
3
7
8
9
10
11
12
13
14
15
16
17
18
111
aaaa
1
a
a
a
b
b
b
c
c
c
d
d
d
e
e
e
222
bbbb
2
1
2
3
7
8
9
10
11
12
13
14
15
16
17
18
222
bbbb
1
a
a
a
b
b
b
c
c
c
d
d
d
e
e
e
333
cccc
2
333
cccc
1
444
dddd
2
444
dddd
1
*
555
EEE
2
-
-
-
-
555
EEE
1
*
*
*
i need to do this how
id
fname
val
fld1
fld2
fld3
day_name
111
aaaa
2
1
2
3
1
111
aaaa
1
a
a
a
1
111
aaaa
2
7
8
9
2
111
aaaa
1
b
b
b
2
111
aaaa
2
10
11
12
3
111
aaaa
1
c
c
c
3
111
aaaa
2
13
14
15
4
111
aaaa
1
d
d
d
4
111
aaaa
2
16
17
18
5
111
aaaa
1
e
e
e
5
222
bbb
2
1
2
3
1
222
bbb
1
a
a
a
1
222
bbb
2
7
8
9
2
222
bbb
1
b
b
b
2
222
bbb
2
10
11
12
3
222
bbb
1
c
c
c
3
222
bbb
2
13
14
15
4
222
bbb
1
d
d
d
4
222
bbb
2
16
17
18
5
222
bb
1
e
e
e
5
tnx for the help
View 8 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
Jul 20, 2007
Hi,
I need to unpivot an excel worksheet with the following format
Artcode
Model
Qtd1
Price1
Qtd2
Price2
Qtd3
Price3
Article1
Model1
1
10
2
11
1
12
Article2
Model2
3
15
3
12
2
14
Article3
Model3
2
12
2
20
1
15
If I had only one destination column, for ex: Qty, I had no problem, but I need to have 2 destination columns, Qty and Price. Is I set 2 different destinationcolumns I get the following error:
Unpivot [2514]: PivotKeyValue is not valid. In an UnPivot transform with more than one unpivoted DestinationColumn, the set of PivotKeyValues per destination must match exactly.
Is there a way to do it?
View 5 Replies
View Related
Jul 27, 2006
Hi
When using unpivot transformation, what exactly this error denote
"Incorrect UnPivot metadata. In an UnPivot transform, all input columns with a PivotKeyValue that is set, and are pointing to the same DestinationColumn, must have metadata that exactly matches "
data on which i was trying unpivoting is -
Name
Pd1
Pd2
Pd3
Pd4
Utsav
111
211
311
411
Verma
122
222
322
422
Nucleus
133
233
333
433
Noida
144
244
344
444
View 3 Replies
View Related
Aug 2, 2007
HI friends i need the pivot an unpivot example
package please send the link with atleast pictures steps
View 3 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
Mar 11, 2008
Hi,
I have a 2 outputs from 2 individual Unpivot transformation, now i want to compare the resultant of these two and find the odd / unmatched rows, could anyone please tell me how to achieve this?
View 3 Replies
View Related
Jun 29, 2007
Hi all,
I am running into a problem which seems to indicate SQL Server 2005 is pretty aggressive with its implicit conversion which makes UNPIVOT apparently unusable for my current task of creating attribute/value pairs of strings for each of the records unpivoted around the primary key.
WITH cte as
(
SELECT 1 as Code, '2' as Status, 'Some Guy' as CreatedBy, Convert(varchar(19), getdate(), 120) as CreatedDate
)
SELECT
[Code]
,[Attribute]
,[Value]
FROM
cte
UNPIVOT([Attribute] FOR [Value] IN ([Status],[CreatedBy],[CreatedDate])) as U;
Msg 8167, Level 16, State 1, Line 1
The type of column "CreatedBy" conflicts with the type of other columns specified in the UNPIVOT list.
The same result shows up if I use an inner SELECT instead of the CTE:
SELECT
[Code]
,[Attribute]
,[Value]
FROM
(
SELECT 1 as Code, '2' as Status, 'Some Guy' as CreatedBy, Convert(varchar(19), getdate(), 120) as CreatedDate
) cte
UNPIVOT([Attribute] FOR [Value] IN ([Status],[CreatedBy],[CreatedDate])) as U;
It seems to me that some arbitrary decision about what column is processed first is made and the process fails as soon as a column is met which is not of the same datatype. I also think it is interesting that the engine will implicitly cast from varchar to DateTime but not the other way around.
Any thoughts on how I can get around this?
Thanks in advance,
Calvin
View 1 Replies
View Related
Feb 15, 2014
I have below table and within same query i need pivot and unpivot.
create table #temp(name1 varchar(10),name2 varchar(10),name3 varchar(10),month date,emp1 int,emp2 int,emp3 int,emp4 int)
insert into #temp values ('a','b','c','1-1-2013',1,2,3,4)
insert into #temp values ('a','b','c','1-2-2013',11,20,30,40)
insert into #temp values ('a','c','c','1-1-2013',22,30,80,40)
insert into #temp values ('a','c','c','1-2-2013',28,34,39,30)
select * from #temp
Now i need output in below format
name1,name2,name3,Emp,jan-13,feb-13
a,b,c,emp1,1,11
a,b,c,emp2,2,20
a,b,c,emp3,3,30
a,b,c,emp4,4,40
a,c,c,emp1,22,28
a,c,c,emp2,30,34
a,c,c,emp3,80,39
a,c,c,emp4,40,30
View 4 Replies
View Related
Sep 11, 2015
Trying to optimise the below query, I believe it's do with the estimated rows on the unpivot using Supratimes this seems to be the only sticking point.The query below is an example replicating what I'm trying to do in live, it takes around 2 seconds to run on my pc.
Create --drop --alter
Table #Actuals
(
Period1 FLOAT
, Period2 FLOAT
, Period3 FLOAT
, Period4 FLOAT
[code]....
View 8 Replies
View Related
Feb 26, 2008
Hello everyone!
Maybe someone of you folks already stumbled upon this one and can help.
I'm using an Unpivot transform in a dataflow.
I want to unpivot various columns that are all of type dt_str(50).
Some of those (input) columns may contain NULL values in some rows, like this:
col1
col2
col3
col4
BusinessKey
val1
NULL
val3
val4
As I run this package, I'd expect the Unpivot's output looking like this:
BusinessKey
col1
val1
BusinessKey
col2
NULL
BusinessKey
col3
val3
BusinessKey
col4
val4
But Unpivot won't output a Key/Value pair if an input value is NULL.
So in fact the output looks like this (col2 is missing):
BusinessKey
col1
val1
BusinessKey
col3
val3
BusinessKey
col4
val4
The BOL documentation doesn't tell anything about Unpivot behaviour if input values are NULL (or at least I couldn't find it).
Now, is this a known problem?
If so, is there a fix or workaround so that I can tell Unpivot to output NULL values?
Thanks for your help!
View 5 Replies
View Related
Feb 18, 2008
Hi there,
I have an issue that feels like it requires an Unpivot task, but I'm struggling to visualise the best way to do it. I'd be grateful for any advice on this.
I have this dataset in my pipeline
moduleid
startdate
modenddate
Revenue
MonthsOnModule
800091
05/09/2007
30/12/2007
150
3
800094
05/09/2007
30/11/2007
148
2
800095
05/09/2007
30/12/2007
300
3
Basically, each module has a start date and an end date. Therefore, with a simple datediff, we can get to MonthsOnModule.
Each module attracts an amount of revenue, which need to be allocated equally to each month over which the module runs.
In the example above, Module 800091 lasts 3 months, generates £150 revenue, so in September 2007, it attracts £50, in October 2007 It attracts £50, In November 2007 it attracts £50.
I'm using this package to populate a fact table in an OLAP Data Warehouse. The destination table needs to hold the data like this:
moduleid
Month
value
800091
200709
50
800091
200710
50
800091
200711
50
800094
200709
74
800094
200710
74
800095
200709
100
800095
200710
100
800095
200711
100
Now that looks like a pretty straight unpivot, but in order to unpivot, you have to have columns to unpivot into rows. In this case, I would need one column for each month that the module lasted. Modules can last any number of months. Some last 40 months, and there is no reason why one couldn't last 100 months or more.
I just can't really seem to visualise my approach to this transform. Anyone got any ideas?
Thank you
View 3 Replies
View Related
Jun 12, 2007
hI,
i have this particular problem with the unpivot.The below is my flat file source.The dates can go upto 130 columns.this count can also vary.SM,SR,SB are again values repeating for diff instrument.They are the values of the instrument on the particular dates.This is a snap shot of one feed.Other feeds may have the dates differing.How do i read this file.
Problem 1:If i skip the first row and unpivot the 2nd row,then with the new feed,with new dates my SSIS package will bomb as it will not find the col names.
Problem 2:IF i uncheck the "Use first row as column headers" then the problem 1 is solved but the o/p will be
20080101
20061102
20061103
1.2
1.3
1.2.
1.5
.....and so on..
IS there any other way to fix this.These are feeds with the spread values of instruments on particular dates.Please help.
RUN 2.01E+11 132238 0 45
INSTRID DATATYPES 20081101 20061102 20061103
Z03369 SM 1.1 1.2 1.3
Z03369 SB 1.3 1.3 1.7
Z03369 SR 2 3 4
Z81910 SM 1.1 1.2 1.3
Z81910 SB 1.3 1.3 1.7
View 7 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
Feb 18, 2015
I have a set of data in which i have a product number going through 6 stages and each stage has a date. Since the each stages are in columns, I have created a unpivot query to transpose the columns into rows.
The actual result data needs to be in format of
ProductNumber Event_NameEvent_Date Event_Days
101 Stage 1 2/13/2014 1
101 Stage 2 2/13/2014 0
101 Stage 3 2/18/2014 5
101 Stage 4 2/23/2014 5
However the result data i am getting is like
ProductNumber Event_NameEvent_Date Event_Days
101 Stage 1 2/13/2014 1
101 Stage 1 2/13/2014 0
101 Stage 1 2/18/2014 5
101 Stage 1 2/23/2014 5
101 Stage 2 2/13/2014 1
101 Stage 2 2/13/2014 0
101 Stage 2 2/18/2014 5
101 Stage 2 2/23/2014 5
The unpivot query is working fine however I am getting duplicate values in the result. For each productnumber there must be only 6 results however i am getting 24 rows for each product number due to duplication.
I have attached the code and the source data for reference
Code:
SELECT distinct ProductNumber ,
Event_Name ,
Event_Date ,
Event_Days
FROM(
SELECT ProductNumber ,
[Code] .....
View 2 Replies
View Related
May 6, 2015
I've this result from my 'case' query;
Jan Feb Mar April
1 2 3 4
I want ;
Month Value
JAN 1
Feb 2
Mar 3
April 4
View 3 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
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
