SQL Dynamic Pivot без агрегата

Я нашел кое-что похожее на то, что мне нужно, но я не могу обернуть мозг вокруг того, как взять его там, где ему нужно идти.

Это помогло точно: простой (?) PIVOT без агрегата

Вот мой текущий запрос:

SELECT [1] AS Lien1 ,[2] AS Lien2 ,[3] AS Lien3 ,[4] AS Lien4 ,[5] AS Lien5 ,[6] AS Lien6
FROM ( SELECT lt.Name AS [LienName] -- ,LienID ,ROW_NUMBER() OVER ( ORDER BY LienID ) AS RN FROM dbo.Lien AS L INNER JOIN dbo.LienType AS LT ON LT.LienTypeID = L.LienTypeID WHERE FileID = 528267 ) AS PivotSource PIVOT
( MAX(LienName) FOR RN IN ( [1], [2], [3], [4], [5], [6] ) ) piv

Что возвращает следующие результаты:

Lien1 Lien2 Lien3 Lien4 Lien5 Lien6
Deed of Trust Assignment Appointment of Substitute Trustee Assignment Assignment Civil Foreclosure Case

Мне нужна прокомментированная строка (- LienID), чтобы не прокомментировать и вернуть LienID после соответствующего LienName (я удалил последние четыре столбца для целей форматирования).

Lien1 LienID Lien2 LienID
Deed of Trust 123 Assignment 234

Возможно, Pivot - это не лучший способ сделать это, но это лучшее, что я нашел до сих пор. У меня есть 5 таблиц, которые возвращают несколько строк, которые мне нужны, чтобы вернуть все значения в одной строке. Я пытался понять динамические вопросы SQL Pivot здесь, но я не смог выполнить его, чтобы выполнить то, что мне нужно для его выполнения.

Я открыт для любых предложений, которые помогут - заблаговременно за ваше время!

EDIT: 11/4/14 @10: 47AM

Я хотел вернуться и опубликовать текущую работу, так как я включил этот ответ в какой-то динамический запрос.

DECLARE @FileID INT = 528267
DECLARE @Cols NVARCHAR(MAX)
DECLARE @query NVARCHAR(MAX)
SELECT @cols = STUFF((SELECT ',' + QUOTENAME('LienNumber' + CONVERT(VARCHAR, ROW_NUMBER() OVER ( ORDER BY SortOrder ))) FROM dbo.Lien AS L INNER JOIN dbo.LienType AS LT ON LT.LienTypeID = L.LienTypeID INNER JOIN dbo.FileActions AS FA ON fa.FileID = l.FileID AND fa.ActionDefID = 1184 AND fa.SentDate IS NOT NULL AND fa.ReceivedDate IS NULL AND fa.FileID = @FileID FOR XML PATH('') ,TYPE ).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
SET @Query = 'DECLARE @FileID INT = 528267 SELECT ' + @cols + ' FROM (select lt.name as Name, ''LienNumber'' + CONVERT(VARCHAR, ROW_NUMBER() OVER ( ORDER BY SortOrder )) as RN FROM dbo.Lien AS L INNER JOIN dbo.LienType AS LT ON LT.LienTypeID = L.LienTypeID INNER JOIN dbo.FileActions AS FA ON fa.FileID = l.FileID AND fa.ActionDefID = 1184 AND fa.SentDate IS NOT NULL AND fa.ReceivedDate IS NULL AND fa.FileID = @FileID ) x pivot ( MAX(name) FOR RN IN ( ' + @Cols + ' ) ) p'
EXEC (@query)

С помощью: Преобразование строк в столбцы с использованием "Pivot" в SQL Server

2 ответа

Если я правильно понял ваш желаемый результат, я думаю, что этот запрос должен это сделать, пожалуйста, попробуйте:

SELECT Lien1 = MAX([1]), LienID = MAX([r1]), Lien2 = MAX([2]), LienID = MAX([r2]), Lien3 = MAX([3]), LienID = MAX([r3]), Lien4 = MAX([4]), LienID = MAX([r4]), Lien5 = MAX([5]), LienID = MAX([r5]), Lien6 = MAX([6]), LienID = MAX([r6])
FROM ( SELECT lt.Name AS [LienName] ,LienID ,ROW_NUMBER() OVER ( ORDER BY LienID ) AS RN1 ,'r'+CAST(ROW_NUMBER() OVER ( ORDER BY LienID ) AS varchar(10)) AS RN2 FROM Lien AS L INNER JOIN LienType AS LT ON LT.LienTypeID = L.LienTypeID WHERE FileID = 528267 ) AS PivotSource
PIVOT ( MAX(LienName) FOR RN1 IN ( [1], [2], [3], [4], [5], [6] ) ) as names
PIVOT ( MAX(LienID) FOR RN2 IN ( [r1], [r2], [r3], [r4],[r5],[r6] ) ) as ids


Вы почти на этом. Другая копия запроса, повернутого по идентификатору, затем снова связанная с fileID, должна сделать трюк. Здесь SQLFiddle.

SELECT a.FileID ,a.Lien1 ,b.LienID1 ,a.Lien2 ,b.LienID2 ,a.Lien3 ,b.LienID3 ,a.Lien4 ,b.LienID4 ,a.Lien5 ,b.LienID5 ,a.Lien6 ,b.LienID6
FROM ( SELECT FileID ,[1] AS Lien1 ,[2] AS Lien2 ,[3] AS Lien3 ,[4] AS Lien4 ,[5] AS Lien5 ,[6] AS Lien6 FROM ( SELECT FileID ,LienName ,ROW_NUMBER() OVER ( ORDER BY LienID ) AS RN FROM dbo.Lien AS L WHERE FileID = 528267 ) AS PivotSource PIVOT ( MAX(LienName) FOR RN IN ( [1], [2], [3], [4], [5], [6] ) ) piv1 ) a INNER JOIN ( SELECT FileID ,[1] AS LienID1 ,[2] AS LienID2 ,[3] AS LienID3 ,[4] AS LienID4 ,[5] AS LienID5 ,[6] AS LienID6 FROM ( SELECT FileID ,LienID ,ROW_NUMBER() OVER ( ORDER BY LienID ) AS RN FROM dbo.Lien AS L WHERE FileID = 528267 ) AS PivotSource PIVOT ( MAX(LienID) FOR RN IN ( [1], [2], [3], [4], [5], [6] ) ) piv1 ) b ON a.FileID = b.FileID

licensed under cc by-sa 3.0 with attribution.