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