Flipping a Table Sideways

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

createtable #McValues(value varchar(5))

insertinto #McValues SELECT'abc'

insertinto #McValues SELECT'def'

insertinto #McValues SELECT'ghi'

insertinto #McValues SELECT'jkl'

insertinto #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 asvarchar)+']',

'['+cast(value asvarchar)+']'

)

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

EXECUTEsp_executesql@SQL

Figure 2: Horizontal Results

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