How Design A SQL Query With Dynamic Pivot Columns in SQL Server

DECLARE @cols AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Id)
                      FROM RWX_Users FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') , 1, 1, '');

SELECT @query =
'SELECT *
FROM
(
  SELECT
    Users.Id
  FROM RWX_Users AS Users
) AS t
PIVOT 
(
  MAX(Id) 
  FOR Id IN( ' + @cols + ' )' +
') AS p ; ';

execute(@query);

Read More

Pivot table with single column in sql server

- Create Table

CREATE TABLE MyTable
([Id] int)
;

-Insert Data In to Table

INSERT INTO MyTable
([Id])
VALUES
(1),
(2),
(3),
(4),
(5)
;


-Design a query with Pivot 

select *
from
(
  select  CAST(Id AS VARCHAR(15)) +'Col' AS Name , Id AS UserId
  from MyTable
) d
pivot
(
     Min(UserId)
    FOR [Name] IN ([1Col], [2Col],[3Col],[4Col], [5Col])
) piv;

Read More