0

I have one table where we are collecting status data from different sensors. For reporting purposes I would need to group information for each sensor (SGUID) to one row. Each sensor is sending reader (RGUID) and value for reader (VAL).

Source table: Source table

Goal result: Goal result

I have heard about pivot's but don't know how to use those in this kind of case.

John Bell
  • 2,350
  • 1
  • 14
  • 23
CodAri
  • 333
  • 1
  • 3
  • 16
  • 1
    Do you know the rguids at develop time? Means: Are you able to hardcode them into a query or do you need to retrieve the values dynamically? – flo Apr 30 '15 at 08:29
  • 3
    This is most likely will be dynamic and will require `Dynamic Pivot`. Probably duplicate for: http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query – Evaldas Buinauskas Apr 30 '15 at 08:30

1 Answers1

2

You need a dynamic pivot using some dynamic T-SQL:

CREATE TABLE #VALS (
    VALS NVARCHAR(4000)
)

INSERT INTO #VALS
SELECT DISTINCT RGUID
FROM [TABLE]

DECLARE @SQL NVARCHAR(MAX)
DECLARE @VALS NVARCHAR(4000)

SELECT @VALS = COALESCE(@VALS+', ','') + '[' + VALS + ']' FROM #VALS

SET @SQL = '
SELECT SGUID, '+@VALS+'
FROM [TABLE]
PIVOT(MAX(VAL) FOR RGUID IN ('+@VALS+')) PIV'
PRINT @SQL
EXEC (@SQL)

The PRINT will show you the constructed query. From there you can debug any issues quite simply. The idea is fairly simple, I've created a working fiddle here.

John Bell
  • 2,350
  • 1
  • 14
  • 23