Search code examples
sqlsql-serverpivotunpivot

How to transpose table SQL

I`m trying to transpose table, but I get message that type of column conflicts with other. This is solution

WITH CTE_Unpivoted as
( select ID, Fields, QuantityValue
from Bikes2 as s
UNPIVOT
(QuantityValue for Fields IN(ProductName, Country, Quantity)
)AS u
)
SELECT Fields, [1],[2],[3],[4],[5],[6],[7]
from CTE_Unpivoted AS u
PIVOT
(SUM(QuantityValue) for ID IN ([1],[2],[3],[4],[5],[6],[7])) AS p

This is data

CREATE TABLE Bikes2
 (ID INT PRIMARY KEY IDENTITY,
 ProductName VARCHAR(50),
 Country VARCHAR(50),
 Quantity INT);
INSERT INTO Bikes2 VALUES ('Road Bike', 'USA', 128)
INSERT INTO Bikes2 VALUES ('Road Bike', 'Italy', 64)
INSERT INTO Bikes2 VALUES ('Electric Bike', 'USA', 257)
INSERT INTO Bikes2 VALUES ('Electric Bike', 'Italy', 143)
INSERT INTO Bikes2 VALUES ('Children Bicecle', 'USA', 386)
INSERT INTO Bikes2 VALUES ('Children Bicecle', 'Italy', 52)
INSERT INTO Bikes2 VALUES ('Road Bike', 'USA', 35)
SELECT * FROM Bikes2

How to do this right? Is it possible make it dynamic? And result smt like this

table

Solution

  • This type of reformatting is better done in the application layer rather than in the database. Here are two reasons why:

    • A SQL query returns a fixed number of columns, and you seem to want one column per row in the table. So you have to hardwire that number.
    • Each column has a single type, but your data has strings and numbers.

    That said, it is possible. Here is one method:

    with b as (
      select b.*, row_number() over (order by id) as seqnum
      from bikes2 b
     )
select 'productname',
       max(case when seqnum = 1 then productname end),
       max(case when seqnum = 2 then productname end),
       max(case when seqnum = 3 then productname end),
       max(case when seqnum = 4 then productname end),
       max(case when seqnum = 5 then productname end),
       max(case when seqnum = 6 then productname end),
       max(case when seqnum = 7 then productname end)
from b
union all
select 'country',
       max(case when seqnum = 1 then country end),
       max(case when seqnum = 2 then country end),
       max(case when seqnum = 3 then country end),
       max(case when seqnum = 4 then country end),
       max(case when seqnum = 5 then country end),
       max(case when seqnum = 6 then country end),
       max(case when seqnum = 7 then country end)
from b
union all
select 'quantity',
       max(case when seqnum = 1 then cast(quantity as varchar(255)) end),
       max(case when seqnum = 2 then cast(quantity as varchar(255)) end),
       max(case when seqnum = 3 then cast(quantity as varchar(255)) end),
       max(case when seqnum = 4 then cast(quantity as varchar(255)) end),
       max(case when seqnum = 5 then cast(quantity as varchar(255)) end),
       max(case when seqnum = 6 then cast(quantity as varchar(255)) end),
       max(case when seqnum = 7 then cast(quantity as varchar(255)) end)
from b;

    This can be shorted to:

    with b as (
      select b.*, row_number() over (order by id) as seqnum
      from bikes2 b
     )
select name,
       max(case when seqnum = 1 then val end),
       max(case when seqnum = 2 then val end),
       max(case when seqnum = 3 then val end),
       max(case when seqnum = 4 then val end),
       max(case when seqnum = 5 then val end),
       max(case when seqnum = 6 then val end),
       max(case when seqnum = 7 then val end)
from b cross apply
     (values (1, 'productname', productname),
             (2, 'country', country),
             (3, 'quantity', convert(varchar(255), quantity))
     ) v(ord, name, val)
group by name, ord
order by ord;

    Here is a db<>fiddle.