Mapping Columns with 2 criteria

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Mapping Columns with 2 criteria

Author

Topic

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2006-07-21 : 09:49:11

hi

I have a table tblDemo which i want to change from:

TCode TransDa Amount
----- ------ ------
AB001 2006001 1000
AB001 2006001 1100
AB002 2006001 1020
AB002 2006001 1150
AB001 2006002 1200
AB001 2006002 1210
AB002 2006002 1110
AB002 2006002 2000

to:

TCode TransDa Amount AB001-2006001 AB002-2006001 AB001-2006002 AB002-2006002
----- ------- ------ ------------- ------------ ------------- -------------
AB001 2006001 1000 1000 0 0 0
AB001 2006001 1100 1100 0 0 0
AB002 2006001 1020 0 1020 0 0
AB002 2006001 1150 0 1150 0 0
AB001 2006002 1200 0 0 1200 0
AB001 2006002 1210 0 0 1210 0
AB002 2006002 1110 0 0 0 1110
AB002 2006002 2000 0 0 0 2000

It looks more like permutation between TCode and TransDa. Any help many thanks in advance. Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-21 : 10:42:04

Same pivot / cross-tab technique as before

declare @table table
(
	TCode 	varchar(10),
	TransDa int,
	Amount	int
)
insert into @table
select	'AB001', 2006001, 1000	union all
select	'AB001', 2006001, 1100	union all
select	'AB002', 2006001, 1020	union all
select	'AB002', 2006001, 1150	union all
select	'AB001', 2006002, 1200	union all
select	'AB001', 2006002, 1210	union all
select	'AB002', 2006002, 1110	union all
select	'AB002', 2006002, 2000

select	TCode, TransDa,
	case when TCode = 'AB001' and TransDa = 2006001 then Amount else 0 end as [AB001-2006001],
	case when TCode = 'AB002' and TransDa = 2006001 then Amount else 0 end as [AB002-2006001],
	case when TCode = 'AB001' and TransDa = 2006002 then Amount else 0 end as [AB001-2006002],
	case when TCode = 'AB002' and TransDa = 2006002 then Amount else 0 end as [AB002-2006002]
from	@table

/* RESULT
TCode      TransDa     AB001-2006001 AB002-2006001 AB001-2006002 AB002-2006002 
---------- ----------- ------------- ------------- ------------- ------------- 
AB001      2006001     1000          0             0             0
AB001      2006001     1100          0             0             0
AB002      2006001     0             1020          0             0
AB002      2006001     0             1150          0             0
AB001      2006002     0             0             1200          0
AB001      2006002     0             0             1210          0
AB002      2006002     0             0             0             1110
AB002      2006002     0             0             0             2000
*/

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2006-07-21 : 11:13:21

Yes, thank and which i had tried before i did a post and it work and also sorry for not posting clearly. Actually i was trying to make this while statement work.

I tried this: but it didn't work. thanks

-- Declare Variables for the use of calculations..
Declare @sQry Varchar(8000)
-- Declare Variables for the use of calculations..
Declare @sQry Varchar(8000)
Declare @date int
Declare @TCode as varchar(15)
Select @Date = 0,@sQry = 'Select * , ', @TCode = '0'

-- Loop for creating the Dynamic SQL
While @Date < (Select Distinct Max(TransDate) From TblDemo) and @TCode < (Select Distinct Max(TCode) From TblDemo)
Begin
Select @Date = Min(TransDa) From Tmp Where TransDate > @Date
Select @TCode = Min(TCode) From Tmp Where TCode > @TCode
Select @sQry = @sQry + ' Case When TransDate = ' + Convert(varchar,@Date) + ' and TCode= ''@TCode''' + ' Then Amount Else 0 End As [' + @TCode + - + Convert(varchar,@Date) + '],'
End

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-21 : 11:40:17

If you were trying to create a dynamic pivot table, you can do so like this...

--data
create table #table (TCode varchar(10), TransDa int, Amount int)
insert #table
select 'AB001', 2006001, 1000 union all
select 'AB001', 2006001, 1100 union all
select 'AB002', 2006001, 1020 union all
select 'AB002', 2006001, 1150 union all
select 'AB001', 2006002, 1200 union all
select 'AB001', 2006002, 1210 union all
select 'AB002', 2006002, 1110 union all
select 'AB002', 2006002, 2000

--calculation
declare @s varchar(8000)
select @s = isnull(@s + ',' + char(13), '') + '  case when TCode = ''' + TCode + 
    ''' and TransDa = ' + cast(TransDa as varchar(10)) + ' then Amount else 0 end as [' + 
    TCode + '-' + cast(TransDa as varchar(10)) + ']'
from #table group by TCode, TransDa
exec('select TCode, TransDa,' + @s + 'from #table order by TCode, TransDa')

/*results
TCode      TransDa     AB001-2006001 AB001-2006002 AB002-2006001 AB002-2006002 
---------- ----------- ------------- ------------- ------------- ------------- 
AB001      2006001     1000          0             0             0
AB001      2006001     1100          0             0             0
AB001      2006002     0             1200          0             0
AB001      2006002     0             1210          0             0
AB002      2006001     0             0             1020          0
AB002      2006001     0             0             1150          0
AB002      2006002     0             0             0             1110
AB002      2006002     0             0             0             2000
*/

--tidy up
drop table #table

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2006-07-21 : 11:45:26

Thank a million thanks.

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2006-07-22 : 00:00:19

I tried doing a select into statement with this:

declare @s varchar(8000)
select @s = isnull(@s + ',' + char(13), '') + ' case when TCode = ''' + TCode +
''' and TransDa = ' + cast(TransDa as varchar(10)) + 'and LedType = ''Actual''' + ' then Amount else 0 end as [' +
TCode + '-' + cast(TransDa as varchar(10)) + ']'
from #table group by TCode, TransDa

Select @s = @s + 'into tblDemo' -- from #table

exec('select TCode,Amount, LedType, TransDa,' + @s + 'from #table order by TCode, TransDa')

But don't seem to work. I don't where gone wrong. Thanks

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2006-07-22 : 00:03:21

Ok I have done it with this code:

declare @s varchar(8000)
select @s = isnull(@s + ',' + char(13), '') + ' case when TCode = ''' + TCode +
''' and TransDa = ' + cast(TransDa as varchar(10)) + 'and LedType = ''Actual''' + ' then Amount else 0 end as [' +
TCode + '-' + cast(TransDa as varchar(10)) + ']'
from #table group by TCode, TransDa

exec('select * ,' + @s + 'into tblDemo from #table order by TCode, TransDa')

Thanks a million thanks anyway.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources