June 28, 2015 at 1:12 am
I have a table imported from a legacy Oracle database that stores values vertically in name/value pairs. I store it in table-type variable that is an exact copy of the structure:
DECLARE @OracleEngData TABLE
( DataSourceCHAR(8)
, [OMNI_NUMBER] INTEGER
, [TIMESTAMP] INTEGER
, [DATA_TYPE] NVARCHAR(24)
, [PARAMETER] NVARCHAR(32)
, [PARAMETER_VALUE] NVARCHAR(132));
If this information were pivoted horizontally:
OMNI_NUMBER would be the primary key.
TIMESTAMP is a 10-digit integer that represents the number of seconds since 1/1/1970 UTC that requires additional conversion. Ugh.
DATA_TYPE is not the data type. It is a general categorization of the next two columns.
PARAMTER would be the column headings if it were horizontal
PARAMETER_VALUE would be the data value in that column.
I would like to try to use PIVOT to list the PARAMETER column values as column headers. This seems to work fine. What's confusing me is that I'd like it to list the PARAMETER_VALUE column values as raw data, just as it is in the source version, without having to apply some sort of aggregate function to it.
Here's a CSV sample of the data you can paste into Excel. I'm trying to transform this:
OMNI_NUMBER,TIMESTAMP,DATA_TYPE,PARAMETER,PARAMETE_VALUE
506026,1413240436,test_data,cnr,211250000,54.8
506026,1413244259,test_data,cnr,211250000,53.2
506026,1413244679,test_data,cnr,211250000,53.1
506026,1413309646,test_data,cnr,211250000,53.4
506026,1413315987,test_data,cnr,211250000,53
506026,1414519090,test_data,cnr,211250000,53.2
506026,1414520072,test_data,cnr,211250000,53.3
506026,1413240436,test_data,cnr,325250000,55
506026,1413244259,test_data,cnr,325250000,53.2
506026,1413244679,test_data,cnr,325250000,53.3
506026,1413309646,test_data,cnr,325250000,53.2
506026,1413315987,test_data,cnr,325250000,52.7
506026,1414519090,test_data,cnr,325250000,53.4
506026,1414520072,test_data,cnr,325250000,53.3
506026,1413240436,test_data,cnr,547250000,55.2
506026,1413244259,test_data,cnr,547250000,53.5
506026,1413244679,test_data,cnr,547250000,53.4
506026,1413309646,test_data,cnr,547250000,53.3
506026,1413315987,test_data,cnr,547250000,53.2
506026,1414519090,test_data,cnr,547250000,53.5
506026,1414520072,test_data,cnr,547250000,53.7
506026,1413240436,test_data,cnr,55250000,55
506026,1413244259,test_data,cnr,55250000,53.6
506026,1413244679,test_data,cnr,55250000,53.6
506026,1413309646,test_data,cnr,55250000,53.8
506026,1413315987,test_data,cnr,55250000,53.6
506026,1414519090,test_data,cnr,55250000,53.4
506026,1414520072,test_data,cnr,55250000,53.6
...into this (beginning the pivot on the 'cnr' columns)...
OMNI_NUMBER,TIMESTAMP,DATA_TYPE,cnr211.25M,cnr325.25M,cnr547.25M,cnr552.50M506026,1413240436,test_data,54.8,55,55.2,55
506026,1413244259,test_data,53.2,53.2,53.5,53.6
506026,1413244679,test_data,53.1,53.3,53.4,53.6
506026,1413309646,test_data,53.4,53.2,53.3,53.8
506026,1413315987,test_data,53,52.7,53.2,53.6
506026,1414519090,test_data,53.2,53.4,53.5,53.4
506026,1414520072,test_data,53.3,53.3,53.7,53.6
But I don't want the sum of the values or the average of the values, just the values. The PIVOT syntax seems to require an aggregate operation. Your ideas?
Thank you!
June 28, 2015 at 1:58 am
Quick cross-tab suggestion
😎
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @OracleEngData TABLE
( [OMNI_NUMBER] INTEGER
, [TIMESTAMP] INTEGER
, [DATA_TYPE] NVARCHAR(24)
, [DATA_TYPE2] NVARCHAR(24)
, [PARAMETER] NVARCHAR(32)
, [PARAMETER_VALUE] NVARCHAR(132));
INSERT INTO @OracleEngData
(OMNI_NUMBER,TIMESTAMP,DATA_TYPE,DATA_TYPE2,PARAMETER,[PARAMETER_VALUE])
VALUES
(506026,1413240436,'test_data','cnr',211250000,54.8)
,(506026,1413244259,'test_data','cnr',211250000,53.2)
,(506026,1413244679,'test_data','cnr',211250000,53.1)
,(506026,1413309646,'test_data','cnr',211250000,53.4)
,(506026,1413315987,'test_data','cnr',211250000,53 )
,(506026,1414519090,'test_data','cnr',211250000,53.2)
,(506026,1414520072,'test_data','cnr',211250000,53.3)
,(506026,1413240436,'test_data','cnr',325250000,55 )
,(506026,1413244259,'test_data','cnr',325250000,53.2)
,(506026,1413244679,'test_data','cnr',325250000,53.3)
,(506026,1413309646,'test_data','cnr',325250000,53.2)
,(506026,1413315987,'test_data','cnr',325250000,52.7)
,(506026,1414519090,'test_data','cnr',325250000,53.4)
,(506026,1414520072,'test_data','cnr',325250000,53.3)
,(506026,1413240436,'test_data','cnr',547250000,55.2)
,(506026,1413244259,'test_data','cnr',547250000,53.5)
,(506026,1413244679,'test_data','cnr',547250000,53.4)
,(506026,1413309646,'test_data','cnr',547250000,53.3)
,(506026,1413315987,'test_data','cnr',547250000,53.2)
,(506026,1414519090,'test_data','cnr',547250000,53.5)
,(506026,1414520072,'test_data','cnr',547250000,53.7)
,(506026,1413240436,'test_data','cnr',55250000,55 )
,(506026,1413244259,'test_data','cnr',55250000,53.6 )
,(506026,1413244679,'test_data','cnr',55250000,53.6 )
,(506026,1413309646,'test_data','cnr',55250000,53.8 )
,(506026,1413315987,'test_data','cnr',55250000,53.6 )
,(506026,1414519090,'test_data','cnr',55250000,53.4 )
,(506026,1414520072,'test_data','cnr',55250000,53.6 );
;WITH BASE_DATA AS
(
SELECT
OD.OMNI_NUMBER
,ROW_NUMBER() OVER
(
PARTITION BY OD.PARAMETER
ORDER BY OD.TIMESTAMP
) AS RID
,OD.TIMESTAMP
,OD.DATA_TYPE
,OD.DATA_TYPE2
,OD.PARAMETER
,OD.PARAMETER_VALUE
FROM @OracleEngData OD
)
SELECT
BD.OMNI_NUMBER
,MIN(BD.TIMESTAMP)
,BD.DATA_TYPE
,BD.DATA_TYPE2
,MAX(CASE WHEN BD.RID = 1 THEN BD.PARAMETER_VALUE END) AS COL01
,MAX(CASE WHEN BD.RID = 2 THEN BD.PARAMETER_VALUE END) AS COL02
,MAX(CASE WHEN BD.RID = 3 THEN BD.PARAMETER_VALUE END) AS COL03
,MAX(CASE WHEN BD.RID = 4 THEN BD.PARAMETER_VALUE END) AS COL04
,MAX(CASE WHEN BD.RID = 5 THEN BD.PARAMETER_VALUE END) AS COL05
,MAX(CASE WHEN BD.RID = 6 THEN BD.PARAMETER_VALUE END) AS COL06
,MAX(CASE WHEN BD.RID = 7 THEN BD.PARAMETER_VALUE END) AS COL07
FROM BASE_DATA BD
GROUP BY BD.OMNI_NUMBER
,BD.PARAMETER
,BD.DATA_TYPE
,BD.DATA_TYPE2
;
Results
OMNI_NUMBER DATA_TYPE DATA_TYPE2 COL01 COL02 COL03 COL04 COL05 COL06 COL07
----------- ----------- ----------- ----------- ------ ------ ------ ------ ------ ------ ------
506026 1413240436 test_data cnr 54.8 53.2 53.1 53.4 53.0 53.2 53.3
506026 1413240436 test_data cnr 55.0 53.2 53.3 53.2 52.7 53.4 53.3
506026 1413240436 test_data cnr 55.2 53.5 53.4 53.3 53.2 53.5 53.7
506026 1413240436 test_data cnr 55.0 53.6 53.6 53.8 53.6 53.4 53.6
June 29, 2015 at 8:53 am
There are a couple of things about the original script that bring up questions. The primary one is that the values that will end up creating the column name cnr552.50M, appear to be one zero shy of the appropriate value in the PARAMETER column of the source data. Also, PARAMETE_VALUE could just be PARAMETER_VALUE, so I coded it that way, and supplied the missing zeros so that a quick transformation of the original data would allow for the use of PIVOT. As your sample data has no issues with the use of the aggregate because there are no records that would get missed by virtue of same, I'm confident that as long as your sample data is representative of your actual data in that respect, that the aggregate won't be a problem. The only other thing you have to worry about is having the same number of values for each group, and that you'll know the resulting column names in advance. If that latter constraint cannot be met, you'll need dynamic SQL, and this will be a fair chunk more complicated. Here's the simple PIVOT:
WITH SOURCE_DATA (OMNI_NUMBER, [TIMESTAMP], DATA_TYPE, DATA_TYPE2, PARAMETER, PARAMETER_VALUE) AS (
SELECT 506026,1413240436,'test_data','cnr',211250000,54.8 UNION ALL
SELECT 506026,1413244259,'test_data','cnr',211250000,53.2 UNION ALL
SELECT 506026,1413244679,'test_data','cnr',211250000,53.1 UNION ALL
SELECT 506026,1413309646,'test_data','cnr',211250000,53.4 UNION ALL
SELECT 506026,1413315987,'test_data','cnr',211250000,53 UNION ALL
SELECT 506026,1414519090,'test_data','cnr',211250000,53.2 UNION ALL
SELECT 506026,1414520072,'test_data','cnr',211250000,53.3 UNION ALL
SELECT 506026,1413240436,'test_data','cnr',325250000,55 UNION ALL
SELECT 506026,1413244259,'test_data','cnr',325250000,53.2 UNION ALL
SELECT 506026,1413244679,'test_data','cnr',325250000,53.3 UNION ALL
SELECT 506026,1413309646,'test_data','cnr',325250000,53.2 UNION ALL
SELECT 506026,1413315987,'test_data','cnr',325250000,52.7 UNION ALL
SELECT 506026,1414519090,'test_data','cnr',325250000,53.4 UNION ALL
SELECT 506026,1414520072,'test_data','cnr',325250000,53.3 UNION ALL
SELECT 506026,1413240436,'test_data','cnr',547250000,55.2 UNION ALL
SELECT 506026,1413244259,'test_data','cnr',547250000,53.5 UNION ALL
SELECT 506026,1413244679,'test_data','cnr',547250000,53.4 UNION ALL
SELECT 506026,1413309646,'test_data','cnr',547250000,53.3 UNION ALL
SELECT 506026,1413315987,'test_data','cnr',547250000,53.2 UNION ALL
SELECT 506026,1414519090,'test_data','cnr',547250000,53.5 UNION ALL
SELECT 506026,1414520072,'test_data','cnr',547250000,53.7 UNION ALL
SELECT 506026,1413240436,'test_data','cnr',552500000,55 UNION ALL
SELECT 506026,1413244259,'test_data','cnr',552500000,53.6 UNION ALL
SELECT 506026,1413244679,'test_data','cnr',552500000,53.6 UNION ALL
SELECT 506026,1413309646,'test_data','cnr',552500000,53.8 UNION ALL
SELECT 506026,1413315987,'test_data','cnr',552500000,53.6 UNION ALL
SELECT 506026,1414519090,'test_data','cnr',552500000,53.4 UNION ALL
SELECT 506026,1414520072,'test_data','cnr',552500000,53.6
),
TRANSFORMED_DATA AS (
SELECT OMNI_NUMBER, [TIMESTAMP], DATA_TYPE, DATA_TYPE2 + LEFT(CAST(PARAMETER/1000000. AS varchar(12)),6) + 'M' AS COLUMN_NAME,
PARAMETER, PARAMETER_VALUE
FROM SOURCE_DATA
)
SELECT OMNI_NUMBER, [TIMESTAMP], DATA_TYPE,
MAX([cnr211.25M]) AS [cnr211.25M], MAX([cnr325.25M]) AS [cnr325.25M],
MAX([cnr547.25M]) AS [cnr547.25M], MAX([cnr552.50M]) AS [cnr552.50M]
FROM TRANSFORMED_DATA
PIVOT (MAX(PARAMETER_VALUE) FOR COLUMN_NAME IN ([cnr211.25M], [cnr325.25M], [cnr547.25M], [cnr552.50M])) AS P
GROUP BY OMNI_NUMBER, [TIMESTAMP], DATA_TYPE
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply