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.