How to dynamically convert rows to columns in sql

Question

If I have a table as follows.

TagName Value Datetime
--------------------------
  T1     v1      D1
  T2     v2      D1
  T3     v3      D1
  T1     v4      D2
  T2     v5      D2
   T3     v6      D2

and I want to create a select query that returns the following

T1    T2    T3    Datetime
----------------------------
v1    v2    v3       d1
V4    v5    v6       d2

The T1/T2/T3 is dynamic i.e there can be any number of tags. I have the database that records the value of different tags at particular timestamp. I want the values of all the tags at one timestamp in one row. Would anybody know how to do this?

Did you search for dynamic cross tab for SQL server on this site or google? Here's 1 of many results: http://stackoverflow.com/questions/41935934/crosstab-query-with-dynamic-columns-in-sql-server-2008 — xQbert, May 08 '17 at 15:41

score 0 · Answer 1 · answered May 08 '17 at 15:51

Example

Declare @SQL varchar(max) = Stuff((Select Distinct ',' + QuoteName(TagName) From Yourtable  Order by 1 For XML Path('')),1,1,'') 
Select  @SQL = '
Select ' + @SQL + ',[DateTime]
From  YourTable
Pivot (max([Value]) For [TagName] in (' + @SQL + ') ) p'
Exec(@SQL);

Returns

T1  T2  T3  DateTime
v1  v2  v3  D1
v4  v5  v6  D2

How to dynamically convert rows to columns in sql

1 Answers1