Jeffrey.Knight@gmail.com

StackOverflow

Facebook

LinkedIn

Twitter

About

Entries

- 2010.01.08
Open password protected Excel VBA

- 2009.11.15
New project on Google Code: Video to Image Sequencer

- 2009.10.14
Bonnie++ Version 1.03c on Dell XPS M1530 w/ Solid State Drive

- 2009.10.05
This cpu is not compatible with 64-bit mode.

- 2009.10.01
omg.CORBA

- 2009.10.01
WSLoginModuleImpl

- 2009.09.29
KeyRingFileException.missingSSLKeyRingData

- 2009.09.28
Connecting to P8 Content Engine : Good Times

- 2009.09.16
Amazon MP3 downloader on Ubuntu 9.06, 64 bit

- 2009.08.19
Image Sequencing with Shell Scripting

- 2009.07.29
War ein Fenster Offen ?

- 2009.07.21
WPF (MVVM): Default focus to text box in user control

- 2009.07.21
French Plug Wiring

- 2009.05.28
Wpf: Binding to parent property (RelativeSource Ancestor DataContext)

- 2009.05.26
Derived Decorator: Auto-set Properties (C#)

- 2009.05.20
Generic Singly Linked List in C#: First Pass

- 2009.05.14
WPF Mvvm: Simple error validation on button click

- 2009.05.03
IoC with StructureMap: Loading Assemblies

- 2009.04.27
Updating WPF UI on an Eventhandler: BitmapImage, DependencyObject, and Thread Ownership

- 2009.04.15
Cruise Control.NET, Apache, Windows

- 2009.03.31
Log4net in Windows Service: AssemblyInfo.cs

- 2009.03.19
Generic functions in C# (Linq, IEnumerable)

- 2009.03.17
Subway Address 0004

- 2009.03.12
Using Reflection in C# to get Property Values

- 2009.02.16
Hello World in Spring from scratch on Linux

- 2009.01.22
[mov,mp4,m4a,3gp,3g2,mj2 @ 0x9a31330] moov atom not found

- 2009.01.09
Syncing music and video on a G3 iPhone with ubuntu linux and gtkpod

- 2008.12.04
Generic XML Serialization

- 2008.12.03
The Garden State

- 2008.12.01
Separating errors from output: Dos and Linux

- 2008.11.25
nerves in patterns on a screen

- 2008.11.18
OpenXml: Excel vs Word - strings

- 2008.11.14
Saving and Loading objects: Serialization using System.Xml.Serialization

- 2008.11.13
Linux upgrade: insufficient space on /boot

- 2008.11.10
Passing command line arguments to a Form (C#)

- 2008.11.07
Largest prime factor in C# (Sieve of Eratosthenes)

- 2008.11.05
McCarren Park, Brooklyn

- 2008.11.04
Office 2007 Open XML global string replace (C#)

- 2008.11.04
This is how I remember it

- 2008.11.03
mount status: { DRDY ERR }, 0000: read verification failed on XFS

- 2008.11.01
Lucky day

- 2008.10.27
Brazilian Jiu Jitsu Takedowns

- 2008.10.20
A Jet over Brittany

- 2008.10.15
The Web application at http://localhost could not be found. Verify that you have typed the URL correctly.

- 2008.09.28
ActiveX OCX and Windows.Forms Interop: Properties and Events

- 2008.09.16
Flash player download mp3

- 2008.09.16
T-SQL: Rotate Columns into Rows, Take 2

- 2008.09.15
T-SQL: Rotate Columns into Rows, Take 1

- 2008.09.12
Delegates: Example in C#

- 2008.09.09
Kerbellec, St. Aignan, France

- 2008.09.08
Linux VPNC not working

- 2008.09.05
C# 3.0: Auto properties and Object Initializer : Example

- 2008.09.01
Morning - St. Aignan, France

- 2008.08.29
Programatically upload file to SharePoint list

- 2008.08.28
Divide and Conquer

- 2008.07.22
Embedding an OCX in a .NET Form/Control and Calling its Methods

- 2008.06.19
Transfering Files to Windows Mobile without ActiveSync

- 2008.05.15
Setting mp4 metadata programatically: Atomic Parsley

- 2008.04.26
Optimized Portable Church

- 2008.04.10
Playing audio cds on Linux from the command line with mplayer

- 2008.03.25
A simple, easy, and free javascript compiler

- 2008.02.22
A Tree in Central Park

- 2008.02.20
Could not determine the process id of the java process

- 2008.02.05
The Gallery of Useless Errors

- 2008.02.04
Regular Expressions and Square Brackets

- 2007.12.20
Inclusive vs. Exclusive: Operator overloading the word "or"

- 2007.12.20
Currency

- 2007.12.12
Security

- 2007.12.08
The New Bluescreen of Death

- 2007.12.05
Time to Water the Plants

- 2007.11.26
Gas in England in Dollars

- 2007.11.12
Sign of the Times.

- 2007.11.11
MTA Metrocard Swipe Database

- 2007.11.04
3.7 Billion Years of Progress

- 2007.10.31
A Company Meeting

- 2007.10.30
Painting

- 2007.10.23
Gimp 2.4:Cut and Paste Image Size

- 2007.10.19
Ubuntu Gutsy Shipped with Known Breaking Bug.

- 2007.10.16
The Dollar

- 2007.10.16
System.String.IsNullOrEmpty()

- 2007.10.15
Williamsburg, Brooklyn, New York

- 2007.10.12
Keyless ssh / ssh-keygen: setup and gotchas

- 2007.10.10
How To Recover Deleted Files on Linux

- 2007.10.02
Climate Change: Arctic Melt

- 2007.10.01
No Sound on Thinkpad Running Ubuntu Linux

- 2007.09.30
South of What?

- 2007.09.30
Population

- 2007.09.21
Planes, Trains, and Automobiles

- 2007.09.18
How To Handle with Spaces in File Names in Linux Shell Scripts

- 2007.09.13
The New Ireland

- 2007.09.04
Medieval Style Door Hanging

- 2007.09.01
How To: Support Old Links and Redirect to New ASP.NET Pages

- 2007.08.28
Why Doesn't My Shared Add-In for Office Show Up?

- 2007.08.27
St. Aignan, France

- 2007.08.24
Ajax in Visual Studio 2008 Beta 2: The UpdatePanel

- 2007.08.23
How To Create a Console/Window Hybrid Application in C#

- 2007.08.20
How To Find Duplicate Files (Even if they have different names!). And some History.

- 2007.08.20
How (not) To Update a Label/Div from a Drop Down Menu

- 2007.08.20
How To Convert a Batch of OGGS to MP3s with Linux

- 2007.08.17
How To Rip the Sound from an FLV (Youtube) to MP3 Using Ubuntu Linux

T-SQL: Rotate Columns into Rows, Take 1

[edit: improved version here.]

The following procedure I wrote will rotate one row of SQL results into a column of name/value pairs.
Note: you can either specify a wildcard for all columns (*) or else pass in a piped list of columns
Related:
PIVOT.

/*
Usage: (ie AdventureWorks)
rotate 'Table_Schema', 'Table_Name', '|Columns|To|Rotate|', 'Where Clause'
Example:
rotate 'Person', 'Address', '*', 'addressid=1'
rotate 'Person', 'Address', '|AddressId|AddressLine1|PostalCode|', 'addressid=50'
compare with:
select AddressId, AddressLine1, PostalCode from Person.Address where addressid=1
Author:
Jeffrey.Knight@gmail.com Mon Sep 15 19:59:10 EDT 2008
*/
create  procedure rotate(@table_schema varchar(25), 
						@table_name varchar(25), 
						@columns varchar(500),
						@whereClause varchar(255)) as
set nocount on
if(@table_schema = '' OR @table_name = '' OR @columns = '' OR @whereClause = '') return

declare @colCount int
declare @columnName  varchar(40)
declare @sql VARCHAR(max) ; set @sql = ''
declare @i int; set @i = 0

declare tableCursor cursor for select column_Name from information_schema.columns 
	where  table_schema=@table_schema and table_name= @table_name 
	order by table_schema, table_name
set @colCount=@@ROWCOUNT
open tableCursor

fetch next from tableCursor into @columnName
while @@FETCH_STATUS = 0 begin
	if(charindex('|' + @columnName + '|', @columns) > 0) OR charindex('*', @columns) > 0 begin
		set @sql = @sql + ' select ''' + @columnName + ''' as Name, + cast(' + @columnName + 
                       ' as varchar(255)) as Value from ' + @table_schema + '.' + @table_name + 
                       ' where ' + @whereClause + '
		UNION'
	end
	
	fetch next from tableCursor into @columnName 
	set @i = @i + 1
end
deallocate tableCursor
set @sql = substring(@sql, 0, len(@sql) - len('UNION'))

exec(@sql)

Categories

,
| Comments (0)TrackBacks (0)