Grouping Fields

Question

i want to get the desired query in Sql using following tables

usertable
id name
1 Vikram
2 vik
3 ram

ProductTable
id   userid   productdetails      Active
1    1            XXXX                   Yes
2    1            YYYY                   NO
3    3            AAAA                   Yes
4    3            BBBB                  Yes
5    1            CCCC                 Yes

Now i want the result something like
Products which are active grouped by user paged by user

page 1
user 1
    Product 1
    product 2
    product 5
user 3
    Product 3

i am getting like
user 1      Product 1
User 1      Product 2
user 1      Product 5
user 3      Product 3

but i want something like this in single Query
user 1 Product1 Product2 Product 5
user 3 product3

Any Help will be appreciated thanks & Regards Vikram

this is not an exact query, my actual table is very big and also contains lots of joining — Vikram Sachdeva, May 02 '16 at 09:27
Do you want the products for each user as comma separated values or as separate columns? — 1000111, May 02 '16 at 09:27
Separated Columns, but there is no limit on the product number — Vikram Sachdeva, May 02 '16 at 09:28
You can check [**this**](http://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns) — 1000111, May 02 '16 at 09:31

score 0 · Answer 1 · edited May 23 '17 at 10:28

    SELECT 
      ev.userid
    , Product = STUFF(CAST((
        SELECT [text()] = ', ' + ev2.productdetails
        FROM #ProductTable ev2
        WHERE ev2.userid = ev.userid
        FOR XML PATH(''), TYPE) AS VARCHAR(100)), 1, 2, '')            
FROM ( 
    SELECT DISTINCT userid
    FROM #ProductTable
) ev

output:

    userid  Product
    1       XXXX, YYYY, CCCC
    3       AAAA, BBBB

String Aggregation in the World of SQL Server

Simulating group_concat MySQL function in SQL Server?

score -1 · Answer 2 · answered May 02 '16 at 09:35

SELECT User, ConcatProduct = 
(
    SELECT [text()] = A.Product + ' '
    FROM TableName A
    WHERE A.User = B.User 
    FOR XML PATH('')
)
FROM TableName B
GROUP BY User

After u get data like following run upper query.

user1      Product 1
user1      Product 2
user1      Product 5
user3      Product 3

Query is written assuming your first column name User and second column name Product. Output will be as following:

user1       Product 1  Product 2  Product 5
user3       Product 3

Grouping Fields

2 Answers2