July 26, 2004 at 10:39 am
TABLE:
code.....amount
A..........10.00
B..........15.00
This is what I want in the result set:
code_a.....code_b.....a_amount.....b_amount
A..............B.............10.00...........15.00
Can i do this?
July 26, 2004 at 10:42 am
Assuming you know the PK and the fields
select a.code 'Code_a', b.code 'code_b', a.amount 'amount_a', b.amount 'amount_b'
from MyTable a, MyTable b
where a.code = 'a' and b.code = 'b'
If you want it dynamic for lots of fields, it can be done with dynamic sql, but will be long and confusing.
July 26, 2004 at 11:42 pm
You can find a procedure that does crostabs dynamically at: http://www.johnmacintyre.ca/codespct.asp
Razvan
July 27, 2004 at 12:13 am
A much more elegant way of doing this without tens of lines of code:
declare @col1 varchar(4096), @col2 varchar(4096)
select @col1 = '', @col2=''
select @col1=@col1 + code + ',',@row2=@row2 + convert(varchar,amount) + ',' from TABLE1
select @col1 + @col2
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply