Convert iTools® archive table from narrow to wide

16.11.18

Gli archivi iTools® sono gestiti in sql mediante una formattazione “narrow”, di seguito una query utile a convertire la formattazione narrow in “wide”, molto più comoda per fare rappresentazioni grafiche:

SELECT *
FROM (
	SELECT [Time], VariableName as Name, ISNULL (CONVERT(VARCHAR, ValueInt) , ISNULL(CONVERT(VARCHAR, ValueDec), ISNULL(CONVERT(VARCHAR, ValueBool), ISNULL(ValueStr, NULL))))   as Value
	FROM [dbo].[node1_archive1]
	WHERE [Time] BETWEEN '2018-11-15 16:57:20.203' AND '2018-11-15 16:57:23.205'
) as S
PIVOT
(
    MAX(Value)
    FOR Name IN (variable1, variable2, variable3, variable4)
)AS PVT

SELECT *

FROM (

SELECT [Time], VariableName as Name, ISNULL (CONVERT(VARCHAR, ValueInt) , ISNULL(CONVERT(VARCHAR, ValueDec), ISNULL(CONVERT(VARCHAR, ValueBool), ISNULL(ValueStr, NULL)))) as Value

FROM [dbo].[node1_archive1]

WHERE [Time] BETWEEN '2018-11-15 16:57:20.203' AND '2018-11-15 16:57:23.205'

) as S

PIVOT

(

MAX(Value)

FOR Name IN (variable1, variable2, variable3, variable4)

)AS PVT

Blog

Convert iTools® archive table from narrow to wide

Lascia un commento Annulla risposta