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

Question

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:

Goal result:

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

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
– flo, Commented Apr 30, 2015 at 8:29
This is most likely will be dynamic and will require Dynamic Pivot. Probably duplicate for: stackoverflow.com/questions/10404348/… — Evaldas Buinauskas
– Evaldas Buinauskas, Commented Apr 30, 2015 at 8:30

John Bell · Accepted Answer · 2015-04-30 10:33:57Z

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.

edited Apr 30, 2015 at 10:33

answered Apr 30, 2015 at 8:33

John Bell

2,3501 gold badge15 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related