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
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
0
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
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
0
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