## Convert multiple rows into column base on another column value

0

(click on this box to dismiss)

Q&A for those involved in the field of teaching Computer Science

``````CREATE TABLE #Data
(
[No] integer NOT NULL,
Name varchar(4) NOT NULL,
Relation varchar(2) NOT NULL
);

INSERT #Data
([No], Name, Relation)
VALUES
(1, 'A', 'P'),
(1, 'B', 'GO'),
(1, 'C', 'JB'),
(2, 'AH', 'P'),
(3, 'TY', 'P'),
(3, 'DO', 'JB'),
(3, 'DO1', 'JB'),
(3, 'DO2', 'JB'),
(3, 'DO3', 'JB'),
(3, 'DO4', 'JB'),
(3, 'DO5', 'GO'),
(3, 'DO6', 'GO'),
(3, 'DO7', 'GO'),
(3, 'DO8', 'GO'),
(3, 'DO9', 'GO'),
(3, 'DO10', 'GO'),
(4, 'PA', 'P'),
(4, 'PA2', 'GO'),
(4, 'PA3', 'GO'),
(4, 'PA4', 'GO'),
(4, 'PA5', 'GO'),
(4, 'PA6', 'GO'),
(4, 'PA7', 'GO');

SELECT
P.[No], P.P,
P.JB1, P.JB2, P.JB3, P.JB4, P.JB5,
P.GO1, P.GO2, P.GO3, P.GO4, P.GO5, P.GO6
FROM
(
SELECT
D.[No],
D.Name,
RelationSeq =
CASE
-- Add a sequence number for rows with Relation JB or GO
WHEN D.Relation IN ('JB', 'GO')
THEN D.Relation +
CONVERT
(
char(1),
ROW_NUMBER() OVER (
PARTITION BY D.[No], D.Relation
ORDER BY
CONVERT(integer, SUBSTRING(D.Name, 3, 2)))
)
-- Otherwise just use the Relation
ELSE CONVERT(varchar(3), D.Relation)
END
FROM #Data AS D
) AS S
PIVOT
(
-- Standard pivot rows to columns
MAX(S.Name)
FOR RelationSeq IN
(P, JB1, JB2, JB3, JB4, JB5, GO1, GO2, GO3, GO4, GO5, GO6)
) AS P;``````

### Enter Parameters

Options:
-
Switch to meta site
Hold tight while we fetch your results
:records returned in :time ms:cached