Wednesday, April 16, 2008

Row To Column

Suppose you have a table structure like

create table tmpStocks
(
[StockSymbol] [char] (8),
[ExchMM] [varchar] (10)
)
go
create table tmpExchanges
(
[Name] [char] (10),
[ExchSymbo] [char] (1)
)
go


2. Insert some values:

insert into tmpStocks (stocksymbol,exchmm) values ('KS','IP')
insert into tmpStocks (stocksymbol,exchmm) values ('PK6','IB')
insert into tmpStocks (stocksymbol,exchmm) values ('LHJ','I')
insert into tmpStocks (stocksymbol,exchmm) values ('JHL','P')
insert into tmpExchanges (name,ExchSymbo) values ('ISE','I')
insert into tmpExchanges (name,ExchSymbo) values ('BOX','B')
insert into tmpExchanges (name,ExchSymbo) values ('PCost','P')


and you want result like

how to get the results as following:

[StockSymbol] [ExchMM] [ISE] [BOX] [PCost]
ks IP 1 0 1
PK6 IB 1 1 0
LHJ I 1 0 0
JHL P 0 0 1


Solution1


select s.stocksymbol, s.exchmm,
ise = sum( case e.exchsymbo when 'I' then 1 else 0 end ),
box = sum( case e.exchsymbo when 'B' then 1 else 0 end ),
pcost = sum( case e.exchsymbo when 'P' then 1 else 0 end )
from tmpStocks s
left outer join tmpExchanges e
on charindex( e.exchsymbo, s.exchmm ) > 0
group by s.stocksymbol, s.exchmm



Solution2

declare @tname char(10), @tsym char(1)
declare @selectSQL varchar(1000)

select @selectSQL = ''

declare cur cursor for
select distinct [name], exchsymbo
from tmpExchanges
order by [name]

open cur
fetch cur into @tname, @tsym

while @@fetch_status = 0
begin

select @selectSQL = @selectSQL + ', ' + rtrim(@tname) + '= sum( case e.exchsymbo when ''' + @tsym + ''' then 1 else 0 end ) '
fetch next from cur into @tname, @tsym

end

select @selectSQL = 'select s.stocksymbol, s.exchmm ' + @selectSQL + ' from tmpStocks s '
+ 'left outer join tmpExchanges e '
+ 'on charindex( e.exchsymbo, s.exchmm ) > 0 '
+ 'group by s.stocksymbol, s.exchmm '

exec(@selectSQL)

close cur
deallocate cur

2 comments:

Cristian said...

3 years later, thank you!!

Venkatesan Prabu said...

Thanks for this article.
Both the solutions are generating the same query.

Any it's too late to ask this query.

Thanks

Cheers,
Venkatesan prabu J
Head, www.kaashivinfotech.com