How to pivot? How to convert multiple rows into one row with multiple columns?

user_2218856 0
Viewed 21943

I have two tables which I want to combine. The first table is with clients and the other with products. Currently I have 22 products, but I want to have a flexible DB design so instead of having 22 columns in the product DB, I have 1 row for each product for each client so if I add or remove 1 product overall, I don't have to change the DB structure.

I want to have a select statement where I select all products for each client and the output should be in a single row with a column for each product.

I have seen some other questions which are similar, but there the aim is to have all the rows concatenated in 1 column- which I don't want.

Assuming 2 clients and 3 products.

Table client:

ClientId | ClientName
---------------------
 1       | Name1
 2       | Name2

Table products

ProductId | ClientId | Product
-------------------------------------
 1        |   1      |  SomeproductA
 2        |   1      |  SomeproductB
 3        |   1      |  SomeproductA
 4        |   2      |  SomeproductC
 5        |   2      |  SomeproductD
 6        |   2      |  SomeproductA

The output should be something like:

Table output:

 ClientId | ClientName | Product1     | Product 2    | Product 3
 -------------------------------------------------------------------
     1    | Name1      | SomeproductA | SomeproductB | SomeproductA
     2    | Name2      | SomeproductC | SomeproductD | SomeproductA

The perfect solution would also be flexible in the sense that the select statement should count the number of distinct products for each client (they will always be the same for all clients), such that if I add or remove 1 product for all clients, I should not change the select statement.

pivot-table sql-server mysql sql
2 Answers

The answers seem to address both MySQL and SQL Server, so I am adding a further SQL Server Answer here and the logic might work in MySQL too.

Below is a dynamic SQL version in Transact SQL for MS SQL Server.

This enables you to get the same result without having to explicitly write out every column you need in the resultant table as the CASE WHEN solution. The CASE WHEN is nice and simple for a few columns, but I recently had a similar scenario that pivoted to around 200 columns.

For dynamic SQL you essentially compile the query that you want as a string using generated variables and then execute it.

-- variable tables to store data
DECLARE @Clients TABLE(ClientID int, 
                ClientName nvarchar(10))

DECLARE @Products TABLE(ProductID int, 
                    ClientID int, 
                    Product nvarchar(15))

-- populate the variable tables with sample data
INSERT INTO @Clients 
VALUES (1, 'Name1'),
    (2, 'Name2')

INSERT INTO @Products 
VALUES (1, 1, 'SomeproductA'),
    (2, 1, 'SomeproductB'),
    (3, 1, 'SomeproductA'),
    (4, 2, 'SomeproductC'),
    (5, 2, 'SomeproductD'),
    (6, 2, 'SomeproductA')

-- display the tables to check
SELECT * FROM @Clients
SELECT * FROM @Products

-- join the two tables and generate a column with rows which will become the new 
-- column names (Product_col) which gives a number to each product per client
SELECT c.ClientID, 
    c.ClientName, 
    p.ProductID, 
    p.Product,
    CONCAT('Product', ROW_NUMBER() 
        OVER(PARTITION BY c.ClientID ORDER BY p.Product ASC))  AS Product_col
INTO #Client_Products
FROM @Products p 
LEFT JOIN @Clients c ON c.ClientID = p.ClientID

-- view the joined data and future column headings
SELECT * FROM #Client_Products

-- setup for the pivot, declare the variables to contain the column names for pivoted 
-- rows and the query string
DECLARE @cols1 AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

-- column name list for products
SET @cols1 = STUFF((SELECT distinct ',' + QUOTENAME(Product_col) 
        FROM #Client_Products
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

SELECT @cols1  -- view the future column names

-- generate query variable string

-- the top select is all the columns you want to actually see as the result
-- The inner query needs the columns you want to see in the result, and the columns 
-- you are pivoting with. The pivot needs to select the value you want to go into the 
-- new columns (MAX()) and the values that will become the column names (FOR x IN())
SET @query = 'SELECT ClientID, 
            ClientName,'
                + @cols1 +' 
            FROM
            (
                SELECT ClientID,
                    ClientName,
                    Product_col,
                    Product
                FROM #Client_Products
           ) x
         PIVOT 
        (
            MAX(Product)
            FOR Product_col IN (' + @cols1 + ')
        ) p'


EXECUTE(@query) -- execute the dynamic sql

DROP TABLE #Client_Products
user_140855990
Related