rearrange a sql table

Question

I have the following table

1      a      10
1      b      11
2      a      4
2      b      8
3      a      5
3      b      4

I want to rearrange the table to the following one

1    10     11
2    4      8
3    5      4

The second column will have the name 'a' and the third the name 'b'. is this possible? I'm taking my first step in SQL. Thanks for your support.

You want to look into [`PIVOT and UNPIVOT`](https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot) — TZHX, Nov 27 '17 at 10:56

score 1 · Answer 1 · answered Nov 27 '17 at 10:58

1

I assume following: table name is MyTable, which contains columns: ID, Col2, Col3.

select ID,
       max(case when Col2 = 'a' then Col3 end) AS A,
       max(case when Col2 = 'b' then Col3 end) AS B
from MyTable
group by ID

answered Nov 27 '17 at 10:58

Michał Turczyn

32,028
14
47
69

I think it would be better to use **`SUM`** instead of **`MAX`**. – Ullas Nov 27 '17 at 11:04

score 0 · Answer 2 · answered Nov 27 '17 at 11:01

Why use PIVOT/UNPIVOT? Much easier to do it this way:

WITH CTE AS (
    SELECT *
    FROM (VALUES (1,'a',10),
                 (1,'b',11),
                 (2,'a',4),
                 (2,'b',8),
                 (3,'a',5),
                 (3,'b',4)) V (Col1, Col2, Col3))
SELECT Col1,
       MAX(CASE Col2 WHEN 'a' THEN Col3 END) AS Col4,
       MAX(CASE Col2 WHEN 'b' THEN Col3 END) AS Col5
FROM CTE
GROUP BY Col1;

score 0 · Answer 3 · answered Nov 27 '17 at 11:22

Try the simple method:

DECLARE @TABLE TABLE(ID INT, NAME VARCHAR(1), VALUE INT)
INSERT INTO @TABLE VALUES(1,'a',10)
INSERT INTO @TABLE VALUES(1,'b',11)
INSERT INTO @TABLE VALUES(2,'a',4)
INSERT INTO @TABLE VALUES(2,'b',8)
INSERT INTO @TABLE VALUES(3,'a',5)
INSERT INTO @TABLE VALUES(3,'b',4)

SELECT ID
    ,MAX(CASE WHEN NAME='a' THEN VALUE END) Value1
    ,MAX(CASE WHEN NAME='b' THEN VALUE END) Value2
FROM @TABLE
GROUP BY ID

OutPut:

ID  Value1  Value2
1     10      11
2     4       8
3     5       4

rearrange a sql table

3 Answers3