Sql script Transpose Multiple Column into Rows

  • Hi

     

    i've the current situation


    DtCont        | DtCont       | DtCont |

    ---------------------------------------------------------

    "20180229"|"20180330"|"20180428"


    and i need the follow result

    |DtCont        |

    ------------------------------------------

    |"20180229"|

    |"20180330"|

    |"20180428"|

     

    With Unpivot is not possible,only solutuon i find is create a sequente of select with union

    select a.PM_DataContabileBKI as r0

    from (

    SELECT top(1) PM_DataContabileBKI

    FROM [xxx].[xxx].[xxx]

    order by 1 desc

    )as a

    union

    select DATEADD( MONTH,-1,a.PM_DataContabileBKI) r0

    from (

    SELECT top(1) PM_DataContabileBKI

    FROM [xxx].[xxx].[xxx]

    order by 1 desc

    )as a

     

    But is not performing with 36 union.......

    Any Idea?

    Thanks

     

     

  • This looks like you are simply repeating the same query over and over

    SELECT top(1) PM_DataContabileBKI
    FROM [xxx].[xxx].[xxx]
    order by 1 desc

    and subtracting a number of months from the single value returned.

    Why not get the value once, and cross apply a list of months to offset

  • If i wrote

    SELECT PM_DataContabileBKI,DATEADD( MONTH,-7,PM_DataContabileBKI) r7

    FROM [xxx].[xxx].[xxx]

    order by 1 desc

     

    the result is a list of column but i nedd a list of row 🙂

  • That SQL does not make sense.

    Kindly provide DDL scripts and sample data so that we can understand what you are seeing.

  • a simple basic example of cross applying a datamath function to get your 36 values based on one date.your example did not go by date, it seemed, though; are you trying to get the last two days of the month?

    SELECT name,ExpandedMonth
    FROM sys.tables t
    CROSS APPLY
    (
    SELECT TOP 36
    DATEADD(mm, -ROW_NUMBER() OVER (ORDER BY sc1.id) ,t.[create_date]) AS ExpandedMonth
    FROM Master.dbo.SysColumns sc1
    ) X
    ORDER BY name,ExpandedMonth

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • UnPivot works for this

    Create Table #Test (Id Int, DtCont1 char(8), DtCont2 char(8), DtCont3 char(8))

    Insert #Test (Id, DtCont1, DtCont2, DtCont3) Values (1, '20180229', '20180330', '20180428')


    SELECT Id, DtCont
    FROM #Test
    UNPIVOT
    (
    DtCont
    FOR Course in (DtCont1, DtCont2, DtCont3)
    ) AS TestUnpivot

    Output:

    Id DtCont

    1 20180229

    1 20180330

    1 20180428

     

    With more data:

    Insert #Test (Id, DtCont1, DtCont2, DtCont3) Values (1, '20180229', '20180330', '20180428')
    Insert #Test (Id, DtCont1, DtCont2, DtCont3) Values (2, '20180220', '20180331', '20180429')
    Insert #Test (Id, DtCont1, DtCont2, DtCont3) Values (3, '20180222', '20180332', '20180420')

    Id DtCont

    1 20180229

    1 20180330

    1 20180428

    2 20180220

    2 20180331

    2 20180429

    3 20180222

    3 20180332

    3 20180420

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply