CODEWITHSUNDEEP
sqlsql-server
James
James

Reputation: 13

Group of 100 user and 100 products sql query

I have a sql table with userid, productid and reviewScore with more than 10 million records. We can imagine userid and productid combination is unique. I need to find matrix of 100 * 100 of userid and product id with their review score.

Sample Input Table - Here the first problem is need to identify common 100 userid and product combination out of 10 million rows for which have reviews and than pivot that to matrix form based .

The productId will form dynamic pivot elements.

Input Table1

UserID  ProductId   ReviewScore
User1   Product1    1
User1   Product2    2
User1   Product3    1
User1   Product4    3
User1   Product5    5
User2   Product1    3
User2   Product2    4
User2   Product3    5
User2   Product4    2
User2   Product5    5
User3   Product1    1
User3   Product2    3
User3   Product3    5
User3   Product4    3
User3   Product5    4
User4   Product1    3
User4   Product2    3
User4   Product3    3
User4   Product4    4
User4   Product5    4
User5   Product1    2
User5   Product2    2
User5   Product3    2
User5   Product4    3
User5   Product5    3

Output which I require.

UserId  Product1    Product2    Product3    Product4    Product5
User1   1            2           1           3             5
User2   3            4           5           2             5
User3   1            3          5            3             4
User4   3            3           3           4             4
User5   2            2           2           3              2

Here the output should be 100* 100 usesrid * product id with review score as values. The idea is to have minimum sparse or 0 values.

I have to write the same query in sql server. Any help is appreciated.

Upvotes: 0

Views: 223

Answers (1)

void
void

Reputation: 7890

use pivote within dynamic sql:

DECLARE @prods AS NVARCHAR(MAX)
DECLARE @query  AS NVARCHAR(MAX)

select @prods = STUFF((SELECT ',' + QUOTENAME(ProductId) 
                    from tab
                    --where limit the products here
                    group by ProductId
                    order by ProductId
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT * from 
             (
                select UserID, ProductId, ReviewScore
                from tab --here also you can limit the products and users by where clause
             ) x
            pivot 
            (
                sum(ReviewScore)
                for ProductId in (' + @prods + ')
            ) p '

EXECUTE(@query)

the Fiddle Demo

Upvotes: 1

Related Questions