CODEWITHSUNDEEP
sqlsql-serverpivotdynamic-sql
jjspierx
jjspierx

Reputation: 440

Dynamic SQL Pivot Causing Duplicate Columns

I created a Dynamic SQL query which joins a few tables, and then pivots off a table called Geometries which stores Name/Value pairs. The SQL is dynamic, because at run-time I won't know which name/value pairs are required to pivot off of.

The query below gets me the data I need, however, all of the geometry columns are listed twice as if I pivoted twice off the same data. I cannot figure out why this happening. I suspect it has to do with the fact that I select the Name/Value pairs in my common table expression which selects the Geometry Name/Value columns on line 13 of the code sample below: g.Name[Geometry], g.Value

Maybe coupling that with the PIVOT code near the bottom is causing the duplicate column data in my query results?

I can't remove the code on line 13, or the PIVOT fails with errors saying Geometry and Value are not valid column names.

Anyway, here is the query...

DECLARE @cols AS NVARCHAR(MAX), @query  AS NVARCHAR(MAX)
SELECT @cols = STUFF((SELECT distinct ',' + QUOTENAME(g.Name) 
                    FROM Geometries g
                    FOR XML PATH(''), TYPE
                    ).value('.', 'NVARCHAR(MAX)') 
                ,1,1,'')
SET @query = 
    'SELECT *, ' + @cols + ' 
    FROM (
            SELECT 
                r.Id[RunId], rd.Id[RunDataId], r.RunNumber [Run #], r.TestNumber [Test #], r.Description,
                rd.data1, rd.data2, rd.data3,
                g.Name[Geometry], g.Value
            FROM dbo.Runs r
                INNER JOIN RunDatas rd ON r.Id = rd.RunId
                INNER JOIN RunGeometries rg ON rg.RunId = r.Id
                INNER JOIN Geometries g ON g.Id = rg.GeometryId
        ) as data
            PIVOT
            (
                Max(Value) FOR Geometry IN (' + @cols + ')
            ) as p'

execute sp_executesql @query

Any help figuring out what is causing the duplicate data would be appreciated.

Upvotes: 0

Views: 1546

Answers (1)

SqlZim
SqlZim

Reputation: 38043

select * is including the columns you are pivoting; try this: 

DECLARE @cols AS NVARCHAR(MAX), @query  AS NVARCHAR(MAX)
SELECT @cols = STUFF((SELECT distinct ',' + QUOTENAME(g.Name) 
                    FROM Geometries g
                    FOR XML PATH(''), TYPE
                    ).value('.', 'NVARCHAR(MAX)') 
                ,1,1,'')
SET @query = 
    'SELECT [RunId], [RunDataId], [Run #], [Test #], Description, data1, data2, data3, ' + @cols + ' 
    FROM (
            SELECT 
                r.Id as [RunId], rd.Id as [RunDataId], r.RunNumber as [Run #], r.TestNumber as [Test #], r.Description,
                rd.data1, rd.data2, rd.data3,
                g.Name[Geometry], g.Value
            FROM dbo.Runs r
                INNER JOIN RunDatas rd ON r.Id = rd.RunId
                INNER JOIN RunGeometries rg ON rg.RunId = r.Id
                INNER JOIN Geometries g ON g.Id = rg.GeometryId
        ) as data
            PIVOT
            (
                Max(Value) FOR Geometry IN (' + @cols + ')
            ) as p'
print @query -- you can also take a look at the code you are generating to help troublshoot
execute sp_executesql @query

You can also check the code that is being generated with print @query or select @query.

Upvotes: 1

Related Questions