vCampus is focusing on the PI Developer community. Questions regarding PI Interfaces not related to vCampus should be send to the TechSupport (email@example.com).
Said that, you might start experimenting with the following query:
CHARINDEX(',',Table_1.value,1)-1) AS a,
CHARINDEX(',',Table_1.value,1) + 1,
CHARINDEX(',',Table_1.value,CHARINDEX(',',Table_1.value,1)+1) - CHARINDEX(',',Table_1.value,1)-1) AS b,
CHARINDEX(',',Table_1.value,CHARINDEX(',',Table_1.value,1)+1) + 1,
LEN(Table_1.value) - CHARINDEX(',',Table_1.value,CHARINDEX(',',Table_1.value,1)+1)) AS c
I'm not sure if I understand your question properly but if I think you trying to do is "UNPIVOT". New versions of SQL Server have an "UNPIVOT" command where you can tranpose columns into rows.
For example, given a table such as:
LogTimeStamp ColumnA ColumnB ColumnB
6/19/2009 10 20 30
6/20/2009 50 60 70
You can unpivot by doing this command:
SELECT [LogTimeStamp], [Column_Name], [Values]
UNPIVOT ([Values] FOR [Column_Name] IN (ColumnA, ColumnB, ColumnC)) as unpvtTable
The results will be:
LogTimeStamp Column_Name Values
6/19/2009 ColumnA 10
6/19/2009 ColumnB 20
6/19/2009 ColumnC 30
6/20/2009 ColumnA 50
6/20/2009 ColumnB 60
6/20/2009 ColumnC 70
Hope this helps.
Also Andreas, this isn't necessarily an interface issue. In any case if vCampus is a true community, then I think any question is fair game that any community can assist if possible.
This is SQL related, google it ;)
Googling PI Server subject is rather unsucesssful ;)
There is a parameter in SQL that allows you to pass values the way you want...
It's called IN... as IN (as magically as 'between', I know.)
Select sinusoid, cdt158, sinusoidu where values in (15, 23, 34, 56, 34) and timestamp between sysdate-1 and sysdate
So, using IN will do what you want for ALL tags, not just one, but you can always force it with AND and OR like "WHERE (taga = 'sinusoid' AND value in (12, 23, 34, 45)) OR (tagb = 'cdt158' AND value in (9, 98, 87, 76, 65))" and the 'between' keyword is used to compress this "timestamp > sysdate-1 and timestamp < sysdate" into this "sysdate between sysdate-i AND sysdate" really cool, eh?
As someone commented, you can use 'unpivot' or even some 'union's...
Try something like
select taga where fecha=#12-05-09# where values in "A, B, C"
select tagb where fecha=#12-05-09# wwhere values in "D, E, F"
select tagc where fecha=#12-05-09# where values in "G, H, I"
Best of lucks!