Best method used in Transposing Multiple columns to rows
Given:
A B C D E F G
FYQ1 11 10 8 6 1 0 0
FYQ2 11 10 8 7 2 0 0
FYQ3 11 11 11 10 5 4 0
FYQ4 11 11 11 10 5 4 0
Required:
FYQ1 FYQ2 FYQ3 FYQ4
A 11 11 11 11
B 10 10 11 11
C 8 8 11 11
D 6 7 10 10
E 1 2 5 5
F 0 0 4 4
G 0 0 0 0
I saw you edit your question.
You can try to use condition aggregate function with UNION ALL
CREATE TABLE T(
Name varchar(50),
A int,
B int,
C int,
D int,
E int,
F int,
G int
);
INSERT INTO T VALUES ('FYQ1',11,10,8 ,6,1,0,0);
INSERT INTO T VALUES ('FYQ2',11,10,8 ,7,2,0,0);
INSERT INTO T VALUES ('FYQ3',11,11,11,10,5,4,0);
INSERT INTO T VALUES ('FYQ4',11,11,11,10,5,4,0);
Query 1:
SELECT 'A' as " ",
MAX(CASE WHEN NAME = 'FYQ1' THEN A END) FYQ1,
MAX(CASE WHEN NAME = 'FYQ2' THEN A END) FYQ2,
MAX(CASE WHEN NAME = 'FYQ3' THEN A END) FYQ3,
MAX(CASE WHEN NAME = 'FYQ4' THEN A END) FYQ4
FROM T
UNION ALL
SELECT 'C',
MAX(CASE WHEN NAME = 'FYQ1' THEN C END),
MAX(CASE WHEN NAME = 'FYQ2' THEN C END),
MAX(CASE WHEN NAME = 'FYQ3' THEN C END),
MAX(CASE WHEN NAME = 'FYQ4' THEN C END)
FROM T
UNION ALL
SELECT 'D',
MAX(CASE WHEN NAME = 'FYQ1' THEN D END),
MAX(CASE WHEN NAME = 'FYQ2' THEN D END),
MAX(CASE WHEN NAME = 'FYQ3' THEN D END),
MAX(CASE WHEN NAME = 'FYQ4' THEN D END)
FROM T
UNION ALL
SELECT 'E',
MAX(CASE WHEN NAME = 'FYQ1' THEN E END),
MAX(CASE WHEN NAME = 'FYQ2' THEN E END),
MAX(CASE WHEN NAME = 'FYQ3' THEN E END),
MAX(CASE WHEN NAME = 'FYQ4' THEN E END)
FROM T
UNION ALL
SELECT 'F',
MAX(CASE WHEN NAME = 'FYQ1' THEN F END),
MAX(CASE WHEN NAME = 'FYQ2' THEN F END),
MAX(CASE WHEN NAME = 'FYQ3' THEN F END),
MAX(CASE WHEN NAME = 'FYQ4' THEN F END)
FROM T
UNION ALL
SELECT 'G',
MAX(CASE WHEN NAME = 'FYQ1' THEN G END),
MAX(CASE WHEN NAME = 'FYQ2' THEN G END),
MAX(CASE WHEN NAME = 'FYQ3' THEN G END),
MAX(CASE WHEN NAME = 'FYQ4' THEN G END)
FROM T
| | FYQ1 | FYQ2 | FYQ3 | FYQ4 |
|---|------|------|------|------|
| A | 11 | 11 | 11 | 11 |
| C | 8 | 8 | 11 | 11 |
| D | 6 | 7 | 10 | 10 |
| E | 1 | 2 | 5 | 5 |
| F | 0 | 0 | 4 | 4 |
| G | 0 | 0 | 0 | 0 |
You can use unpivot and pivot, i share example.
CREATE TABLE T(
Name varchar(50),
A int,
B int,
C int,
D int,
E int,
F int,
G int
);
INSERT INTO T VALUES ('FYQ1',11,10,8 ,6,1,0,0);
INSERT INTO T VALUES ('FYQ2',11,10,8 ,7,2,0,0);
INSERT INTO T VALUES ('FYQ3',11,11,11,10,5,4,0);
INSERT INTO T VALUES ('FYQ4',11,11,11,10,5,4,0);
SELECT words,
fyq1,
fyq2,
fyq3,
fyq4
FROM (
SELECT name,
fyq1 AS words,
value
FROM (
SELECT *
FROM t) unpivot ( (value) FOR (fyq1,fyq2,fyq3,fyq4) IN (a,
b,
c,
d,
e,
f,
g) )) pivot( SUM(value) FOR name IN ('FYQ1' AS fyq1,
'FYQ2' AS fyq2,
'FYQ3' AS fyq3,
'FYQ4' AS fyq4) )
ORDER BY words;
