I need to change a table data with information from a different table

Question

I have 2 tables

I need that whenever a consultation is made or inves to show the tagname in Table 2 the data in the column mask and whenever a new tagname it already updates to mask

Follows the code:

USE [master]
GO
/****** Object:  Table [dbo].[historian]    Script Date: 19/09/2019 10:00:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[historian](
    [TIMESTAMP] [datetime] NULL,
    [TAGNAME] [varchar](max) NULL,
    [VALUE] [float] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
INSERT [dbo].[historian] ([TIMESTAMP], [TAGNAME], [VALUE]) VALUES (CAST(N'2019-01-01T00:00:00.000' AS DateTime), N'BET', 1)
INSERT [dbo].[historian] ([TIMESTAMP], [TAGNAME], [VALUE]) VALUES (CAST(N'2019-01-01T01:00:00.000' AS DateTime), N'BET', 2)
INSERT [dbo].[historian] ([TIMESTAMP], [TAGNAME], [VALUE]) VALUES (CAST(N'2019-01-01T02:00:00.000' AS DateTime), N'BET', 3)
INSERT [dbo].[historian] ([TIMESTAMP], [TAGNAME], [VALUE]) VALUES (CAST(N'2019-01-01T00:00:00.000' AS DateTime), N'JAC', 11)
INSERT [dbo].[historian] ([TIMESTAMP], [TAGNAME], [VALUE]) VALUES (CAST(N'2019-01-01T01:00:00.000' AS DateTime), N'JAC', 22)
INSERT [dbo].[historian] ([TIMESTAMP], [TAGNAME], [VALUE]) VALUES (CAST(N'2019-01-01T02:00:00.000' AS DateTime), N'JAC', 33)
INSERT [dbo].[historian] ([TIMESTAMP], [TAGNAME], [VALUE]) VALUES (CAST(N'2019-01-01T00:00:00.000' AS DateTime), N'SAO', 111)
INSERT [dbo].[historian] ([TIMESTAMP], [TAGNAME], [VALUE]) VALUES (CAST(N'2019-01-01T01:00:00.000' AS DateTime), N'SAO', 222)
INSERT [dbo].[historian] ([TIMESTAMP], [TAGNAME], [VALUE]) VALUES (CAST(N'2019-01-01T02:00:00.000' AS DateTime), N'SAO', 333)
DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.TAGNAME) 
            FROM historian as c 
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT TIMESTAMP, ' + @cols + ' from 
            (
                select TIMESTAMP
                    , VALUE
                    , TAGNAME
                from historian
           ) x
            pivot 
            (
                 max(VALUE)
                for TAGNAME in (' + @cols + ')
            ) p '


execute(@query)

but basically where in table 1 is the TAGNAME, at the time of the query, I need it to return the information of the MASK column of table 2, when the pivot is executed instead of the TAGNAME it will show the MASK of table 2 in place of the TAGNAME of table 1

Answer 1

Considering only the tables you put in the question, since your SQL command references columns that do not exist in the tables presented, or need to make a join, just use the historian table (unless there has been some mistake in the question):

SELECT DISTINCT mask FROM historian WHERE tagname = ?;

I couldn’t understand the reason for the repetitions. Modeling error?