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 )
go
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')
go
select * from #testpivot
Go
-- Expected Result :-
Buyer 01/15/2018 01/16/2018 01/17/2018 TotalAmt
--------------------------
Sam 600 0 0 600
Ed 100 200 200 500
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 )
go
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')
go
select * from #testpivot
Go
-- Expected Result :-
Buyer 01/15/2018 01/16/2018 01/17/2018 TotalAmt
--------------------------
Sam 600 0 0 600
Ed 100 200 200 500
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 : https://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/A_653-Dynamic-Pivot-Procedure-for-SQL-Server.html
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 : https://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/A_653-Dynamic-Pivot-Procedure-for-SQL-Server.html
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks !!!
https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx
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.