CODEWITHSUNDEEP
sql-server-2008t-sqlpivotunpivot
Mathieu
Mathieu

Reputation: 661

TSQL UNPIVOT for months + year column to date

I have this table structure in SQL Server 2008:

Columns: PersonID, DOSE1, DOSE2, DOSE3, ..... DOSE12, YEAR

example row: 123, 0.1, 0.0, 0.5, ..... 0.7, 2008

So basically I have a column for each month, and then a column year.

And the rows contain dose values for each of these months of that year.

My desired output is:

Columns: PersonId, BeginDate, EndDate, Dose

BeginDate and EndDate would be derived from the DOSEx columns, and the year. So say the year is 2008, the DOSE1 column would give me a BeginDate of 01/01/2008 end the EndDate should be 31/01/2008 23:59

For DOSE4 it's the month of April, so BeginDate should be 01/04/2008 and EndDate 30/04/2008 23:59

Any way to achieve this using TSQL ? I have a suspicion I should be using UNPIVOT, but not really sure how to get there.

Any help is much appreciated.

Regards,

TJ

Upvotes: 0

Views: 2791

Answers (1)

Lamak
Lamak

Reputation: 70638

This should work:

;WITH CTE AS
(
    SELECT  PersonId, 
            CONVERT(DATETIME,CAST([YEAR] AS VARCHAR(4))+RIGHT('0'+SUBSTRING(Months,5,2),2)+'01') BeginDate,
            Dose
    FROM YourTable A
    UNPIVOT(Dose FOR Months IN (DOSE1,DOSE2,DOSE3,DOSE4,DOSE5,DOSE6,DOSE7,DOSE8,DOSE9,DOSE10,DOSE11,DOSE12)) UP
)

SELECT PersonId, BeginDate, DATEADD(MINUTE,-1,DATEADD(MONTH,1,BeginDate)) EndDate, Dose
FROM CTE

Upvotes: 2

Related Questions