如何动态地在SQL Server中使用联接来旋转表

I have two tables, KeyIndexes and IndexTypes.

我有两个表,KeyIndexes和IndexTypes。

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

对于第一个表中存在的所有ImageFileID,我希望结果在单行中,如下所示 -

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

这是上面的SQL小提琴 - http://sqlfiddle.com/#!18/1c9a2/2/0

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

如何使用PIVOT或SQL Server中的任何东西完成它?

2 个解决方案

#1

You can do this without PIVOT.

没有PIVOT你可以做到这一点。

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

#2

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

#1