How to transpose making columns distinct values with multiples lines for each column

Question

I have a table that each line has a customer name and a GUID that is a code for an audience. I want to have a resulta that each column has the GUIDs for each customer.

Original table:

Customer        | Audience
                |
Customer A      | a4e7c9b7-c9d6-4698-8dc8-e0f2920f6bb7
Customer B      | 7a1d31b9-ecec-4c09-9eb9-cb10a8cc7d58
Customer A      | fa89f93d-ad7c-47ee-9a0c-598430c63c43
Customer C      | 0bc45f14-6c1b-48cf-a79e-a7cdc24deecc
Customer A      | 980b90cc-c923-46be-a180-bbbdf43169b2
Customer B      | 29d4806f-70fb-4b5f-9630-7314c32a536a

I need somethin like 'select Customer A, Customer B, Customer C ........ Audience FROM Table'

The result that I need is something like these:

It could work export everything to excel and manipulate there.

Thanks!

check this https://stackoverflow.com/questions/18657214/sql-server-dynamic-pivot-over-5-columns — sultania23, Jul 25 '19 at 12:56
Nom, it has more than 200 customers and almost 20 thousand audiences. — Alexandre Guimenti, Jul 25 '19 at 23:44

score 1 · Answer 1 · answered Jul 25 '19 at 13:50

One option would be a pivot query with the use of ROW_NUMBER to assign arbitrary positions to each GUID, for each customer. We can do this with three separate CTEs, one for each of the three customers in your data set:

WITH cteA AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) rn
    FROM yourTable
    WHERE Customer = 'Customer A'
),
cteB AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) rn
    FROM yourTable
    WHERE Customer = 'Customer B'
),
cteC AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) rn
    FROM yourTable
    WHERE Customer = 'Customer C'
)

SELECT
    a.Audience,
    b.Audience,
    c.Audience
FROM cteA a
FULL OUTER JOIN cteB b
    ON a.rn = b.rn
FULL OUTER JOIN cteC c
    ON a.rn = c.rn AND b.rn = c.rn;

Demo

Is there a way to loop through all the customers without actually typing each one? It has hundreads of customers... — Alexandre Guimenti, Jul 25 '19 at 23:45
The problem is that your expected output is not really relational. I don't know how to do what you want without using a stored procedure. — Tim Biegeleisen, Jul 26 '19 at 00:21

How to transpose making columns distinct values with multiples lines for each column

1 Answers1

Demo