Transpose rows and columns without aggregate

I have the following dataset

Date            Field1        Col1  Col2    Col3
2012/07/02      Customer1      CL   DS      RT
2012/07/03      Customer1      DS   RT      700
2012/07/04      Customer1      DS   RT      700
2012/07/02      Customer2      CL   DS      RT
2012/07/03      Customer2      DS   RT      1500
2012/07/04      Customer2      DS   RT      1500
2012/07/02      Customer3      CL   DS      RT
2012/07/03      Customer3      DS   RT      6000
2012/07/04      Customer3      DS   RT      6000
2012/07/02      Customer4      CL   RC      RT
2012/07/03      Customer4      RC   RT      4900
2012/07/04      Customer4      RC   RT      4900

I’d like output as follows:

  • Select average duration
  • write to windows event viewer from sql server job
  • How to rename a table in the sql server compact edition
  • How to make awkward pivot of sql table in SQL Server 2005?
  • cumulative sum not working
  • inserting records from specific columns in one table to another table depending on multiple criteria
  • Field1  2012/07/02  2012/07/03  2012/07/04
    Col1    Customer1   CL  DS  DS
    Col2    Customer1   DS  RT  RT
    Col3    Customer1   RT  700 700
    Col1    Customer2   CL  DS  DS
    Col2    Customer2   DS  RT  RT
    Col3    Customer2   RT  1500    1500
    Col1    Customer3   CL  DS  DS
    Col2    Customer3   DS  RT  RT
    Col3    Customer3   RT  6000    6000
    Col1    Customer4   CL  RC  RC
    Col2    Customer4   RC  RT  RT
    Col3    Customer4   RT  4900    4900
    

    The problem is also that I have an unfixed amount of Customers (Field1) & unfixed amount of Dates.

  • SQL String aggregation returns different results based on location of variables
  • SQL Server group by set of results
  • Using Isnumeric with wild card in SQL Server
  • Select values from multiple columns into single column
  • Sequential Invoice Numbers SQL server
  • Find duplicate rows in SQL Server by multiple conditions
  • 3 Solutions collect form web for “Transpose rows and columns without aggregate”

    What you are trying to do is known as a PIVOT. There are two ways that these can be done, either with a static pivot where you hard-code all of the values to transform or a dynamic pivot where the values were determined at execution.

    Static Version (See SQL Fiddle with Demo):

    select *
    from 
    (
      select dt, field1, col, value
      from yourTable
      unpivot
      (
        value for col in (col1, col2, col3)
      ) u
    )x1
    pivot
    (
      min(value)
      for dt in ([2012-07-02], [2012-07-03], [2012-07-04])
    ) p
    

    Dynamic Pivot (See SQL Fiddle with Demo):

    DECLARE @colsUnpivot AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX),
        @colsPivot as  NVARCHAR(MAX)
    
    select @colsUnpivot = stuff((select ','+quotename(C.name)
             from sys.columns as C
             where C.object_id = object_id('yourTable') and
                   C.name like 'col%'
             for xml path('')), 1, 1, '')
    
    select @colsPivot = STUFF((SELECT distinct ', ' + QUOTENAME(convert(char(10), dt, 120))
                        from yourTable
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'')
    
    set @query = 
              'SELECT col, field1, ' + +@colsPivot+ '
               FROM
               (
                  select dt, field1, col, value
                  from yourTable
                  unpivot
                  (
                    value for col in ('+ @colsUnpivot+')
                  ) u
               )x1
               pivot
               (
                 min(value)
                 for dt in ('+@colsPivot+')
               ) p '
    
    exec(@query)
    

    The dynamic pivot is a great option when you do not know the number of items that you need to convert into columns. Both will yield the same results.

    Edit #1, if you want the date columns in a specific order – desc, etc, then you will use the following to get the dates (See SQL Fiddle with Demo):

    select @colsPivot = STUFF((SELECT ', ' + QUOTENAME(convert(char(10), dt, 120))
                        from yourTable
                        group by dt
                        ORDER by dt desc
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'')
    

    You don’t need to do it without aggregates, but you do need to use Dynamic SQL to do it. Search for “SQL Dynamic Pivot” and you’ll find plenty of posts about it.
    Like: SQL Server dynamic PIVOT query?

    But better would be to do this in the client, as SQL’s clients prefer to know what columns are being produced ahead of time.

    You must use SQL Server Pivot query for your question.

    You Can Use Below Query :

    CREATE TABLE mytable(date DATE, Field1 NVARCHAR(50), col1 NVARCHAR(30), col2 NVARCHAR(30),col3 NVARCHAR(30))
    INSERT INTO mytable(date,Field1,col1,col2,col3)
    VALUES('2012/07/02','Customer1','CL','DS','RT')
    ...
    
    SELECT Col, Field1, [2012/07/02],[2012/07/03],[2012/07/04]
    FROM (SELECT Field1, 'Col1' AS Col, Date, Col1 AS ColVal
          FROM MyTable
    
          UNION ALL
    
          SELECT Field1, 'Col2' , Date, Col2 AS ColVal
          FROM MyTable
    
          UNION ALL 
    
          SELECT Field1, 'Col3' , Date, Col3 AS Col
          FROM MyTable
          )AS P
    PIVOT (MIN(Colval) FOR Date IN ([2012/07/02],[2012/07/03],[2012/07/04])) AS Pvt
    ORDER BY Field1, Col
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.