Tramel Jones Tramel Jones - 1 year ago 66
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
- however I have nothing to really aggregate upon. I've also attempted the
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 Source

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


enter image description here

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download