CODEWITHSUNDEEP
sqlsql-serverpivotpivot-table
CodAri
CodAri

Reputation: 353

How to select SQL Server row values as columns (Dynamic Pivot)

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.

Upvotes: 0

Views: 548

Answers (1)

John Bell
John Bell

Reputation: 2350

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.

Upvotes: 2

Related Questions