Pivot Only 2 Columns of Data

KANEDA 0149
KANEDA 0149 used Ask the Experts™
on
I'm trying to pivot a single table that contains 7 columns but I really only need 2 of them.  One column is called ID and the other is PROFILE_ID which is the one I want to transpose/pivot into 7 new columns based on the values originally returned.   I know I can use a union but I'm trying to simply it if when I have other scenarios similar to this that have more values like 27 that needs to get transposed/pivoted.  I tried a pivot syntax but it didn't seem to work for me or at least I'm doing it wrong.  I only need the 'ID' and 'PROFILE_ID' column from the original table that needs to be pivoted or transposed.  Below is an example of the original output and what I need or expect to get.  Thanks in advance!

Original Output from select * dbo.Table
Original Output
Need Output in this Format
Expected Output
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
Brian CroweDatabase Administrator
Top Expert 2005
Commented: 
DECLARE @Table TABLE
(
	ID				INT,
	Profile_ID		INT
);

INSERT @Table (ID, Profile_ID)
VALUES
	(245613, 13),
	(245613, 11),
	(245613, 9),
	(310007, 11),
	(310007, 12),
	(310007, 9),
	(310007, 8),
	(310007, 10),
	(310007, 13),
	(320014, 8);

SELECT ID, [8], [9], [10], [11], [12], [13], [14]
FROM
(
	SELECT *
	FROM @Table
) AS Src
PIVOT
(
	MAX(Profile_ID)
	FOR Profile_ID IN ([8], [9], [10], [11], [12], [13], [14])
) AS Pvt

Open in new window

Database Administrator
Top Expert 2005
Commented:
Here is a dynamic version since I assume that would be your next request...

CREATE TABLE #Table
(
	ID				INT,
	Profile_ID		INT
);

INSERT #Table (ID, Profile_ID)
VALUES
	(245613, 13),
	(245613, 11),
	(245613, 9),
	(310007, 11),
	(310007, 12),
	(310007, 9),
	(310007, 8),
	(310007, 10),
	(310007, 13),
	(320014, 8);

DECLARE @ColList	NVARCHAR(4000),
	@SQL			NVARCHAR(4000);

SELECT @ColList =
	STUFF(
	(
		SELECT ',[' + CAST(Profile_ID AS NVARCHAR) + ']'
		FROM #Table
		GROUP BY Profile_ID
		ORDER BY Profile_ID
		FOR XML PATH('')
	), 1, 1, '');

SELECT @SQL = 
	'SELECT ID, ' + @ColList + 
	'FROM
	(
		SELECT *
		FROM #Table
	) AS Src
	PIVOT
	(
		MAX(Profile_ID)
		FOR Profile_ID IN (' + @ColList + ')
	) AS Pvt';

EXECUTE sp_executesql @SQL;

Open in new window

KANEDA 0149

Author

Commented:
Thank you Brian, that is exactly what I needed and it worked perfectly!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial