http://www.sqlservercentral.com/blogs/briankmcdonald/2010/05/28/flipping-a-table-sideways/

Printed 2014/08/28 05:42PM

Flipping a Table Sideways

By Brian K. McDonald, 2010/05/28

Not too often, someone asks for a strange request like wanting to pivot on every single value inside a table. No aggregations at all, just a listing of the columns as shown in the screenshot below. This happened for a colleague of mine recently. As such, a few of us quickly searched for some solution that could assist him and after many failed attempts, I came up with the below solution.

Screenshot

Desired Results

 

 

Step 1: Setting up the Temp Table

create table #McValues (value varchar(5))

insert into #McValues SELECT 'abc'

insert into #McValues SELECT 'def'

insert into #McValues SELECT 'ghi'

insert into #McValues SELECT 'jkl'

insert into #McValues SELECT 'mno'

 

Figure 1: Vertical Results

Vertical Results

 Step 2: Flip Each Value

Basically what this is doing is creating a horizontal list of the results of the table as shown in figure 2 below.

DECLARE @Columns VARCHAR(MAX)

SELECT @Columns = COALESCE(@Columns + ',[' + cast(value as varchar) + ']',

    '[' + cast(value as varchar)+ ']'

  )

FROM #McValues

 

DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = N'

SELECT *

FROM (

      SELECT value

      FROM #McValues

) AS PivotData ' +

'PIVOT

(

min(value)

FOR value IN (

 ' + @Columns +

 ')

 ) AS PivotTable

'

 

And finally, all you have to do is execute the statement. Results will look similar to what is in figure 2 below.

EXECUTE (@SQL)

--OR YOU CAN DO THIS

EXECUTE sp_executesql @SQL 

 

 

 

Figure 2: Horizontal Results

 Horizontal Results

 

Until next time, “keep your ear to the grindstone” – Good Will Hunting

 

Brian K. McDonald, MCDBA, MCSD
Business Intelligence Consultant – Pragmatic Works Consulting

Email: bmcdonald@pragmaticworks.com | Blog: BI Developer Network

Convert with DTS xChange  | Develop with BI xPress  | Process with TaskFactory | Document with BI Documenter

 


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.