SQL Server View to change structure

8aqjt8rx 于 2023-03-17 发布在 SQL Server

关注(0)|答案(2)|浏览(132)

I have an IT guy that has set up a SQL table for me (and is populating it with data)... but the layout is not great...

Instead of a 3 column layout (date, tagname, tagvalue) they have it set with a date column, then all the tag names as column names, and the values are under each column. He doesn't want to change it (this is how we always do it) and doesn't understand the benefits of the other layout (all the same information is there).

I am hoping I can use SQL Server Management Studio to build a view based on this table that will display the data in the format that I want (date, tagname, tagvalue). But I don't have a clue how to do that...

EDIT I can give you an idea of what the table looks like, but I thought it was pretty clear from above... I would like a solution where I can get table 1 structure into table 2 structure without needing to list every field name (245 and growing)

The current table structure is this...

Col1 => LoggedDT (datetime)
Col2 => TagName1 (float)
Col3 => TagName2 (float)
Col4 => TagName3 (float)
...
Col246 => TagName245 (float)

So viewing the table would look like this

LoggedDT    TagName1    TagName2    TagName3... TagName245
1/1/23 0:00        5          17          42           53
1/1/23 0:01        6          18          40           88
1/1/23 0:02        9          20          37           49

Very rarely would a database table be a good design with 240+ columns... and needing to add a new column (or remove a column) every time a tag is added or removed from the required logging.

A MUCH better design would be as such...

Col1 => LoggedDT (datetime)
Col2 => TagName  (varchar)
Col3 => TagValue (float)

And viewing the table would look like this

LoggedDT     TagName     TagValue
1/1/23 0:00  TagName1          5
1/1/23 0:00  TagName2         17
1/1/23 0:00  TagName3         42
1/1/23 0:00  TagName245       53
1/1/23 0:01  TagName1          6
1/1/23 0:01  TagName2         18
1/1/23 0:01  TagName3         40
1/1/23 0:01  TagName245       88
1/1/23 0:02  TagName1          9 
1/1/23 0:02  TagName2         20
1/1/23 0:02  TagName3         37
1/1/23 0:02  TagName245       49

sql-server

来源：https://stackoverflow.com/questions/75665747/sql-server-view-to-change-structure

2条答案

按热度按时间

pdkcd3nj1#

To convert "normalized" => "EAV" data, you can use a UNPIVOT / values construct. Something like:

SELECT  t.somedate
,   x.*
FROM    IT_Table t
CROSS APPLY (
    VALUES  
        ('Tag1', CAST(Col1 AS NVARCHAR(MAX)))
    ,   ('Tag2', CAST(Col2 AS NVARCHAR(MAX)))
    ) x (colName, colValue)

Note, that you might or might not need to CAST the values, depending on what kind of columns you got, but usually they are not all the same datatype.

赞(0）回复(0）举报 2023-03-17

6yt4nkrj2#

If there are a static number of tagname types, you can build a series of UNION ALL statements like:

SELECT x.*
FROM
(
SELECT date AS TagDate, 'TagName1' AS TagName, TagValue1 AS TagValue FROM YourTable
UNION ALL SELECT date, 'TagName2', TagValue2 FROM YourTable 
UNION ALL SELECT date, 'TagName3', TagValue3 FROM YourTable 
etc..
) x

but siggemannen's answer is more elegant and should be preferred if possible.

赞(0）回复(0）举报 2023-03-17

我来回答

SQL Server View to change structure

2条答案

相关问题

热门标签

最新问答