I have a table with 4 columns: tagid, TimeStamp, intvalue, and floatvalue.
The tagid has four different types of data: LineRun, ScrapRun, Speed, and Length.
Each tagid has a value in either the column intvalue or floatvalue.
I would like to transpose the tagid into header and insert its corresponding value that associated with it.
How would you do this?
I spent several hours trying to do it but no luck!
Use something like CASE
SELECT TimeStamp, CASE WHEN tagid = 'LineRun' THEN intvalue ELSE NULL END AS LineRun, CASE WHEN tagid = 'ScrapRun' THEN intvalue ELSE NULL END AS ScrapRun,.......
Iff you had posted readily consumable data (CREATE TABLE/INSERT statements), I'd already have a working bit of code for you.
What Anthony is suggesting is called a CROSSTAB. It's a rather ancient "black art" in SQL Server that's about twice as fast and much more useful than a PIVOT.
To learn how to do such a thing (use MAX() instead of SUM() in the article for text), please see the following article.
That method won't give you the precise output you asked for... instead, it would consolidate the timestamps and the related information to a single line.
18 People are following this question.
