CODEWITHSUNDEEP
mysqlsql
David
David

Reputation: 564

Transform single table row + column names into columns

I have a simple SQL select query that returns the following result (single row):

 [0]  [1]  [2]  [3] [13] (column headings)
 5102 4516 31   95  2    (row data)

Using SQL, I need to transform this to return two columns (5 rows) that looks like this:

[id]  [val]
0     5102
1     4516
2     31
3     95
13    2

I expect to be able to do this using pivot or something similar.

SQL query used to produce the first (single row) result:

 SELECT * FROM TABLE WHERE KEY = BLAH

Upvotes: 2

Views: 115

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93694

You need to unpivot the data

For SQL Server use Cross Apply. Try this

SELECT id,val
FROM   yourtable
       CROSS apply (VALUES ([0],'0'),
                           ([1],'1'),
                           ([2],'2'),
                           ([3],'3'),
                           ([13],'13')) cs  (val,id)

or Use Union ALL to unpivot the data

SELECT [0] AS val,'0' AS id
FROM   yourtable
UNION ALL
SELECT [1],'1'
FROM   yourtable
UNION ALL
SELECT [2],'2'
FROM   yourtable
UNION ALL
SELECT [3],'3'
FROM   yourtable
UNION ALL
SELECT [13],'13'
FROM   yourtable

Upvotes: 1

Related Questions