How to create Column values to Row Values in the SQL? ( SQL 2008 or SQL 2012 )

How to create Column values to Row Values in the SQL? ( SQL 2008 or SQL 2012 )

I am trying to create a Pivot format of pushing column values to Row values.

create table #testpivot ( buyer nvarchar(1000), Amount money, TranscDate datetime )
insert into #testpivot values ('Sam', 100, '2018-01-15 10:19:34.400')
insert into #testpivot values ('Sam', 200, '2018-01-15 11:19:34.400')
insert into #testpivot values ('Sam', 300, '2018-01-15 21:19:34.400')
insert into #testpivot values ('Ed', 100, '2018-01-15 10:19:34.400')
insert into #testpivot values('Ed', 100, '2018-01-16 14:19:34.400')
insert into #testpivot values ('Ed', 100, '2018-01-16 08:19:34.400')
insert into #testpivot values('Ed', 100, '2018-01-17 12:19:34.400')
insert into #testpivot values('Ed', 100, '2018-01-17 01:19:34.400')
select * from #testpivot


-- Expected Result :-

Buyer       01/15/2018   01/16/2018      01/17/2018     TotalAmt
Sam             600                       0                0                         600                                        
Ed              100                       200             200                      500
Who is Participating?
Mark WillsConnect With a Mentor Topic AdvisorCommented:
While waiting for your feedback, here is a start (without dynamic SQL)...
select *   -- this is where we tidy up NULL values and SUM for row totals
    (select buyer, amount, cast(transcdate as date) transdate
     from #testpivot ) s
     (sum(amount) for transdate in ([2018-01-15],[2018-01-16],[2018-01-17])) p

Open in new window

See reference

You may have to
Use select buyer,amount,<datepart conversion to extract from TranscDate) as DateColumn from #testpivot )
Then you query the result pivoting on the datecolumn

The pivot is an aggregation so you can add sum(Amount) as the total.
Mark WillsTopic AdvisorCommented:
Challenge with PIVOT is having to "know" what the columns will be.

Not so bad when the periods (new column headers) are predictable.

When they are not predictable, we often resort to dynamic SQL to help with those column names.

What kind of date ranges (and granularity) are you needing to pivot ?

More than happy to help answer, and have written an article :
chokkaStudentAuthor Commented:
Thanks !!!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.