CODEWITHSUNDEEP
sqlsql-server
cmccarra
cmccarra

Reputation: 199

Combine values from multiple rows into one row with each value in it's own column

I have a number of sites were customers can register and I want to combine in one row all the customer IDs associated with an email address across all sites. I am working in SQL Sever.

Example source data:

enter image description here

What I would like to achieve in a new table:

enter image description here

My first thought was something like this, with email as primary key but it doesn't work. Could I use a CASE expression?

SELECT c.[Email],
       (SELECT c.[CustomerID] FROM Customer c WHERE c.[BrandID] = '1859') AS [Brand1],
       (SELECT c.[CustomerID] FROM Customer c WHERE c.[BrandID] = '1594') AS [Brand2],
       (SELECT c.[CustomerID] FROM Customer c WHERE c.[BrandID] = '4578') AS [Brand3]
  FROM Customer c

Upvotes: 1

Views: 1237

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Just use conditional aggregation:

SELECT c.Email,
       MAX(CASE WHEN BrandID = 1859 THEN c.[CustomerID] END) as Brand1,
       MAX(CASE WHEN BrandID = 1594 THEN c.[CustomerID] END) as Brand2,
       MAX(CASE WHEN BrandID = 4578 THEN c.[CustomerID] END) as Brand3
FROM Customer c
GROUP BY c.Email;

BrandID looks like a number so I removed the single quotes around the comparison value. Of course, use single quotes if it is really a string.

Upvotes: 3

DarkRob
DarkRob

Reputation: 3833

You may use pivot for same.

 ; with cte as (
 select row_number() over (partition by email order by brandid) as Slno,
 Email, brandid from table)
  , ct as (
  select brandid, 'brand' + cast(slno as varchar(5)) as Col, email from cte )
  select * from (
  select brandid, col, email from ct ) as d
  pivot (
  max(brandid) for col in ( [brand1], [brand2], [brand3] )
  ) as p

Upvotes: 0

Red Devil
Red Devil

Reputation: 2393

You can try case like this:

SELECT c.Email,
   SUM(CASE WHEN BrandID = 1859 THEN CustomerID ELSE 0 END) as Brand1,
   SUM(CASE WHEN BrandID = 1594 THEN CustomerID ELSE 0 END) as Brand2,
   SUM(CASE WHEN BrandID = 4578 THEN CustomerID ELSE 0 END) as Brand3
FROM Customer c
GROUP BY c.Email;

Upvotes: 0

Related Questions