Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Query on row field and display as columns 1

Status
Not open for further replies.

CJwater

IS-IT--Management
Mar 26, 2008
34
US
Hi,

My table has a field AccType and it can contain 1 of 4 values on each record with an Amount for each. I need to create a table with those 4 columns and matching Amount. I'm sure this can be done but I can't picture how.

Table1 has 3 or 4 possible value CB, TL, PA and PAC
it also has the "amount" field for each record.
My current Table:
AccTYpe Amount
CB 5
TL 3
PA 20
PAC 6


I need to break out Table1 in his manner
CB TL PA PAC
5 3 20 6
5 2 8 0

Can anyone explain how this is done?
Thanks
CJ
 
If you are using SQL 2005 or SQL2008, do a google search on [google]SQL Server Pivot[/google].

If you're using SQL 2000, let me know and I'll show you how.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Sorry forgot to clarify MS SQL 2000

Thanks
CJ
 
select sum(case when ActType = 'CB' then Amount end) as CB,
sum(case when ActType = 'TL' then Amount end) as TL, etc.

from myTable

What is your grouping criteria?
 
Markros,

Thanks for the links, but I was hoping for a simple example with a concept that I could apply to my SP.

My query has no grouping no summing, I just need pivot the data and join it to a few other table to collect related info.


Here's my complete query.

SELECT
left(DFT.PONo,6) as 'PO #' , DFV.AcctDesc as 'AccTYpe', DFV.Funds as 'Amount' , DFM.InvDt as 'Invoice Date',DFM.InvNo as 'Invoice Number', DFM.InvAmt as 'Invoice Amount', DFV.GLDt as 'GL Date', DFV.VendNo
FROM
dbo.tbl_DFTracking1 DFV FULL OUTER JOIN
dbo.tbl_DFTracking2 DFT ON DFT.JobId = DFV.JobId FULL OUTER JOIN
dbo.tbl_DFTracking3 DFM ON DFT.JobId = DFM.JobId
WHERE
DFV.acctDesc LIKE 'primetime%%%' and DFM.acctDesc LIKE 'DUE FROM%%%' and DFV.gldt BETWEEN @FromDate and @ToDate and DFV.funds < 0 -- display only negative it's a credit
and RIGHT(DFV.VendNo,5) = @VendorNum
ORDER BY DFT.PONo

CJ
 
Markros,

I worked with the first example. I get it now!

Thanks You so much :)
CJ
 
Glad to help. You may post your final solution for others benefit.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top