SQL Dynamic Pivot without aggregate

Question

I have found some thing similar to what I need, but I can't wrap my brain around how to take it where it needs to go.

This helped for sure: simple(?) PIVOT without an aggregate

---

Here is my current query:

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

Which returns the following results:

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

---

I need the commented line (-- LienID) to not be commented out and return the LienID after the corresponding LienName like (I removed the last four columns for formatting purposes).

Lien1            LienID Lien2        LienID    
Deed of Trust    123    Assignment   234       

---

Maybe Pivot isn't the best way to do this, but it's the best that I have found thus far. I have 5 tables that return multiple rows that I need to return all the values on one row. I have been trying to understand the dynamic SQL Pivot questions on here, but I haven't been able to execute one to perform the way I need it to perform.

I am open to any and all suggestions that would help - thanks in advance for your time!

---

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

I wanted to come back and post the work in progress since I've turned this answer into some what of a dynamic query.

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)

---

With the help of: Convert Rows to columns using 'Pivot' in SQL Server

Answer 1

You're nearly there on this. Another copy of the query, pivoted on ID, then joined again by fileID should do the trick. Here's the 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

Answer 2

If I understood your desired output correct I think this query should do it, please give it a try:

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