Pivot - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Pivot

Author

Topic

boosts
Starting Member

9 Posts

Posted - 2012-06-11 : 11:29:55

I have a table with 20 rows and the following structure:

FeatureID | Value
---------------------
3243241 | 4.232
3243241 | 3.234
. | .
. | .
. | .

And I need to write a sproc to take in the FeatureID as a variable and return the following:

FeatureID | Point1 | Point2 | ...
--------------------------------------------
3243241 | 4.232 | 3.234 | ...

And I cannot seem to wrap my head around this simple task :)

Any help is appreciated.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-06-11 : 11:37:33

[code]DECLARE @a TABLE(FeatureID INT, VALUE SMALLMONEY)
INSERT @a VALUES(3243241,4.232)
INSERT @a VALUES(3243241,3.234)

SELECT FeatureID, Point1, Point2, Point3
FROM (SELECT *, 'Point' + CAST(ROW_NUMBER() OVER (PARTITION BY FeatureID ORDER BY FeatureID) AS VARCHAR) Point
FROM @a) a
PIVOT(MAX(value) FOR Point IN(Point1,Point2,Point3)) b[/code]If you need to generate the headings dynamically then use this:

http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

boosts
Starting Member

9 Posts

Posted - 2012-06-11 : 13:15:56

Very nice. Thank you!

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-06-14 : 08:03:59

In case you need to generate the headings dynamically then you can do a Dynamic Pivot as follows:


--Creating Table

Create Table Ex
(FeatureID int,
 Value Float )


--Inserting Sample Data
 
Insert Into Ex
Select 3243241, 4.232
Union ALL
Select 3243241, 3.234


--Dynamic Pivot

Declare @cols Varchar(Max), @sql Varchar(Max)
Declare @temp Table(Cols Varchar(10) )
Insert Into @temp
	Select Distinct 'Point' + Cast(ROW_NUMBER() Over (Partition By FeatureId Order By(Select NULL) ) As Varchar(10) ) As rn From Ex
Select @cols = Coalesce(@cols + ', ', '') +QUOTENAME(Cols) From @temp
Set @sql = 'Select FeatureID, '+@cols+' From
				(Select *, ''Point'' + Cast(ROW_NUMBER() Over (Partition By FeatureId Order By(Select NULL) ) As Varchar(10) ) As rn From Ex) As a
			Pivot
			(Max(Value) For rn In ('+@cols+') ) As Pvt'
Execute (@sql)

N 28° 33' 11.93148"
E 77° 14' 33.66384"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources