quote:

---

location
-------
loc1
loc2
loc3
loc4
loc5
loc6

---

quote:

---

location location location
------- ------- --------
loc1 loc2 loc3
loc4 loc5 loc6

---

IF OBJECT_ID('tempdb..#T') IS NOT NULL
DROP TABLE #T
IF OBJECT_ID('tempdb..#TT') IS NOT NULL
DROP TABLE #TT

CREATE TABLE #T( ID INT )
INSERT INTO #T
SELECT number FROM Master..spt_Values WHERE Type = 'P' AND number BETWEEN 1 and 23
DECLARE @Cnt INT
SELECT @Cnt = (COUNT(*)/2) + (COUNT(*)%2) FROM #T
SELECT *, ROW_NUMBER() OVER (PARTITION BY CASE WHEN @Cnt < ID THEN 1 ELSE 2 END
ORDER BY ID) AS 'ROW'
, CASE WHEN @Cnt < ID THEN 2 ELSE 1 END AS 'seq'
INTO #TT
FROM #T

DECLARE @Sql VARCHAR(MAX)
DECLARE @Cols VARCHAR(MAX)

SELECT @Sql = '', @Cols = ''

SELECT @Cols = @Cols + ', MAX(CASE WHEN ROW = ' + cast(ROW as varchar(10)) + 'THEN ID END) AS "' + cast(ROW as varchar(10)) + '"'
FROM ( SELECT DISTINCT ROW FROM #TT ) A

print @Cols

SELECT @Sql = 'SELECT ' + STUFF(@Cols, 1, 1, '') 
+ ' FROM #TT
GROUP BY seq '

EXEC(@Sql)