SQL Transpose Full Table

SQL transpose full table

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

Simple way to transpose columns and rows in SQL?

There are several ways that you can transform this data. In your original post, you stated that PIVOT seems too complex for this scenario, but it can be applied very easily using both the UNPIVOT and PIVOT functions in SQL Server.

However, if you do not have access to those functions this can be replicated using UNION ALL to UNPIVOT and then an aggregate function with a CASE statement to PIVOT:

Create Table:

CREATE TABLE yourTable([color] varchar(5), [Paul] int, [John] int, [Tim] int, [Eric] int);

INSERT INTO yourTable
    ([color], [Paul], [John], [Tim], [Eric])
VALUES
    ('Red', 1, 5, 1, 3),
    ('Green', 8, 4, 3, 5),
    ('Blue', 2, 2, 9, 1);

Union All, Aggregate and CASE Version:

select name,
  sum(case when color = 'Red' then value else 0 end) Red,
  sum(case when color = 'Green' then value else 0 end) Green,
  sum(case when color = 'Blue' then value else 0 end) Blue
from
(
  select color, Paul value, 'Paul' name
  from yourTable
  union all
  select color, John value, 'John' name
  from yourTable
  union all
  select color, Tim value, 'Tim' name
  from yourTable
  union all
  select color, Eric value, 'Eric' name
  from yourTable
) src
group by name

See SQL Fiddle with Demo

The UNION ALL performs the UNPIVOT of the data by transforming the columns Paul, John, Tim, Eric into separate rows. Then you apply the aggregate function sum() with the case statement to get the new columns for each color.

Unpivot and Pivot Static Version:

Both the UNPIVOT and PIVOT functions in SQL server make this transformation much easier. If you know all of the values that you want to transform, you can hard-code them into a static version to get the result:

select name, [Red], [Green], [Blue]
from
(
  select color, name, value
  from yourtable
  unpivot
  (
    value for name in (Paul, John, Tim, Eric)
  ) unpiv
) src
pivot
(
  sum(value)
  for color in ([Red], [Green], [Blue])
) piv

See SQL Fiddle with Demo

The inner query with the UNPIVOT performs the same function as the UNION ALL. It takes the list of columns and turns it into rows, the PIVOT then performs the final transformation into columns.

Dynamic Pivot Version:

If you have an unknown number of columns (Paul, John, Tim, Eric in your example) and then an unknown number of colors to transform you can use dynamic sql to generate the list to UNPIVOT and then PIVOT:

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 <> 'color'
         for xml path('')), 1, 1, '')

select @colsPivot = STUFF((SELECT  ',' 
                      + quotename(color)
                    from yourtable t
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query 
  = 'select name, '+@colsPivot+'
      from
      (
        select color, name, value
        from yourtable
        unpivot
        (
          value for name in ('+@colsUnpivot+')
        ) unpiv
      ) src
      pivot
      (
        sum(value)
        for color in ('+@colsPivot+')
      ) piv'

exec(@query)

See SQL Fiddle with Demo

The dynamic version queries both yourtable and then the sys.columns table to generate the list of items to UNPIVOT and PIVOT. This is then added to a query string to be executed. The plus of the dynamic version is if you have a changing list of colors and/or names this will generate the list at run-time.

All three queries will produce the same result:

| NAME | RED | GREEN | BLUE |
-----------------------------
| Eric |   3 |     5 |    1 |
| John |   5 |     4 |    2 |
| Paul |   1 |     8 |    2 |
|  Tim |   1 |     3 |    9 |

Azure SQL Transpose a table with all rows

I tried my luck. Could you check below query if it works,

What I did different to your query is making the result of UNPIVOT distinct by adding row_number to it so that the later PIVOT will take max of each row and display separately. My bad if the explanation doesn't makes sense to you.

select [1],[2],[3],[4],[5],[6] 
  from 
  ( select link_id,head_values,
           row_number() over (partition by link_id order by link_id) rn
      from
         ( select link_id
                 ,cast(comp1 as varchar(255)) as comp1
                 ,cast(comp2 as varchar(255)) as comp2
                 ,cast(comp3 as varchar(255)) as comp3
                 ,cast(comp4 as varchar(255)) as comp4
             from [dbo].[test_excel_poc_head]
         ) as cp
    unpivot
    (
      head_values for head_value in (comp1,comp2,comp3,comp4)
    ) as up
   ) temp_results
pivot
(
  max(head_values)
  for link_id in ([1],[2],[3],[4],[5],[6])
) as pivottable;

db<>fiddle for your reference.

SQL How to transpose table from row to column

I am not using exact names of columns but you can try this,

SELECT GOOD2,
       NAME,
       MAX(CASE WHEN [TYPE] = 'BF' THEN VALUE END) BQTY,
       MAX(CASE WHEN [TYPE] = 'RC' THEN VALUE END) BQTY,
       MAX(CASE WHEN [TYPE] = 'ISU' THEN VALUE END) BQTY,
       MAX(CASE WHEN [TYPE] = 'CF' THEN VALUE END) BQTY
FROM   TABLE1
GROUP BY
       GOOD2,
       NAME

Transposing SQL Table Columns to Rows with Count on Each Category

One option is to UNPIVOT your data and then PIVOT the results

Example

Select *
 From  (
        Select B.* 
         From  YourTable A
         Cross Apply ( values (PIDA,'PIDA',1)
                             ,(NIDA,'NIDA',1)
                             ,(SIDA,'SIDA',1)
                             ,(IIPA,'IIPA',1)
                     ) B(Categories,Item,Value)
       ) src
 Pivot ( sum(Value) for Item in ([PIDA],[NIDA],[SIDA],[IIPA] ) ) pvt

Results (with small sample size)

Categories      PIDA    NIDA    SIDA    IIPA
NULL            1       1       2       3
Supported       2       3       NULL    1
Uncatalogued    1       NULL    2       NULL

SQL Transpose table - sqlserver

Write Dynamic Pivot Query as:

DECLARE @cols AS NVARCHAR(MAX)
DECLARE @query AS NVARCHAR(MAX)
DECLARE @colsFinal AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' +
                        QUOTENAME(pname)
                      FROM mytab
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)') 
                        , 1, 1, '')


select @colsFinal =  STUFF((SELECT distinct ',' +
                        'ISNULL('+QUOTENAME(pname)+',0) AS '+ QUOTENAME(pname)
                      FROM mytab
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)') 
                        , 1, 1, '')
--Edited query to replace null with 0 in Final result set.
SELECT @query = 'SELECT mID, '+@colsFinal +'
FROM mytab
PIVOT
(
  MAX(pvalue)
  FOR pname IN(' + @cols + ')) AS p;'


exec sp_executesql @query

Check demo here..

---

Related Topics