Dynamic 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 2005 Forums

Transact-SQL (2005)

Dynamic Pivot

Author

Topic

sheridanbman
Starting Member

10 Posts

Posted - 2010-08-19 : 19:13:05

Wondering if someone can help. Here is an example of my table structure.

inputDate shipCleanAccuracy billCleanAccuracy
10/3/2009 99.48 99.87
10/10/2009 99.48 99.97
10/17/2009 99.89 99.97
10/24/2009 99.57 99.87
10/31/2009 99.9 99.9
11/7/2009 99.97 99.95
11/14/2009 99.93 99.65
11/21/2009 99.96 99.99
11/28/2009 99.81 99.93
12/5/2009 99.93 99.94
12/12/2009 99.87 99.96
12/19/2009 99.94 99.94
12/26/2009 99.96 99.98
1/2/2010 99.93 99.97
1/9/2010 99.76 99.97
1/16/2010 99.99 99.97
1/23/2010 100 99.88
1/30/2010 100 99.97
2/6/2010 99.94 99.91

I was hoping to produce an output like this.

--------------- 10/3/2009 10/10/200 10/17/2009
shipCleanAccuracy 99.48 99.48 99.89
billCleanAccuracy 99.87 99.97 99.97

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-08-19 : 19:28:22

you can use the PIVOT operator with Dynamic SQL

KH
[spoiler]Time is always against us[/spoiler]

sheridanbman
Starting Member

10 Posts

Posted - 2010-08-19 : 19:32:31

khtan can i have shipCleanAccuracy and billCleanAccuracy as the rows?

I couldn't seem to find away to do that dynamically with columns as the rows.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-08-19 : 19:38:54

you will need to unpivot that first and then pivot again

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-08-19 : 19:46:32

something to help you kick start ..


select	*
from	(
		select	*
		from    yourtable
			unpivot
			(
				value
				for typ in ([shipCleanAccuracy], [billCleanAccuracy])
			) up
	) d
	pivot
	(
		sum(value)
		for inputDate in ([10/03/2009], [10/10/2009], [10/17/2009])
	) p

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources