February 18, 2015 at 1:47 am
Hi,
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.
Can anyone please help me in getting rid of the duplicates.
I have attached the code and the source data for reference
Code:
SELECT distinct ProductNumber ,
Event_Name ,
Event_Date ,
Event_Days
FROM(
SELECT ProductNumber ,
[Stage 5] ,
[Stage 1] ,
[Stage 3] ,
[Stage 2] ,
[Stage 4] ,
[Stage 6],
DATEDIFF(DAY, [Stage 1], [Stage 2]) AS Stage1_Stage2_Days,
DATEDIFF(DAY, [Stage 2], [Stage 3]) AS Stage2_Stage3_Days,
DATEDIFF(DAY, [Stage 3], [Stage 4]) AS Stage3_Stage4_Days,
DATEDIFF(DAY, [Stage 4], [Stage 5]) AS Stage4_Stage5_Days,
DATEDIFF(DAY, [Stage 5], [Stage 6]) AS Stage5_Stage6_Days
FROM dbo.demot
)AS UP
UNPIVOT
(
Event_Date FOR Event_Name
IN
( [Stage 5] ,
[Stage 1] ,
[Stage 3] ,
[Stage 2] ,
[Stage 4] ,
[Stage 6]
)
) AS UPV
UNPIVOT
(
Event_Days FOR Event_Namex
IN
(
Stage1_Stage2_Days,
Stage2_Stage3_Days,
Stage3_Stage4_Days,
Stage4_Stage5_Days,
Stage5_Stage6_Days
)
) AS UPV1
---WHERE upv.event_days = upv1.event_days
---ORDER BY ProductNumber, Event_Date, Event_Name
February 18, 2015 at 3:33 am
Could you provide the Basic DDL for the source table along with the INSERT Script with the sample data, as like many others I'm adverse to opening Excel workbooks from the internet.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
February 18, 2015 at 8:25 am
Maybe you'd like to check a different method to unpivot.
http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply