SQL transpose full table

by

In order to transpose the data into the result that you want, you will need to use both the UNPIVOT and the PIVOT functions.

The UNPIVOT function takes the A and B columns and converts the results into rows. Then you will use the PIVOT function to transform the day values into columns:

select *
from
(
  select day, col, value
  from yourtable
  unpivot
  (
    value
    for col in (A, B)
  ) unpiv
) src
pivot
(
  max(value)
  for day in (Mon, Tue, Wed, Thu, Fri)
) piv

See SQL Fiddle with Demo.

If you are using SQL Server 2008+, then you can use CROSS APPLY with VALUES to unpivot the data. You code would be changed to the following:

select *
from
(
  select day, col, value
  from yourtable
  cross apply
  (
    values ('A', A),('B', B)
  ) c (col, value)
) src
pivot
(
  max(value)
  for day in (Mon, Tue, Wed, Thu, Fri)
) piv

See SQL Fiddle with Demo.

Edit #1, applying your current query into the above solution you will use something similar to this:

select *
from
(
  select LEFT(datename(dw,datetime),3) as DateWeek,
    col, 
    value
  from DataTable 
  cross apply 
  (
    values ('A', ACalls), ('B', BCalls)
  ) c (col, value)
) src
pivot
(
  sum(value)
  for dateweek in (Mon, Tue, Wed, Thu, Fri)
) piv

More Related Contents:

  1. SQL query rows as columns [duplicate]
  2. SQL Server dynamic PIVOT query?
  3. Efficiently convert rows to columns in sql server
  4. Convert Rows to columns using ‘Pivot’ in SQL Server
  5. Simple way to transpose columns and rows in SQL?
  6. SQL Server : Columns to Rows
  7. Unpivot with column name
  8. T-SQL dynamic pivot
  9. SQL Server 2005 Pivot on Unknown Number of Columns
  10. How to simulate UNPIVOT in Access?
  11. TSQL Pivot without aggregate function
  12. Dynamically create columns sql
  13. TSQL PIVOT MULTIPLE COLUMNS
  14. Dynamic Pivot Columns in SQL Server
  15. SQL Server : dynamic pivot over 5 columns
  16. Pivot Table and Concatenate Columns
  17. Dynamic Pivot Table in SQL Server
  18. How to replace (null) values with 0 output in PIVOT
  19. Pivot data in T-SQL
  20. PIVOT query on Distinct records
  21. SQL Server PIVOT perhaps?
  22. In SQL Server how to Pivot for multiple columns
  23. Using PIVOT in SQL Server 2008
  24. SQL Unpivot multiple columns Data
  25. How to cast the DateTime to Time
  26. SQL: parse the first, middle and last name from a fullname field
  27. separate comma separated values and store in table in sql server
  28. How to unpivot in BigQuery?
  29. Sql Server : How to use an aggregate function like MAX in a WHERE clause
  30. How to get the next number in a sequence

Leave a Comment