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

0

(click on this box to dismiss)

Q&A about the site for serious players and enthusiasts of poker

``````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
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``````

### Enter Parameters

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