Unpivot Output Comarison
Mar 11, 2008Hi,
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?
ADVERTISEMENT
NULL Values In UNPIVOT Output Possible?
Feb 26, 2008Hello 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!
Can You Unpivot On Multiple Keys With The Unpivot Transform
Oct 17, 2006Hi 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
UNPIVOT Help
Apr 24, 2008Hi, 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
...
UNPIVOT
Sep 7, 2007Hi 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.
Can Unpivot Help Me Here?
Aug 29, 2006HI, 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
SQL: Unpivot Query
Dec 25, 2007Hi,
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!
Unpivot Table
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?
Need Help UNPIVOT Table
Apr 24, 2008hi 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
Unpivot Transformation
Dec 10, 2007Hi,
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
How To Set The Dynamic Column Name In Unpivot SQL
Apr 10, 2008Dear 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
Unpivot With Multiple DestinationColumns
Jul 20, 2007Hi,
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?
Incorrect UnPivot Metadata
Jul 27, 2006Hi
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
Pivot And Unpivot Example In SSIS
Aug 2, 2007
HI friends i need the pivot an unpivot example
package please send the link with atleast pictures steps
Exotic Unpivot Transformation
Jul 18, 2006I 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
How To Set The Dynamic Column Name In Unpivot SQL
Apr 10, 2008Hi 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
Unpivot Transformation And Expressions
Jan 12, 2007I 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
Implicit Casting And UNPIVOT
Jun 29, 2007Hi 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
T-SQL (SS2K8) :: Pivot And Unpivot In Same Query?
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
Optimization Of Unpivot / Insert Code
Sep 11, 2015Trying 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]....
Unpivot Transformation Null Values
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
Unpivot Or Custom Script Task?
Feb 18, 2008Hi 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
Unpivot And Pivot Transformation Issue
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.
WANT TO SKIP ROWS AAND ALSO UNPIVOT
Jun 12, 2007hI,
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
T-SQL (SS2K8) :: Comparing Columns In Unpivot Table
Nov 21, 2014SET 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
SQL Server 2012 :: Duplicate Values In UNPIVOT
Feb 18, 2015I 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] .....
T-SQL (SS2K8) :: Case Query - Unpivot Function
May 6, 2015I'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
Transact SQL :: Grouping Similar Rows After Unpivot?
Sep 23, 2015I'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
Unpivot Multiple Columns With Datetime NULLs
Apr 3, 2007Hi 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?
SQL Server 2012 :: Using Cross Apply To UNPIVOT Data
Jan 15, 2014I was reading Kenneth Fisher's and Dwain Camps' articles on unpivoting using cross apply... And I can actually get them to work....
CREATE TABLE #TxCycle(
Cycle INT NOT NULL,
PatientID INT NOT NULL,
ALOPECIA TINYINT,
Causality1 TINYINT,
Relatedness1 TINYINT,
[Code] ....
The one thing I was wondering was this: how do I extract the symptom names from the field list without knowing them all beforehand? Dwain does this
-- DDL and sample data for UNPIVOT Example 2
CREATE TABLE #Suppliers
(ID INT, Product VARCHAR(500)
,Supplier1 VARCHAR(500), Supplier2 VARCHAR(500), Supplier3 VARCHAR(500)
,City1 VARCHAR(500), City2 VARCHAR(500), City3 VARCHAR(500))
Can this be adapted if you don't know all the column names beforehand? (Likely not). Back in the dark ages, when I was working on a database like this, it was in Access, and I could loop over the fields collection and evaluate each field name. (Yes, I know you're not supposed to store information in field names, but I inherited that mess!)
T-SQL (SS2K8) :: Join Or Pivot / Unpivot For Mismatch Dates
Jul 31, 2014I have a table which uses multiple joins to create another table but it turns out that the effective_date which is used in the join to match row together does not work all the time since some of the dates for the effective date column are out of sync meaning records that show data as missing even when the other table contains the data. I try the SQL script below but it returning 6 rows instead of 3–
select t2.[entity_id]
,t2.[effective_date]
,[company_name]
,[last_accounts_date]
,[s_code]
,[s_code_description]
,[ineffective_date]
[code]....
T-SQL (SS2K8) :: Unpivot Syntax And Multi-part Identifiers
Jan 29, 2015This query is the first time I am using the Unpivot syntax and I am coming across a problem. When I try to unpivot my data, I get the following statement:
"Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "Table3.DocketId" could not be bound."
What is the cause of this issue?
Select
Table3.DocketId,
UP.AssignmentType,
Up.AssignedStaff
From
(
Select distinct
Table2.DocketId,
[Code] ....
SQL Server 2012 :: Transposing Data Using Pivot And Unpivot
Jun 5, 2015My table structure is like
col1 col2 col3 col4 col5 col6
abc. def. 3fg. 59j. 567. 596040
abc. def. 3fg. 59j. 567. 596042
abc. def. 3fg. 59j. 567. 596043
abc. def. 3fg. 59j. 567. 596044
edf. ijk. rkl. 1fh. 567. 596045
edf. ijk. rkl. 1fh. 567. 596046
edf. ijk. rkl. 1fh. 567. 596047
edf. ijk. rkl. 1fh. 567. 596048
edf. ijk. rkl. 1fh. 567. 596049
And I am trying to get the above data , gel them ino col 6 by comma separated
col1 col2 col3 col4 col5 col6
abc def 3fg 59j 567 596040,567 596042,567 596043,567 596044
edf ijk rkl 1fh 567 596045,596046,596047,596048,596049
Can I get an example query for this...