Simple Pivot I think

• Gary Wheeler

  Valued Member

  Points: 51

  More actions

  December 12, 2018 at 5:40 pm

  #376599

  I have a data set that looks like this.

  Type	Col1	Col2	Col3	Col4	Col5	Col6
  Row1	10	10	10	10	10	10
  Row2	10	10	10	10	10	10
  Row3	10	10	10	10	10	10
  Row4	10	10	10	10	10	10

  I want it to look like this

  Type	Row1	Row2	Row3	Row4
  Col1	10	10	10	10
  Col2	10	10	10	10
  Col3	10	10	10	10
  Col4	10	10	10	10
  Col5	10	10	10	10
  Col6	10	10	10	10

  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.

• drew.allen

  SSC Guru

  Points: 77013

  More actions

  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

• Gary Wheeler

  Valued Member

  Points: 51

  More actions

  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.

• Gary Wheeler

  Valued Member

  Points: 51

  More actions

  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