How to PIVOT rows value to columns in SQL Server

Question

How can I pivot or somehow turn row values from ProductType to be Name of the new columns and calculate Amount for each product type grouped by TimeId

Here is my table:

TimeId  ProductType NetIncomeByProduct
---------------------------------------
2017121500  Casino  0.000000
2017121501  Casino  0.000000
2017121502  Casino  4.333282
2017121503  Casino  0.000000
2017121504  Casino  0.000000
2017121505  Casino  0.000000
2017121506  Casino  -0.308172
2017121507  Casino  -0.154086
2017121508  Casino  0.000000
2017121509  Casino  0.000000
2017121510  Casino  0.000000
2017121511  Casino  -0.308172
2017121512  Casino  0.000000
2017121513  Casino  0.000000
2017121514  Casino  0.000000
2017121515  Casino  0.000000
2017121516  Casino  0.000000
2017121517  Casino  0.000000
2017121518  Casino  0.000000
2017121519  Casino  0.000000
2017121520  Casino  0.000000
2017121521  Casino  0.000000
2017121522  Casino  0.000000
2017121523  Casino  0.000000
2017121523  Live Dealer 0.000000
2017121522  Live Dealer 0.000000
2017121521  Live Dealer 0.000000
2017121520  Live Dealer 0.000000
2017121519  Live Dealer 0.000000
2017121518  Live Dealer 0.000000
2017121517  Live Dealer 0.000000
2017121516  Live Dealer 0.000000
2017121515  Live Dealer 0.000000
2017121514  Live Dealer 0.000000
2017121513  Live Dealer 0.000000
2017121512  Live Dealer 0.000000
2017121511  Live Dealer -0.308172
2017121510  Live Dealer 0.000000
2017121509  Live Dealer 0.000000
2017121508  Live Dealer 0.000000
2017121507  Live Dealer -0.154086
2017121506  Live Dealer -0.308172
2017121505  Live Dealer 0.000000
2017121504  Live Dealer 0.000000
2017121503  Live Dealer 0.000000
2017121502  Live Dealer 4.333282
2017121501  Live Dealer 0.000000
2017121500  Live Dealer 0.000000

So what i want is to have for example TimeId Casino Livedealer in header, but i won't know in front how many product types i will have and for each product type to have calculated the NetIncomeByProduct.

One example will be:

TimeId       Casino      Livedealer 
----------------------------------
2017121502   4.333282    0.0000

Google sql server dynamic pivot , there is tons of information on this on the internet — M.Ali
– M.Ali, Commented Jan 27, 2018 at 17:00
Thank you , i found the right answer on suggested page. Have a nice day — user2171512
– user2171512, Commented Jan 27, 2018 at 17:20
How about providing an answer to your own question so that (1) others can benefit from what you have learned, and (2) others don't come in here thinking it is an unanswered questions (thus wasting their time). — Alan
– Alan, Commented Jan 27, 2018 at 22:45

user2171512 · Accepted Answer · 2018-01-28 11:33:32Z

So the right answer to my question was:

DECLARE @cols AS NVARCHAR(MAX),
                    @query  AS NVARCHAR(MAX);

                SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(t.ProductType) 
                            FROM #TempTableNetIncomeByProduct t
                            FOR XML PATH(''), TYPE
                            ).value('.', 'NVARCHAR(MAX)') 
                        ,1,1,'')

                set @query = 'SELECT TimeId, ' + @cols + ' from 
                            (
                                select TimeId
                                    , NetIncomeByProduct
                                    , ProductType
                                from #TempTableNetIncomeByProduct
                           ) x
                            pivot 
                            (
                                 max(NetIncomeByProduct)
                                for ProductType in (' + @cols + ')
                            ) p 
                            ORDER BY TimeId'


                execute(@query)

And the result from my table is:

TimeId  Casino  Live Dealer
2017121500  0.000000    0.000000
2017121501  0.000000    0.000000
2017121502  4.333282    -0.154086
2017121503  0.000000    0.000000
2017121504  0.000000    0.000000
2017121505  0.000000    0.000000
2017121506  0.000000    -0.308172
2017121507  0.000000    -0.154086
2017121508  0.000000    0.000000
2017121509  0.000000    0.000000
2017121510  0.000000    0.000000
2017121511  0.000000    -0.308172
2017121512  0.000000    0.000000
2017121513  0.000000    0.000000
2017121514  0.000000    0.000000
2017121515  0.000000    0.000000
2017121516  0.000000    0.000000
2017121517  0.000000    0.000000
2017121518  0.000000    0.000000
2017121519  0.000000    0.000000
2017121520  0.000000    0.000000
2017121521  0.000000    0.000000
2017121522  0.000000    0.000000
2017121523  0.000000    0.000000

Collectives™ on Stack Overflow

How to PIVOT rows value to columns in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related