Tramel Jones Tramel Jones - 18 days ago 5
SQL Question

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

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

Answer

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

enter image description here