CODEWITHSUNDEEP
sqlsql-serversql-server-2000
dazzathedrummer
dazzathedrummer

Reputation: 531

SQL Server list by category

If I have a table that stores lines of balances 

    Code     cat  balance
    ----------------------
    Sales    101  123
    Cost     101   45
    Overhead 101   67
    Sales    102  890
    Costs    102   12
    Overhead 102   34

(etc, assuming that the 'cat' is a product category and there are many more categories and that 'code' represents a general ledger code)

How would I code it so that the output shows....

Code   101   102   [103..etc ->]
-----------------------------------    
Sales  123   890   [#]
Costs   45    12   [#]
Overhead...

This is probably very simple but I cant find a way of doing this and any help would be greatly appreciated.

Darren

Upvotes: 2

Views: 182

Answers (3)

Taryn
Taryn

Reputation: 247840

Unfortunately, SQL Server 2000 does not have a PIVOT function but you can replicate it using dynamic SQL.

DECLARE @query  AS NVARCHAR(MAX),  -- VARCHAR(8000)  in SQL Server 2000 or text
  @rowCount as int,
  @pivotCount as int,
  @pivotRow as varchar(10) = ''

select distinct cat
into #colsPivot
from yourtable

set @pivotCount= (select COUNT(*) from #colsPivot) 
set @rowCount = 1
set @query = ''

---- create the CASE string
while @rowCount <= @pivotCount
    begin
        set @pivotRow = (select Top 1 cat from #colsPivot)

        set @query = @query + ', sum(case when cat = ' + @pivotRow + ' then balance end) as ''' + @pivotRow + ''''

        delete from #colsPivot where cat = @pivotRow

        if @rowCount <= @pivotCount
            begin
                set @rowCount = @rowCount + 1
                print @rowCount
            end
    end

-- add the rest of the SQL Statement
set @query = 'SELECT code ' + @query + ' from yourtable group by code'

exec(@query)

drop table #colsPivot

See SQL Fiddle With Demo

Upvotes: 3

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125444

select 
    Code,
    sum(case cat when 101 then balance else 0) as '101',
    sum(case cat when 102 then balance else 0) as '102'
from balances
group by Code

Upvotes: 1

AnandPhadke
AnandPhadke

Reputation: 13506

Try using PIVOT function of sql server

Upvotes: 0

Related Questions