CODEWITHSUNDEEP
sqlsql-server
LogicalDesk
LogicalDesk

Reputation: 1297

How to Dynamically Pivot a table with join in SQL Server

I have two tables, KeyIndexes and IndexTypes.

enter image description here

enter image description here

For all ImageFileID present in the first table I want the result in single row like below-

enter image description here

Here is the SQL fiddle for above- http://sqlfiddle.com/#!18/1c9a2/2/0

How to get it done using PIVOT or anything in SQL Server?

Upvotes: 0

Views: 52

Answers (2)

LogicalDesk
LogicalDesk

Reputation: 1297

After multiple attempts I created following dynamic pivot query which is working fine for above mentioned problem:

CREATE PROC [dbo].[pGetQAReleaseRejectRec]
@ImageFileID as int=null
AS
BEGIN
DECLARE @cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)    

SELECT @cols = STUFF((SELECT  ',' + QUOTENAME([FieldName]) 
                    FROM KeyIndexes k join 
IndexTypes i on k.IndexTypeID=i.IndexTypeID
where ImageFileID=@ImageFileID
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @query='
select * from
(
  select ImageFileID,k.KeyIndexValue,i.FieldName from KeyIndexes k join 
IndexTypes i on k.IndexTypeID=i.IndexTypeID 
where ImageFileID= '+CONVERT(varchar, @ImageFileID)+'
) s
PIVOT
(
    max(KeyIndexValue)
    FOR [FieldName] IN (' + @cols + ')
)AS pvt'

EXEC SP_EXECUTESQL @query
END

Upvotes: 0

Ross Bush
Ross Bush

Reputation: 15175

You can do this without PIVOT.

SELECT
    ImageFileID,
    PolicyNumber = MAX(CASE WHEN IndexTypeID=1 THEN KeyIndexValue ELSE NULL END)
    ...
FROM
    KeyIndexes
GROUP BY
    ImageFileID

Upvotes: 1

Related Questions