SQL: Unpivot Query

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


ADVERTISEMENT

Can You Unpivot On Multiple Keys With The Unpivot Transform

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

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

View 4 Replies View Related

T-SQL (SS2K8) :: Case Query - Unpivot Function

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

UNPIVOT Help

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

UNPIVOT

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

Can Unpivot Help Me Here?

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

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?

View 11 Replies View Related

Need Help UNPIVOT Table

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

Unpivot Transformation

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

How To Set The Dynamic Column Name In Unpivot SQL

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

Unpivot With Multiple DestinationColumns

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

Incorrect UnPivot Metadata

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

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


View 3 Replies View Related

Exotic Unpivot Transformation

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

How To Set The Dynamic Column Name In Unpivot SQL

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

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 View Related

Unpivot Output Comarison

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

Implicit Casting And UNPIVOT

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

Optimization Of Unpivot / Insert Code

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

NULL Values In UNPIVOT Output Possible?

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

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

View 3 Replies View Related

Unpivot Or Custom Script Task?

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

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.

View 5 Replies View Related

WANT TO SKIP ROWS AAND ALSO UNPIVOT

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

T-SQL (SS2K8) :: Comparing Columns In Unpivot Table

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

SQL Server 2012 :: Duplicate Values In UNPIVOT

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

Transact SQL :: Grouping Similar Rows After Unpivot?

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

Unpivot Multiple Columns With Datetime NULLs

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

SQL Server 2012 :: Using Cross Apply To UNPIVOT Data

Jan 15, 2014

I 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!)

View 7 Replies View Related

T-SQL (SS2K8) :: Join Or Pivot / Unpivot For Mismatch Dates

Jul 31, 2014

I 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]....

View 3 Replies View Related

T-SQL (SS2K8) :: Unpivot Syntax And Multi-part Identifiers

Jan 29, 2015

This 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] ....

View 3 Replies View Related

SQL Server 2012 :: Transposing Data Using Pivot And Unpivot

Jun 5, 2015

My 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...

View 1 Replies View Related







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