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

0

(click on this box to dismiss)

Q&A about the site for software quality control experts, automation engineers, and software testers

``````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 main site
Hold tight while we fetch your results
:records returned in :time ms:cached