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

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

 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

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