T-SQL How to "Flatten" top 3 rows into a single row

Question

I've searched for an answer to this question and found questions similar to my own, however I do not have a "ColumnHeader" column to denote which field the record should go into. Ex:

TSQL Pivot without aggregate function

trying to flatten rows into columns

Fetching Columns of a multiple rows in one row

My problem is thus - I have data in this format (selected as a top 3 result from a product recommendation query):

 ------------------------------
 CustID | StyleNo | Brand | ID 
 ------------------------------
    1   |   ABC   | BrandA| 1 
 ------------------------------
    1   |   DEF   | BrandB| 2 
 ------------------------------
    1   |   GHI   | BrandC| 3 
 ------------------------------
    2   |   JKL   | BrandA| 4 
 ------------------------------
    2   |   MNO   | BrandB| 5 
 ------------------------------
    2   |   PQR   | BrandD| 6 
 ------------------------------

That I'd like to make look like this:

 -----------------------------------------------------------------
 CustID | StyleNo1| StyleNo2| StyleNo3 | Brand1 | Brand2 | Brand3
 -----------------------------------------------------------------
    1   |   ABC   |   DEF   |   GHI    | BrandA | BrandB | BrandC 
 -----------------------------------------------------------------
    2   |   JKL   |   MNO   |   PQR    | BrandA | BrandB | BrandD
 -----------------------------------------------------------------

In order for my program to simply read the row of recommendations for each customer.

What I have attempted is a PIVOT - however I have nothing to really aggregate upon. I've also attempted the Min(Case...When...Then...End) as outlined in the second linked question, but as stated I don't have reference to a "Header" column.

The ID column is completely inconsequential for the time being, but it may help to solve this problem. It is NOT needed in the end result.

I am currently using SQLServer 2012

try stuff https://msdn.microsoft.com/en-us/library/ms188043.aspx — happymacarts, Nov 22 '16 at 20:24
@happymacarts STUFF seems to enable me to parse the data into a comma separated list of values, which I'd have to decode on the front-end. Is there a simpler way of just providing tabular data in the format I've described? Please let me know if I don't quite understand your solution. — Tramel Jones, Nov 22 '16 at 20:31
maybe try a group by or look at grouping sets i think i misunderstood your oP — happymacarts, Nov 22 '16 at 20:34
is the target table structure fixed? you always have 3 records to flat, or up to 3, or it could be any number. — Dance-Henry, Nov 22 '16 at 20:34
The target table has up to 3 recommendations, my select statement will check to see if the 2nd or 3rd recommendation is NULL before using the data. — Tramel Jones, Nov 22 '16 at 20:55

John Cappelletti · Accepted Answer · 2016-11-22T20:47:07.583

With the window function Row_Number() and a conditional aggregation

 Select CustID
       ,StyleNo1 = max(case when RN=1 then StyleNo else null end)
       ,StyleNo2 = max(case when RN=2 then StyleNo else null end)
       ,StyleNo3 = max(case when RN=3 then StyleNo else null end)
       ,Brand1   = max(case when RN=1 then Brand   else null end)
       ,Brand2   = max(case when RN=2 then Brand   else null end)
       ,Brand3   = max(case when RN=3 then Brand   else null end)
 From  (
         Select *,RN = Row_Number() over (Partition By CustID Order by StyleNo,Brand)
         From  YourTable
       ) A
 Where RN<=3
 Group By CustID

Returns

@TramelJones Happy to help – John Cappelletti Nov 22 '16 at 21:05 — John Cappelletti, Nov 22 '16 at 21:05

score 2 · Answer 2 · answered Nov 22 '16 at 20:56

What you are doing is called "pivoting" - for this you could use PIVOT. A better way IMHO is to use approach that Jeff Moden talks about in this article.

WITH idSort AS
(
  SELECT *, rn = ROW_NUMBER() OVER (PARTITION BY CustID ORDER BY ID) FROM @yourTable
)
SELECT
  CustID,
  StyleNo1 = MAX(CASE rn WHEN 1 THEN StyleNo END),
  StyleNo2 = MAX(CASE rn WHEN 2 THEN StyleNo END),
  StyleNo3 = MAX(CASE rn WHEN 3 THEN StyleNo END),
  Brand1   = MAX(CASE rn WHEN 1 THEN Brand   END),
  Brand2   = MAX(CASE rn WHEN 2 THEN Brand   END),
  Brand3   = MAX(CASE rn WHEN 3 THEN Brand   END)
