November 12, 2010 at 3:57 am
I have a situation
I have two tables
First
Id DateTime TAGA TagA_Qualyty TAGB TagB_Qualyty
125/11/2010hh:mm6070
Second
IdDateTime TagValue
125/11/2010TAGA6
225/11/2010TAGB7
The First table is loaded by external software, and i need that after each incert in first table load second table like I show above.
Someone can help me?
November 12, 2010 at 4:20 am
SET DATEFORMAT DMY
DROP TABLE #First
CREATE TABLE #First (Id INT, [DateTime] DATETIME, TAGA INT, TagA_Qualyty INT, TAGB INT, TagB_Qualyty INT)
INSERT INTO #First (Id, [DateTime], TAGA, TagA_Qualyty, TAGB, TagB_Qualyty)
SELECT 1, '25/11/2010', 6, 0, 7, 0
SELECT
ID = ROW_NUMBER() OVER(ORDER BY [DateTime], Tag),
[DateTime],
Tag,
[Value]
FROM
(
SELECT
[DateTime],
Tag = 'TAGA',
[Value] = TAGA
FROM #First
UNION ALL
SELECT
[DateTime],
Tag = 'TAGB',
[Value] = TAGB
FROM #First
) d
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 12, 2010 at 4:36 am
Another Option
Select Id,DateTime,
case when RowN = 1 then TagA
when Rown = 2 then TagB end as Tag,
case when RowN = 1 then TagA_Qualyty
when Rown = 2 then TagB_Qualyty end as Tag_Qualyty
from #first
cross join (Select 1 as Rown union Select 2 ) RowN
Order by id,Tag
November 12, 2010 at 4:55 am
Thanks for the replies.
Chris Morris-439714
your answer is exactly what I want, but if possible I would like to know if you make it so dynamic? because the first table may have changed their columns by external software, which would mean the routine maintenance
I read about the way to show a table example and I apologize for the lack of formatting
November 12, 2010 at 4:58 am
Hi Jeff
If possible, can you post a few examples of how the incoming data will look?
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 12, 2010 at 5:34 am
<?xml version="1.0" standalone="yes"?>
<_x002E_tHistorian xmlns="TatsoftDB">
<xs:schema id="_x002E_tHistorian" targetNamespace="TatsoftDB" xmlns:mstns="TatsoftDB" xmlns="TatsoftDB" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:msprop="urn:schemas-microsoft-com:xml-msprop" attributeFormDefault="qualified" elementFormDefault="qualified">
<xs:element name="_x002E_tHistorian" msdata:IsDataSet="true" msdata:Locale="en-US" msprop:sensitivity="False" msprop:specification="10">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="R01GravaHorimetro" msprop:indexes="<?xml version="1.0" encoding="utf-16"?> <xs:schema id="indexes" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:msprop="urn:schemas-microsoft-com:xml-msprop"> <xs:element name="indexes" msdata:IsDataSet="true" msdata:Locale="en-US"> <xs:complexType> <xs:choice minOccurs="0" maxOccurs="unbounded"> <xs:element name="indexes_x003A_R01GravaHorimetro"> <xs:complexType> <xs:sequence> <xs:element name="UTCTimestamp_Ticks" msprop:key="UTCTimestamp_Ticks" minOccurs="0"> <xs:simpleType> <xs:restriction base="xs:string"> <xs:length value="1" /> </xs:restriction> </xs:simpleType> </xs:element> </xs:sequence> </xs:complexType> </xs:element> </xs:choice> </xs:complexType> </xs:element> </xs:schema>">
<xs:complexType>
<xs:sequence>
<xs:element name="ID" msprop:dbType="BigInt" type="xs:long" />
<xs:element name="UTCTimestamp_Ticks" msprop:dbType="BigInt" type="xs:long" minOccurs="0" />
<xs:element name="LogType" msprop:dbType="TinyInt" type="xs:unsignedByte" minOccurs="0" />
<xs:element name="R01_BB_ALT_PR_01_HORIMETRO_DIA" msprop:dbType="Float" type="xs:double" minOccurs="0" />
<xs:element name="_R01_BB_ALT_PR_01_HORIMETRO_DIA_Q" msprop:dbType="SmallInt" type="xs:short" minOccurs="0" />
<xs:element name="R01_BB_ALT_PR_02_HORIMETRO_DIA" msprop:dbType="Float" type="xs:double" minOccurs="0" />
<xs:element name="_R01_BB_ALT_PR_02_HORIMETRO_DIA_Q" msprop:dbType="SmallInt" type="xs:short" minOccurs="0" />
<xs:element name="R01_BB_ALT_PR_03_HORIMETRO_DIA" msprop:dbType="Float" type="xs:double" minOccurs="0" />
<xs:element name="_R01_BB_ALT_PR_03_HORIMETRO_DIA_Q" msprop:dbType="SmallInt" type="xs:short" minOccurs="0" />
<xs:element name="R01_BB_ALT_PR_04_HORIMETRO_DIA" msprop:dbType="Float" type="xs:double" minOccurs="0" />
<xs:element name="_R01_BB_ALT_PR_04_HORIMETRO_DIA_Q" msprop:dbType="SmallInt" type="xs:short" minOccurs="0" />
<xs:element name="R01_BB_BOOST__01_HORIMETRO_DIA" msprop:dbType="Float" type="xs:double" minOccurs="0" />
<xs:element name="_R01_BB_BOOST__01_HORIMETRO_DIA_Q" msprop:dbType="SmallInt" type="xs:short" minOccurs="0" />
<xs:element name="R01_BB_BOOST__02_HORIMETRO_DIA" msprop:dbType="Float" type="xs:double" minOccurs="0" />
<xs:element name="_R01_BB_BOOST__02_HORIMETRO_DIA_Q" msprop:dbType="SmallInt" type="xs:short" minOccurs="0" />
<xs:element name="R01_BB_BOOST__03_HORIMETRO_DIA" msprop:dbType="Float" type="xs:double" minOccurs="0" />
<xs:element name="_R01_BB_BOOST__03_HORIMETRO_DIA_Q" msprop:dbType="SmallInt" type="xs:short" minOccurs="0" />
<xs:element name="R01_BB_ELV_T1_01_HORIMETRO_DIA" msprop:dbType="Float" type="xs:double" minOccurs="0" />
<xs:element name="_R01_BB_ELV_T1_01_HORIMETRO_DIA_Q" msprop:dbType="SmallInt" type="xs:short" minOccurs="0" />
<xs:element name="R01_BB_ELV_T1_02_HORIMETRO_DIA" msprop:dbType="Float" type="xs:double" minOccurs="0" />
<xs:element name="_R01_BB_ELV_T1_02_HORIMETRO_DIA_Q" msprop:dbType="SmallInt" type="xs:short" minOccurs="0" />
<xs:element name="R01_BB_ELV_T1_03_HORIMETRO_DIA" msprop:dbType="Float" type="xs:double" minOccurs="0" />
<xs:element name="_R01_BB_ELV_T1_03_HORIMETRO_DIA_Q" msprop:dbType="SmallInt" type="xs:short" minOccurs="0" />
<xs:element name="R01_BB_ELV_T1_04_HORIMETRO_DIA" msprop:dbType="Float" type="xs:double" minOccurs="0" />
<xs:element name="_R01_BB_ELV_T1_04_HORIMETRO_DIA_Q" msprop:dbType="SmallInt" type="xs:short" minOccurs="0" />
<xs:element name="R01_BB_ELV_T2_01_HORIMETRO_DIA" msprop:dbType="Float" type="xs:double" minOccurs="0" />
<xs:element name="_R01_BB_ELV_T2_01_HORIMETRO_DIA_Q" msprop:dbType="SmallInt" type="xs:short" minOccurs="0" />
<xs:element name="R01_BB_ELV_T2_02_HORIMETRO_DIA" msprop:dbType="Float" type="xs:double" minOccurs="0" />
<xs:element name="_R01_BB_ELV_T2_02_HORIMETRO_DIA_Q" msprop:dbType="SmallInt" type="xs:short" minOccurs="0" />
<xs:element name="R01_BB_ELV_T2_03_HORIMETRO_DIA" msprop:dbType="Float" type="xs:double" minOccurs="0" />
<xs:element name="_R01_BB_ELV_T2_03_HORIMETRO_DIA_Q" msprop:dbType="SmallInt" type="xs:short" minOccurs="0" />
<xs:element name="R01_BB_ELV_T2_04_HORIMETRO_DIA" msprop:dbType="Float" type="xs:double" minOccurs="0" />
<xs:element name="_R01_BB_ELV_T2_04_HORIMETRO_DIA_Q" msprop:dbType="SmallInt" type="xs:short" minOccurs="0" />
<xs:element name="R01_BB_ELV_T2_05_HORIMETRO_DIA" msprop:dbType="Float" type="xs:double" minOccurs="0" />
<xs:element name="_R01_BB_ELV_T2_05_HORIMETRO_DIA_Q" msprop:dbType="SmallInt" type="xs:short" minOccurs="0" />
<xs:element name="R01_BB_ORD_T1_01_HORIMETRO_DIA" msprop:dbType="Float" type="xs:double" minOccurs="0" />
<xs:element name="_R01_BB_ORD_T1_01_HORIMETRO_DIA_Q" msprop:dbType="SmallInt" type="xs:short" minOccurs="0" />
<xs:element name="R01_BB_ORD_T1_02_HORIMETRO_DIA" msprop:dbType="Float" type="xs:double" minOccurs="0" />
<xs:element name="_R01_BB_ORD_T1_02_HORIMETRO_DIA_Q" msprop:dbType="SmallInt" type="xs:short" minOccurs="0" />
<xs:element name="R01_BB_ORD_T1_03_HORIMETRO_DIA" msprop:dbType="Float" type="xs:double" minOccurs="0" />
<xs:element name="_R01_BB_ORD_T1_03_HORIMETRO_DIA_Q" msprop:dbType="SmallInt" type="xs:short" minOccurs="0" />
<xs:element name="R01_BB_ORD_T1_04_HORIMETRO_DIA" msprop:dbType="Float" type="xs:double" minOccurs="0" />
<xs:element name="_R01_BB_ORD_T1_04_HORIMETRO_DIA_Q" msprop:dbType="SmallInt" type="xs:short" minOccurs="0" />
<xs:element name="R01_BB_ORD_T2_01_HORIMETRO_DIA" msprop:dbType="Float" type="xs:double" minOccurs="0" />
<xs:element name="_R01_BB_ORD_T2_01_HORIMETRO_DIA_Q" msprop:dbType="SmallInt" type="xs:short" minOccurs="0" />
<xs:element name="R01_BB_ORD_T2_02_HORIMETRO_DIA" msprop:dbType="Float" type="xs:double" minOccurs="0" />
<xs:element name="_R01_BB_ORD_T2_02_HORIMETRO_DIA_Q" msprop:dbType="SmallInt" type="xs:short" minOccurs="0" />
<xs:element name="R01_BB_ORD_T2_03_HORIMETRO_DIA" msprop:dbType="Float" type="xs:double" minOccurs="0" />
<xs:element name="_R01_BB_ORD_T2_03_HORIMETRO_DIA_Q" msprop:dbType="SmallInt" type="xs:short" minOccurs="0" />
<xs:element name="R01_CPU_______XX_HORIMETRO_DIA" msprop:dbType="Float" type="xs:double" minOccurs="0" />
<xs:element name="_R01_CPU_______XX_HORIMETRO_DIA_Q" msprop:dbType="SmallInt" type="xs:short" minOccurs="0" />
<xs:element name="R01_VENTIL_T1_01_HORIMETRO_DIA" msprop:dbType="Float" type="xs:double" minOccurs="0" />
<xs:element name="_R01_VENTIL_T1_01_HORIMETRO_DIA_Q" msprop:dbType="SmallInt" type="xs:short" minOccurs="0" />
<xs:element name="R01_VENTIL_T1_02_HORIMETRO_DIA" msprop:dbType="Float" type="xs:double" minOccurs="0" />
<xs:element name="_R01_VENTIL_T1_02_HORIMETRO_DIA_Q" msprop:dbType="SmallInt" type="xs:short" minOccurs="0" />
<xs:element name="R01_VENTIL_T1_03_HORIMETRO_DIA" msprop:dbType="Float" type="xs:double" minOccurs="0" />
<xs:element name="_R01_VENTIL_T1_03_HORIMETRO_DIA_Q" msprop:dbType="SmallInt" type="xs:short" minOccurs="0" />
<xs:element name="R01_VENTIL_T2_01_HORIMETRO_DIA" msprop:dbType="Float" type="xs:double" minOccurs="0" />
<xs:element name="_R01_VENTIL_T2_01_HORIMETRO_DIA_Q" msprop:dbType="SmallInt" type="xs:short" minOccurs="0" />
<xs:element name="R01_VENTIL_T2_02_HORIMETRO_DIA" msprop:dbType="Float" type="xs:double" minOccurs="0" />
<xs:element name="_R01_VENTIL_T2_02_HORIMETRO_DIA_Q" msprop:dbType="SmallInt" type="xs:short" minOccurs="0" />
<xs:element name="R01_VENTIL_T2_03_HORIMETRO_DIA" msprop:dbType="Float" type="xs:double" minOccurs="0" />
<xs:element name="_R01_VENTIL_T2_03_HORIMETRO_DIA_Q" msprop:dbType="SmallInt" type="xs:short" minOccurs="0" />
<xs:element name="R01_VENTIL_T2_04_HORIMETRO_DIA" msprop:dbType="Float" type="xs:double" minOccurs="0" />
<xs:element name="_R01_VENTIL_T2_04_HORIMETRO_DIA_Q" msprop:dbType="SmallInt" type="xs:short" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:complexType>
<xs:sequence>
<xs:element name="ID" msprop:dbType="BigInt" type="xs:long" />
<xs:element name="UTCTimestamp_Ticks" msprop:dbType="BigInt" type="xs:long" minOccurs="0" />
<xs:element name="LogType" msprop:dbType="TinyInt" type="xs:unsignedByte" minOccurs="0" />
<xs:element name="RGL_IND_RECIRCXX_PERCENTUAL" msprop:dbType="Float" type="xs:double" minOccurs="0" />
<xs:element name="_RGL_IND_RECIRCXX_PERCENTUAL_Q" msprop:dbType="SmallInt" type="xs:short" minOccurs="0" />
<xs:element name="RGL_AG_RECIRC_XX_TOTAL" msprop:dbType="Float" type="xs:double" minOccurs="0" />
<xs:element name="_RGL_AG_RECIRC_XX_TOTAL_Q" msprop:dbType="SmallInt" type="xs:short" minOccurs="0" />
<xs:element name="RGL_AG_CAPTADAXX_TOTAL" msprop:dbType="Float" type="xs:double" minOccurs="0" />
<xs:element name="_RGL_AG_CAPTADAXX_TOTAL_Q" msprop:dbType="SmallInt" type="xs:short" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
<xs:unique name="Primary_x0020_Key" msprop:key="ID" msdata:PrimaryKey="true">
<xs:selector xpath=".//mstns:R01GravaHorimetro" />
<xs:field xpath="mstns:ID" />
</xs:unique>
</xs:element>
</xs:schema>
</_x002E_tHistorian>
November 12, 2010 at 5:44 am
It looks a little different to your first post: "The First table is loaded by external software, and i need that after each incert in first table load second table like I show above."
Is this data the source for table 1?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 12, 2010 at 6:06 am
sorry
In the first post I put a simplified example
This table described in XML is First table
The second table is like the first post. I found some difficult to post the image from the table1. So i had thought about post it in xml format.
The first example above is not so clear but it is what i need.
November 12, 2010 at 6:14 am
jeff.henrique.tatsoft (11/12/2010)
sorryIn the first post I put a simplified example
This table described in XML is First table
The second table is like the first post. I found some difficult to post the image from the table1. So i had thought about post it in xml format.
The first example above is not so clear but it is what i need.
Please amend the following script to match the first table. We need the structure and some sample data:
CREATE TABLE #First (Id INT, [DateTime] DATETIME, TAGA INT, TagA_Qualyty INT, TAGB INT, TagB_Qualyty INT)
INSERT INTO #First (Id, [DateTime], TAGA, TagA_Qualyty, TAGB, TagB_Qualyty)
SELECT 1, '25/11/2010', 6, 0, 7, 0
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 12, 2010 at 6:39 am
CREATE TABLE #First (Id INT, [DateTime] numeric(18,0), LogType int, TAGA INT, _TAGA_Q INT, TAGB INT, _TagB_Q INT)
INSERT INTO #First (Id, [DateTime], TAGA, TagA_Qualyty, TAGB, TagB_Qualyty)
SELECT 37,634145506545000000,1,0,192,0,192
The struct of TagA and tagB can be add like TagC or D by the user on external software.
I really apreciate your attention and i hope that you understand my needs
November 12, 2010 at 6:46 am
jeff.henrique.tatsoft (11/12/2010)
CREATE TABLE #First (Id INT, [DateTime] numeric(18,0), LogType int, TAGA INT, _TAGA_Q INT, TAGB INT, _TagB_Q INT)
INSERT INTO #First (Id, [DateTime], TAGA, TagA_Qualyty, TAGB, TagB_Qualyty)
SELECT 37,634145506545000000,1,0,192,0,192
Msg 121, Level 15, State 1, Line 2
The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns.
Also, this is very similar to the data provided in your first post.
Can you please explain what you mean by "because the first table may have changed their columns by external software, which would mean the routine maintenance". Do you mean the definition of table #First can change? Different columns?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 12, 2010 at 7:00 am
There is no neat way to do this, the design is bad.
Possibly the easiest way is to build a dynamic sql string by testing for the incrementing column group name with sys.columns.
Not neat , but do-able.
November 12, 2010 at 7:01 am
The external softwuare is a scada Software so between his funcitions is save historical date for industrial plant. But the number of dates can be add for user.
In the first example there are two points TAGA and TAGB, the user cnt take those but he can add more.
Each point is compoun for 2 colunms: TAGA and _TagA_Q
When the user add more one point like tagc willl add two columns TAGC and _TAGC_Q
The Date Time is in tick format
November 12, 2010 at 7:08 am
Dave Ballantyne
I'm looking for this
You can describe or post how i can do this?
November 12, 2010 at 7:24 am
This is why the design is so bad....
Untested , you will have to account for the other columns you need
Declare @SqlString varchar(max)
Declare @CaseStmt varchar(max)
Declare @Upvt varchar(max)
Declare @ColOn integer
Select @ColOn =1
Select @SqlString ='Select Id,DateTime,'
Select @CaseStmt = 'case when RowN = 1 then TagA'
Select @Upvt = 'Select 1 as Rown '
while(0=0) begin
Declare @LookingForCol varchar(50)
Select @LookingForCol = 'Tag'+char(ascii('A')+@ColOn)
if not exists(Select 1 from sys.columns where name = @LookingForCol and Object_id = object_id('YourTableName')) begin
break
end
Select @CaseStmt = @CaseStmt +' when Rown = '+cast(@ColOn+1 as varchar(20)+' then Tag'+ @LookingForCol
Select @Upvt = @Upvt +' union all Select '+cast(@ColOn+1 as varchar(20)
end
Select @SqlString = @SqlString + @CaseStmt +' from #first cross join ('+@Upvt+') RowN Order by id.tag'
exec sp_executesql @SqlString
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply