CODEWITHSUNDEEP
sqlsql-server-2012pivot
data addicted
data addicted

Reputation: 69

First time pivot SQL Server 2012

I needed help with pivoting a table. This is my first attempt.

I have extracted data into a #temp table. I have two columns, in my #temp table, Customer Nr and product

One customer number can have more than one 1 product. How do I show, for one customer number, up to 3 products?

This is my very first attempt:

SELECT DISTINCT
    cust_no,
    products
FROM 
    #temp
PIVOT 
    (cust_no, 
     CASE WHEN [Product 1] IS NULL THEN NULL ELSE [Product 2] END AS [Product 2],
     CASE WHEN [Product 2] IS NULL THEN NULL ELSE [Product 3] END AS [Product 3])

I got stuck here. I am working in SQL Server 2012

Upvotes: 1

Views: 50

Answers (1)

Taryn
Taryn

Reputation: 247850

You don't really need to use the PIVOT function to get the result. You could use an aggregate function with a CASE expression, along with a windowing function like row_number() to convert the data from rows into columns. The windowing function will generate a unique number for each product within a customer. This number is then used to place the value into each of the columns.

Here is some sample data:

CREATE TABLE temp
    ([CustomerNr] int, [Product] varchar(9));

INSERT INTO temp
    ([CustomerNr], [Product])
VALUES
    (1234, 'Rice'),
    (1234, 'Tuna'),
    (1234, 'Sauce'),
    (2345, 'Jam'),
    (3456, 'Spaghetti'),
    (3456, 'Jelly');

Using an aggregate function and a CASE expression your code would be:

select CustomerNr,
  Product1 = max(case when rn = 1 then Product else null end),
  Product2 = max(case when rn = 2 then Product else null end),
  Product3 = max(case when rn = 3 then Product else null end)
from
(
  select CustomerNr, Product,
    rn = row_number() over(partition by customerNr order by product)
  from temp
) d
group by CustomerNr;

See SQL Fiddle with Demo.

But if you wanted to use the PIVOT function you can you'll just still need to use a windowing function to help determine the number of products for each Customer:

select CustomerNr, Product1, Product2, Product3
from
(
  select CustomerNr, Product,
    new_col = 'Product'
                +cast(row_number() over(partition by customerNr order by product) as varchar(1))
  from temp
) d
pivot
(
  max(product) 
  for new_col in (Product1, Product2, Product3)
) piv;

See SQL Fiddle with Demo. Either version will give a result:

| CustomerNr | Product1 |  Product2 | Product3 |
|------------|----------|-----------|----------|
|       1234 |     Rice |     Sauce |     Tuna |
|       2345 |      Jam |    (null) |   (null) |
|       3456 |    Jelly | Spaghetti |   (null) |

Upvotes: 2

Related Questions