0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for people organizing, marketing or licensing open source development projects

CREATE TABLE #samples (SampleID int, Time datetime, RawData image)
CREATE TABLE #parameters (ParamID int, SampleID int, ParamType uniqueidentifier, Value float)

DECLARE @paramType1 uniqueidentifier = newid()
DECLARE @paramType2 uniqueidentifier = newid()
DECLARE @paramType3 uniqueidentifier = newid()

INSERT INTO #samples VALUES (1, GETDATE(), null), (2, GETDATE(), null), (3, GETDATE(), null), (4, GETDATE(), null)
INSERT INTO #parameters VALUES 
(1, 1, @paramType1, 100),
(2, 1, @paramType2, 101),
(3, 1, @paramType3, 102),
(4, 2, @paramType1, 103),
(5, 2, @paramType2, 104), 
(6, 2, @paramType3, 105),
(7, 3, @paramType1, 200)

DECLARE @columns VARCHAR(MAX)

SELECT @columns = COALESCE(@columns + ',[' + CAST(p.ParamType as varchar(50)) + ']'
                           ,'[' + CAST(p.ParamType as varchar(50)) + ']')
FROM 
(
  SELECT DISTINCT ParamType FROM #parameters
) as p

DECLARE @query VARCHAR(MAX)
SET @query = '
    SELECT * FROM
    (
        SELECT SampleID, ParamType, Value FROM #parameters
    ) as tbl3
    PIVOT
    (
        AVG(Value) FOR ParamType in (' + @columns + ')
    ) AS tblResults'

EXECUTE (@query)

DROP TABLE #samples
DROP TABLE #parameters

fork query

created dec 6 11 kevev22