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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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 :
Mark WillsTopic 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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chokkaStudentAuthor Commented:
Thanks !!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.