SQL Query Questions

Question

Hey I am not a SQl expert, but I am trying to query data and transpose it and am having trouble, here is what I am wanting:

make this

DateandTime             Val1    Val2
2020-01-01 00:00:00.000  10      20

look like this

DateandTime              Tag       Value
2020-01-01 00:00:00.000  Val1       10
2020-01-01 00:00:00.000  Val2       20

I am not sure what this kind of change this is called in SQL, but any help would be great.

Thanks

Also, this seems to be at least partly covered under https://stackoverflow.com/questions/13372276/simple-way-to-transpose-columns-and-rows-in-sql — WaitingForGuacamole, Feb 23 '21 at 16:05
Does this answer your question? [Simple way to transpose columns and rows in SQL?](https://stackoverflow.com/questions/13372276/simple-way-to-transpose-columns-and-rows-in-sql) — WaitingForGuacamole, Feb 23 '21 at 16:05

score 1 · Answer 1 · edited Feb 23 '21 at 15:57

1

This is unpivoting. A generic method is union all:

select dateandtime, 'Val1' as tag, val1 as value
from t
union all
select dateandtime, 'Val2', val2
from t;

Some databases have functionality -- specifically lateral joins -- to make this more efficient.

edited Feb 23 '21 at 15:57

Tim Biegeleisen

502,043
27
286
360

answered Feb 23 '21 at 15:56

Gordon Linoff

1,242,037
58
646
786

Tim's edit: removed alises from second half of the union query, as the database will just ignore them anyway +1. – Tim Biegeleisen Feb 23 '21 at 15:58

score 0 · Accepted Answer · answered Feb 23 '21 at 16:17

In most databases you can use a function called unpivot. Here is an example of it:

select
    *
from TAB_1
unpivot ( value  for tag in (val1, val2))

Results:

+-----------------------+----+-----+
|DATEANDTIME            |TAG |VALUE|
+-----------------------+----+-----+
|2020-01-01 00:00:00.000|VAL1|10   |
|2020-01-01 00:00:00.000|VAL2|20   |
+-----------------------+----+-----+

SQL Query Questions

2 Answers2