FROM idSort
GROUP BY CustID;

Thanks for the article! Good tip – Tramel Jones Nov 22 '16 at 21:04 — Tramel Jones, Nov 22 '16 at 21:04

score 0 · Answer 3 · answered Nov 22 '16 at 21:36

Other approach can be using CTE's and Cross Apply.

CREATE TABLE #UnFlattenedData
    (
      CustID TINYINT ,
      StyleNo CHAR(3) ,
      Brand CHAR(6) ,
      ID TINYINT
    );

INSERT  INTO #UnFlattenedData
        ( CustID, StyleNo, Brand, ID )
VALUES  ( 1, -- CustID - tinyint
          'ABC', -- StyleNo - char(3)
          'BrandA', -- Brand - char(6)
          1  -- ID - tinyint
          ),
        ( 1, -- CustID - tinyint
          'DEF', -- StyleNo - char(3)
          'BrandB', -- Brand - char(6)
          2  -- ID - tinyint
          ),
        ( 1, -- CustID - tinyint
          'GHI', -- StyleNo - char(3)
          'BrandC', -- Brand - char(6)
          3  -- ID - tinyint
          ),
        ( 2, -- CustID - tinyint
          'JKL', -- StyleNo - char(3)
          'BrandA', -- Brand - char(6)
          4  -- ID - tinyint
          ),
        ( 2, -- CustID - tinyint
          'MNO', -- StyleNo - char(3)
          'BrandB', -- Brand - char(6)
          5  -- ID - tinyint
          ),
        ( 2, -- CustID - tinyint
          'PQR', -- StyleNo - char(3)
          'BrandD', -- Brand - char(6)
          6  -- ID - tinyint
          );
WITH    cte
          AS ( SELECT   * ,
                        ROW_NUMBER() OVER ( PARTITION BY u1.CustID ORDER BY u1.ID ) AS R1
               FROM     #UnFlattenedData AS u1
             ),
        u1
          AS ( SELECT   C1.CustID ,
                        U1.StyleNo ,
                        U1.Brand
               FROM     cte AS C1
                        INNER JOIN #UnFlattenedData AS U1 ON U1.CustID = C1.CustID
                                                             AND U1.ID = C1.ID
               WHERE    C1.R1 = 1
             ),
        u2
          AS ( SELECT   C1.CustID ,
                        U1.StyleNo ,
                        U1.Brand
               FROM     cte AS C1
                        INNER JOIN #UnFlattenedData AS U1 ON U1.CustID = C1.CustID
                                                             AND U1.ID = C1.ID
               WHERE    C1.R1 = 2
             ),
        u3
          AS ( SELECT   C1.CustID ,
                        U1.StyleNo ,
                        U1.Brand
               FROM     cte AS C1
                        INNER JOIN #UnFlattenedData AS U1 ON U1.CustID = C1.CustID
                                                             AND U1.ID = C1.ID
               WHERE    C1.R1 = 3
             )
    SELECT  u1.CustID ,
            u1.StyleNo AS StyleNo1 ,
            u2.StyleNo AS StyleNo2 ,
            u3.StyleNo AS StyleNo3 ,
            u1.Brand AS Brand1 ,
            u2.Brand AS Brand2 ,
            u3.Brand AS Brand3
    FROM    u1
            CROSS APPLY ( SELECT    *
                          FROM      u2
                          WHERE     u2.CustID = u1.CustID
                        ) AS u2
            CROSS APPLY ( SELECT    *
                          FROM      u3
                          WHERE     u3.CustID = u1.CustID
                        ) AS u3;

Does using a CTE with `CROSS APPLY` have any performance benefit over the cross tab? (accepted answer) — Tramel Jones, Nov 22 '16 at 21:40

T-SQL How to "Flatten" top 3 rows into a single row

3 Answers3