CODEWITHSUNDEEP
sqlsql-server-2008t-sqlpivot
David Redden
David Redden

Reputation: 147

SQL Multiple rows/records into one row with 3 columns

I have a similar table to this dummy data, where there are 3 records for one individual. I would like to change that to one record with multiple columns. What is complicating it for me the most is I want the 3 most current Products based off of Date_Purchased

Existing:

NameFirst            NameLast  MbrKey      Product       DatePurchased
John                 Doe       123456      ProductA      1/1/2015 
John                 Doe       123456      ProductA      2/1/2015 
John                 Doe       123456      ProductB      3/1/2015 
John                 Doe       123456      ProductB      12/1/2015 
Joe                  Smith     987654      ProductA      3/1/2015
Jane                 Jones     555555      ProductA      1/1/2015
Jane                 Jones     555555      ProductB      1/1/2015

This is what I have so far:

select MbrKey, NameLast, NameFirst, 
Case when rn = 1 then Product else null end as Product1,
case when rn = 2 then Product else null end as Product2,
case when rn = 3 then Product else null end as Product3
from
(select t2.*
from(
select t.*, ROW_NUMBER () over (partition by t.MbrKey 
order by t.MbrKey, t.DatePurchased desc) as RN
from testing t) as t2
where t2.RN between 1 and 3) as t3

I think this got me closer as the results are as follows:

NameFirst     NameLast  MbrKey      Product1     Product2     Product3 
Doe          John       123456      ProductB     NULL         NULL
Doe          John       123456      NULL         ProductA     NULL
Doe          John       123456      NULL         NULL         ProductA
Jones        Jane       555555      ProductA     NULL         NULL
Jones        Jane       555555      NULL         ProductB     NULL
Smith        Joe        987654      ProductA     NULL         NULL

Future State: Below is what I am hoping for.

NameFirst     NameLast  MbrKey      Product1     Product2     Product3 
Doe           John      123456      ProductB     ProductB     ProductA
Jones         Jane      555555      ProductA     ProductB     Null
Smith         Joe       987654      ProductA     Null         Null

Any help would be greatly appreciated!

Upvotes: 4

Views: 4465

Answers (3)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

Try PIVOT:

DECLARE @t TABLE
    (
      Name NVARCHAR(MAX) ,
      Product NVARCHAR(MAX) ,
      Date DATE
    )

INSERT  INTO @t
VALUES  ( 'John', 'ProductA', '20150101' ),
        ( 'John', 'ProductA', '20150102' ),
        ( 'John', 'ProductB', '20150103' ),
        ( 'John', 'ProductB', '20150112' ),
        ( 'Joe', 'ProductA', '20150103' ),
        ( 'Jane', 'ProductA', '20150101' ),
        ( 'Jane', 'ProductB', '20150101' );


WITH    cte
          AS ( SELECT   Name ,
                        Product ,
                        ROW_NUMBER() OVER ( PARTITION BY Name ORDER BY Date DESC ) AS RN
               FROM     @t
             )
    SELECT  Name ,
            [1] AS Product1 ,
            [2] AS Product2 ,
            [3] AS Product3
    FROM    cte PIVOT( MAX(Product) FOR rn IN ( [1], [2], [3] ) ) a
    ORDER BY Name

Output:

Name    Product1    Product2    Product3
Jane    ProductA    ProductB    NULL
Joe     ProductA    NULL        NULL
John    ProductB    ProductB    ProductA

Of course you should partition here by MbrKey. I leave it for you.

Upvotes: 1

Brennan Pope
Brennan Pope

Reputation: 1073

Here you go:

DECLARE @Table TABLE
(
    NameFirst VARCHAR(50)
    ,NameLast VARCHAR(50)
    ,MbrKey INT
    ,Product VARCHAR(50)
    ,DatePurchased DATETIME
)

INSERT INTO @Table
VALUES


('John'                 ,'Doe'       ,123456     ,'ProductA'      ,'1/1/2015' )
,('John'                ,'Doe'       ,123456     ,'ProductA'      ,'2/1/2015' )
,('John'                ,'Doe'       ,123456     ,'ProductB'      ,'3/1/2015' )
,('John'                ,'Doe'       ,123456     ,'ProductB'      ,'12/1/2015') 
,('Joe'                 ,'Smith'     ,987654     ,'ProductA'      ,'3/1/2015' )
,('Jane'                ,'Jones'     ,555555     ,'ProductA'      ,'1/1/2015' )
,('Jane'                ,'Jones'     ,555555     ,'ProductB'      ,'1/1/2015' )

SELECT 

    NameFirst 
    ,NameLast 
    ,MbrKey 
    ,MAX(CASE ProductRank WHEN 1 THEN Product END) Product1 
    ,MAX(CASE ProductRank WHEN 2 THEN Product END) Product2
    ,MAX(CASE ProductRank WHEN 3 THEN Product END) Product3

FROM 
(
    SELECT
        MbrKey
        ,MAX(NameFirst) AS NameFirst
        ,MAX(NameLast) AS NameLast
    FROM
        @Table
    GROUP BY MbrKey
) Members
CROSS APPLY
(
    SELECT TOP 3
        Product
        ,ROW_NUMBER() OVER (ORDER BY DatePurchased DESC) AS ProductRank
    FROM @Table T
    WHERE T.MbrKey = Members.MbrKey
    ORDER BY DatePurchased DESC
) Products
GROUP BY 
    Members.MbrKey
    ,Members.NameFirst
    ,Members.NameLast

EDIT: In this case, using cross apply should be better performing than the subquery approach because you only want the most recent three products. This way, the Row_Number() function won't have to act on all records. Also, if you have a members table, you could use that instead of the "Members" subquery in this example.

Upvotes: 1

jpw
jpw

Reputation: 44871

Use the max() aggregate function with the case statements and a group by clause. You can also skip a level of subqueries:

select 
  MbrKey, NameLast, NameFirst, 
  max(Case when rn = 1 then Product else null end) as Product1,
  max(case when rn = 2 then Product else null end) as Product2,
  max(case when rn = 3 then Product else null end) as Product3
from (
  select 
    t.*, 
    rn = ROW_NUMBER () over (partition by t.MbrKey order by t.MbrKey, t.DatePurchased desc)
  from testing t
) as t1 
where t1.RN between 1 and 3
group by MbrKey, NameLast, NameFirst

Upvotes: 2

Related Questions