Currently I am listing out a group of data with separate "UNION"ed select statements which creates a new row entry for each piece of information:
Example:
(No column name)
1 40
2 11
3 13
4 29
5 64
6 34
What I want is to change that into a row with labeled columns.
Example:
Column 1 Column 2 Column 3 Column 4 Column 5 Column 6
1 40 11 13 29 64 34
How would i go about doing that?
DECLARE @T TABLE (Val INT, SortOrder VARCHAR(20))
INSERT INTO @T
SELECT 40,'Column 1'
UNION
SELECT 11,'Column 2'
UNION
SELECT 13,'Column 3'
UNION
SELECT 29,'Column 4'
UNION
SELECT 64,'Column 5'
UNION
SELECT 34,'Column 6'
SELECT * FROM @T
pivot (avg (Val) for SortOrder in (
[Column 1],[Column 2],[Column 3],[Column 4],[Column 5],[Column 6])
) as Whatever
rextester: http://rextester.com/LWZH64528
results:
+----------+----------+----------+----------+----------+----------+
| Column 1 | Column 2 | Column 3 | Column 4 | Column 5 | Column 6 |
+----------+----------+----------+----------+----------+----------+
| 40 | 11 | 13 | 29 | 64 | 34 |
+----------+----------+----------+----------+----------+----------+
Date as first column value
DECLARE @T TABLE (Val VARCHAR(20), SortOrder VARCHAR(20))
INSERT INTO @T
SELECT '2016-10-12','Column 1'
UNION
SELECT '11','Column 2'
UNION
SELECT '13','Column 3'
UNION
SELECT '29','Column 4'
UNION
SELECT '64','Column 5'
UNION
SELECT '34','Column 6'
SELECT * FROM @T
pivot (MAX(Val) for SortOrder in ([Column 1],[Column 2],[Column 3],[Column 4],[Column 5],[Column 6])) as Whatever
Result:
+----------+----------+----------+----------+----------+----------+
| Column 1 | Column 2 | Column 3 | Column 4 | Column 5 | Column 6 |
+----------+----------+----------+----------+----------+----------+
|2016-10-12| 11 | 13 | 29 | 64 | 34 |
+----------+----------+----------+----------+----------+----------+