CODEWITHSUNDEEP
sql
joerdie
joerdie

Reputation: 379

Using SQL pivot in case with only one duplicated data field

I have a table that looks something like this. 

owner     owner_email     customer
dude1     email1          customer1
dude1     email1          customer2
dude1     email1          customer3
dude2     email2          customer4
dude2     email2          customer5

etc...

I am looking for the following 

owner     owner_email     customer1     customer2     customer3 etc

Using the pivots examples on this site, I am starting to see that I would need something else to group by and I do not have that. Is using pivot the right solution here? I have the following from using another example. 

SELECT * FROM 
(
SELECT [owner], [owner_email], [customer]
FROM tTableName
) AS source
PIVOT
(
    MAX([customer])
    FOR [customer] IN ([name1][name2][name3][name4][name5])
) as pvt

Thanks.

Upvotes: 0

Views: 85

Answers (1)

Taryn
Taryn

Reputation: 247860

In order to do this you will want to look at adding the row_number() to your inner query. I am using the row_number along with name to create the columns name1, name2, etc. Then when you apply the PIVOT function you will return the customer for each owner in separate columns:

SELECT * 
FROM 
(
  SELECT [owner], [owner_email], [customer],
    'Name'+cast(row_number() over(partition by owner
                                  order by customer) as varchar(50)) name
  FROM tTableName
) AS source
PIVOT
(
    MAX([customer])
    FOR [name] IN ([name1],[name2],[name3],[name4],[name5])
) as pvt;

See SQL Fiddle with Demo.

If you are going to have more than 5 names for each owner, then you can use dynamic SQL:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' 
                        + QUOTENAME('Name'+cast(row_number() over(partition by owner
                                                order by customer) as varchar(50))) 
                    from tTableName
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT  [owner], [owner_email],' + @cols + ' 
            from 
            (
              SELECT [owner], [owner_email], [customer],
                ''Name''+cast(row_number() over(partition by owner
                                              order by customer) as varchar(50)) name
              FROM tTableName
            ) x
            pivot 
            (
                max(customer)
                for name in (' + @cols + ')
            ) p '

execute(@query);

See SQL Fiddle with Demo. These create a result:

| OWNER | OWNER_EMAIL |     NAME1 |     NAME2 |     NAME3 |  NAME4 |  NAME5 |
-----------------------------------------------------------------------------
| dude1 |      email1 | customer1 | customer2 | customer3 | (null) | (null) |
| dude2 |      email2 | customer4 | customer5 |    (null) | (null) | (null) |

Upvotes: 3

Related Questions