Query on row field and display as columns 1

[CJwater]

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

 

[gmmastros]

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

 

[CJwater]

Sorry forgot to clarify MS SQL 2000

Thanks
CJ

 

[markros]

SQL Server 2005

http://msdn.microsoft.com/en-us/library/ms177410.aspx

 

[markros]

See also

http://forums.asp.net/t/1452944.aspx

 

[markros]

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?

 

[CJwater]

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]

Use the suggestion from my previous post if you know in advance all possible types. Use your current result as derived table.

You may take a look at

http://social.msdn.microsoft.com/fo.../thread/831611f4-f05f-4467-8afe-865b0048b9ae/

http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables

and finally this interesting article

http://sqlservercodebook.blogspot.com/2008/07/using-coalesce-to-pivot.html

 

[CJwater]

Markros,

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

Thanks You so much
CJ

 

[markros]

Glad to help. You may post your final solution for others benefit.