Pivot Query

Can someone help me with a pivot query for the attached dataset.

Thanks in advance.
primarykeys.xlsx
sherbug1015Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
SharathConnect With a Mentor Data EngineerCommented:
If you know the number of positions, you can try like this.
SELECT TableName, 
       [1] as primarykeycolumn1,
	   [2] as primarykeycolumn2,
	   [3] as primarykeycolumn3
  FROM Test
 PIVOT (MAX(primarykeycolumn1) FOR position IN ([1],[2],[3])) AS p

Open in new window


if the no. of positions are unknown, you can try dynamic sql.
DECLARE @sql VARCHAR(8000),@select VARCHAR(8000)
SET @sql = (SELECT DISTINCT ',[' + CONVERT(NVARCHAR(10),position) + ']' from Test for xml PATH('')) 
SET @select = (SELECT DISTINCT ',[' + CONVERT(NVARCHAR(10),position) + '] AS primarykeycolumn' + CONVERT(NVARCHAR(10),position) from Test for xml PATH('')) 
SET @sql = '
SELECT TableName' + @select + '
  FROM Test
 PIVOT (MAX(primarykeycolumn1) FOR position IN (' + SUBSTRING(@sql,2,LEN(@sql)) + ')) AS p'
EXEC(@sql)

Open in new window


http://sqlfiddle.com/#!3/53bc8/18
http://sqlfiddle.com/#!3/53bc8/16
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.