July 23, 2017 at 9:25 am
Select from a table, is there an easier way (without using Cursor) to have the data with the same "Invoice Number" to display its cost on the same row? In other worlds instead of seeing two rows for the invoice 118919, one with a cost 30.00 and another with a cost of 749.25. I will see one row for invoice 118919 but with two columns, one for 30.00 & one for 749.25?
July 23, 2017 at 10:59 am
Look up 'dynamic pivot'. It's a way, using dynamic SQL, to do what you want. You need dynamic SQL, because the number of columns isn't defined at the time of writing the query.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 23, 2017 at 11:51 am
Thanks works, I forgot about the Pivot function.
July 23, 2017 at 1:40 pm
I have the values displaying from left to right, the dynamic Pivot works, but if I want add an additional column to be the total of the months
I would have to union the alias table that was pivot along with Group by.....
Feb Mar Apr May Jun Jul Total <- is the new column which I want to create that not part of the existing table
8 7 5 3 0 2 25
3 6 9 0 0 0 18
July 23, 2017 at 1:45 pm
Post your code please?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 23, 2017 at 6:30 pm
select [Invoice Number],
[Invoice Date],
[Dept#],
[Loc#],
[Category#],
[GS] as 'General Store',
[Regfee] as 'NE Regional Fees',
[PCOST] as 'Pacific Fees',
[OfficeFee] as 'Official Fees'
from
(
select [Invoice Number],
[Invoice Date],
[Invoice Category],
llcode,
lamount
from RPTtbl
) as K
Pivot
(
sum(lamount) for llcode
in (General Store, NE Regional Fees, Pacific, Official Fee))
as p
order by 1
July 23, 2017 at 7:53 pm
How about the CREATE TABLE and INSERT scripts?
July 24, 2017 at 12:59 am
That's not what I meant when I said to look up *dynamic* pivot. That's a normal pivot, it's got no flexibility to add more columns.
But, something like this should work (untested, of course)
WITH InvoiceData AS (
SELECT [Invoice Number],
[Invoice Date],
[Dept#],
[Loc#],
[Category#],
[GS] as 'General Store',
[Regfee] as 'NE Regional Fees',
[PCOST] as 'Pacific Fees',
[OfficeFee] as 'Official Fees'
from
(
select [Invoice Number],
[Invoice Date],
[Invoice Category],
llcode,
lamount
from RPTtbl
) as K
Pivot
(
sum(lamount) for llcode
in ([General Store], [NE Regional Fees], [Pacific], [Official Fee]))
as p
)
SELECT [Invoice Number], [Invoice Date], [Invoice Category], [General Store], [NE Regional Fees], [Pacific], [Official Fee], [General Store] + [NE Regional Fees] + [Pacific] + [Official Fee] FROM InvoiceData
order by [Invoice Number]
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply