Post reply

Simple Pivot I think

  • December 12, 2018 at 5:40 pm

    #376599

    I have a data set that looks like this.

    TypeCol1Col2Col3Col4Col5Col6
    Row1101010101010
    Row2101010101010
    Row3101010101010
    Row4101010101010

    I want it to look like this

    TypeRow1Row2Row3Row4
    Col110101010
    Col210101010
    Col310101010
    Col410101010
    Col510101010
    Col610101010

    There may be more or less rows and each row will have a different type  The columns will be fixed.  I don't need to do any calculations.
    I thought this would be straight forward but I guess not.

  • December 13, 2018 at 11:03 am

    #2016249

    It's not a simple pivot.  It's a combination of a pivot and an unpivot.  And, because the number of rows isn't constant, it will need to be dynamic.

    I have to ask WHY!!! 

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • December 13, 2018 at 11:10 am

    #2016250

    I have output of table 1 already.  The boss has asked for the data to be pivoted.  Mine is not to ask why.

  • December 13, 2018 at 11:56 am

    #2016253

    After it was said that I would need both pivot and unpivot I came up with the following that works for any number of Columns and Rows.

    Declare @Rows varchar(max)
     ,@Cols varchar(max)
     ,@DynamicPivotQuery nvarchar(max)

    Select @Rows = ISNULL(@Rows + ',','') + '[' + SourceColumnName + ']' From SourceTableName
    Select @Cols = ISNULL(@Cols + ',','') + '[' + [Name] + ']' From
    (
    select C.name
    from sys.columns c
    where c.object_id = OBJECT_ID('dbo.SourceTableName')) [Column]
    Select @Cols = Replace(@Cols, '[Type],', '')
    Set @DynamicPivotQuery = N' select * from SourceTableName
    unpivot (value for [Date] in (' + @Cols + ')) unp
    pivot (max(value) for Type in  (' + @Rows + ')) p'
    exec sp_executesql @DynamicPivotQuery

Viewing 4 posts - 1 through 3 (of 3 total)

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