USE Northwind
GO
SET NOCOUNT ON
CREATE TABLE myTable99(RegionID char(1), RegionDescription varchar(20))
GO

INSERT INTO myTable99(RegionID, RegionDescription)
SELECT '1', 'Eastern'	UNION ALL
SELECT '2', 'Western'	UNION ALL
SELECT '3', 'Northern'	UNION ALL
SELECT '4', 'Southern'
GO

DECLARE @RegionID varchar(8000), @RegionDescription varchar(8000)

  SELECT @RegionID = COALESCE(@RegionId + ', ','') + RegionID 
    FROM myTable99 
ORDER BY RegionID
  SELECT @RegionDescription = COALESCE(@RegionDescription + ', ','') + RegionDescription 
    FROM myTable99 
ORDER BY RegionID

SELECT @RegionID
UNION ALL 
SELECT @RegionDescription
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

declare @sql	nvarchar(1024)
select	@sql = 'SELECT' + char(13)
select 	@sql = @sql + 'max(case when RegionID = ' 
                    + convert(varchar(10), RegionID) 
                    + ' then RegionDescription else null end) as [' 
                    + convert(varchar(10), RegionID) 
                    + '],' 
                    + char(13)
from	Region
order by RegionID

select	@sql = left(@sql, len(@sql) - 2)
select	@sql = @sql + char(13) + 'FROM Region'

print @sql

exec (@sql)

SELECT
max(case when RegionID = 1 then RegionDescription else null end) as [1],
max(case when RegionID = 2 then RegionDescription else null end) as [2],
max(case when RegionID = 3 then RegionDescription else null end) as [3],
max(case when RegionID = 4 then RegionDescription else null end) as [4]
FROM Region