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

When you say _return the LienID after the corresponding LienName._ what does that mean? What should the results look like? — jpw, Nov 03 '14 at 21:56
Huh, could you set up a set of sample data on sqlfiddle? The idea I have should work, but would prefer to have a live engine to test on before editing my answer. — Jaaz Cole, Nov 03 '14 at 23:37

Jaaz Cole · Answer 1 · 2014-11-04T15:40:02.823

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

While this does select the Lien, ID it wasn't exactly what I was going for - but that's my fault for not posting a sample of what it should look like, which I am going to do now. — jessica k., Nov 03 '14 at 23:31

score 0 · Accepted Answer · answered Nov 03 '14 at 23:58

0

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

answered Nov 03 '14 at 23:58

jpw

44,361
6
66
86

That's it! That's depressing because now I realize what I need to do to satisfy my client and it makes me want to cry. That being said, I need to take this a step further and some how make it dynamic. The example that I used had 6 rows returned but in reality, it could be more or less. Thanks JPW! – jessica k. Nov 04 '14 at 13:07
@jessicak. It should be easy to make the query dynamic, I can look at it later. – jpw Nov 04 '14 at 18:14
1

I just figured it out, I think. It's probably the ugliest thing I've ever written in my life, but if it saves someone 25 hours a week on the floor, the code can be as ugly as it has to be. – jessica k. Nov 04 '14 at 19:46

SQL Dynamic Pivot without aggregate

2 Answers2