SQl display rows as columns

Question

I need to convert the rows data to be displayed in columns for each user in MS Sql server:

+----------+----------+
| UserName | Products |
+----------+----------+
| User1    | Product1 |
| user1    | Product2 |
| user1    | Product3 |
| user2    | Product4 |
| User2    | Product3 |
| user3    | Product1 |
+----------+----------+

I need the output like below:

+----------+-----------+-----------+-----------+
| UserName |    p1     |    p2     |    p3     |
+----------+-----------+-----------+-----------+
| User1    | Product1  | Product2  | Product3  |
| User2    | Product4  | Product3  |           |
| user3    | Product1  |           |           |
+----------+-----------+-----------+-----------+

also I don't know how to create a serial for columns unknown number of products for each user. How we can achieve this and thanks in advance?

Possible duplicate of [Convert Rows to columns using 'Pivot' in SQL Server](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) — anon609, Sep 04 '19 at 19:37

score 1 · Answer 1 · answered Sep 04 '19 at 19:32

Straight from the docs:

SELECT <non-pivoted column>,  
    [first pivoted column] AS <column name>,  
    [second pivoted column] AS <column name>,  
    ...  
    [last pivoted column] AS <column name>  
FROM  
    (<SELECT query that produces the data>)   
    AS <alias for the source query>  
PIVOT  
(  
    <aggregation function>(<column being aggregated>)  
FOR   
[<column that contains the values that will become column headers>]   
    IN ( [first pivoted column], [second pivoted column],  
    ... [last pivoted column])  
) AS <alias for the pivot table>  
<optional ORDER BY clause>;

I didn't have aggregation operation in my case. can you please apply the pivot function in my example or share the T-sql — Amr, Sep 04 '19 at 19:37

score 1 · Accepted Answer · answered Sep 05 '19 at 17:23

If you are using SQL Server 2017 (or newer) you can aggregate all products for each UserName with string_agg, then you can split the result in columns using a bit of XML syntax.

Basically you build an xml version of your data and then you perform an xpath query to obtain the final result.

Here is an example of this technique:

declare @tmp table (UserName varchar(50), Products varchar(50))

insert into @tmp values
     ('User1','Product1')
    ,('user1','Product2')      
    ,('user1','Product3')
    ,('user2','Product4')
    ,('User2','Product3')
    ,('user3','Product1')

;WITH AggregatedProducts
AS (
    select UserName
    ,CAST('<x>' + REPLACE(string_agg(Products,'_'), '_', '</x><x>') + '</x>' AS XML) AS Segments  
    from @tmp
    group by UserName
    )
SELECT 
     UserName
    ,Segments.value(N'/x[1]', 'varchar(50)') AS p1
    ,Segments.value(N'/x[2]', 'varchar(50)') AS p2
    ,Segments.value(N'/x[3]', 'varchar(50)') AS p3
FROM AggregatedProducts

Output:

Thank You for sharing this. it's working for me however i don't know is there any solution to use pivot function in my scenario. — Amr, Sep 05 '19 at 22:43

Radagast · Answer 3 · 2019-09-06T12:35:25.160

Here is another method that uses string_agg and then parsename function to split '.' delimited strings that are at most 128 characters and have at most 4 parts.

select 
username,
parsename(products,1) as p1,
parsename(products,2) as p2,
parsename(products,3) as p3,
from
(select username, string_agg(products,'.') within group (order by products desc) as products 
from your_table 
group by username) t

SQL Fiddle

SQl display rows as columns

3 Answers3