SQL Server Rows to Columns for Dynamic Columns/Rows

We could covert the rows to columns and columns to rows using PIVOT and UNPIVOT options in SQL Server.

Use of PIVOT and UNPIVOT are very easy when it comes to display static columns (when you know the column names). But what if you don't know about column names or column count etc.

That's where this article will help

1. I have created table like below query

CREATE TABLE [DBO].[PIVOTTEST](

       [NAME] [VARCHAR](50) NULL,

       [VAL] [VARCHAR](100) NULL

) ON [PRIMARY]

2. Inserted sample Data into table

3. Use the Below Query to PIVOT for Dynamic columns

--VARIABLES

DECLARE   @QUERY AS NVARCHAR(MAX)

DECLARE   @COLUMNS AS NVARCHAR(MAX)

--------------------------------------------------------------------

----GET UNIQUE VALUES OF PIVOT COLUMN 

SELECT  @COLUMNS= COALESCE(@COLUMNS + ',','') + Quotename(Name)

FROM (SELECT DISTINCT Name FROM [DBO].PIVOTTEST) AS PIVOTEXAMPLE

-------------------------------OR-----------------------------------

----GET UNIQUE VALUES OF PIVOT COLUMN

SELECT @COLUMNS = STUFF((SELECT ',' + QUOTENAME(NAME) FROM PIVOTTEST                   

            FOR XML PATH('')) ,1,1,'')

--------------------------------------------------------------------

--CREATE THE DYNAMIC QUERY WITH ALL THE VALUES FOR

--PIVOT COLUMN AT RUNTIME

SET   @QUERY =

    N'SELECT ' +   @COLUMNS + '

    FROM [DBO].[PIVOTTEST]

    PIVOT (MAX (Val)

          FOR Name IN (' + @COLUMNS + '))P'

--EXECUTE DYNAMIC QUERY

Execute  SP_EXECUTESQL @QUERY

4. Result would be 

I hope this article will be very helpful to all. Thanks for reading this article.

“Keep reading and share the knowledge”  

“Grow more trees to save the Earth”