Mapping row values according to column.

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 row values according to column.

Author

Topic

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2006-07-18 : 13:01:40

hi

I have 2 columns named TransDate and Amount
Example:

TransDate Amount
2006001 1000
2006001 1200
2006001 1300
2006002 120
2006002 2600
2006002 980
2006002 3540
2006003 120
2006003 2200
2006003 3982
2006003 120

How do I dynamically create columns name base on TransDate such as this:

2006001 2006002 2006003 TransDate Amount
2006001 1000
2006001 1200
2006001 1300
2006002 120
2006002 2600
2006002 980
2006002 3540
2006003 120
2006003 2200
2006003 3982
2006003 120

and how do i insert the amount according to the respective date like:

2006001 2006002 2006003 TransDate Amount
1000 0 0 2006001 1000
1200 0 0 2006001 1200
1300 0 0 2006001 1300
0 120 0 2006002 120
0 2600 0 2006002 2600
0 980 0 2006002 980
0 3540 0 2006002 3540
0 0 120 2006003 120
0 0 2200 2006003 2200
0 0 3982 2006003 3982
0 0 120 2006003 120

Your reply is greatly much appreciated. Thanks

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-18 : 13:10:35

This looks like a dynamic pivot/crosstab request, and there are several articles out there on the subject.
I recommend you take a look at them...

http://www.sqlservercentral.com/columnists/plarsson/pivottableformicrosoftsqlserver.asp
http://www.sqlteam.com/item.asp?ItemID=2955
http://weblogs.sqlteam.com/jeffs/archive/2005/05/02/4842.aspx

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-18 : 23:25:36

hi

so sorry if i did not express clearly of the layout. it is not crosstab or pivot but just want to create or transform the original table format to another one. Thanks

I have a sql table with 2 columns named TransDate and Amount
Example:

TransDate Amount
2006001 1000
2006001 1200
2006001 1300
2006002 1200
2006002 2600
2006002 9800
2006002 3540
2006003 1200
2006003 2200
2006003 3982
2006003 1200

How do I create another sql table with columns name base on TransDate such as this:

TransDate Amount 2006001 2006002 2006003
2006001 1000
2006001 1200
2006001 1300
2006002 1200
2006002 2600
2006002 9800
2006002 3540
2006003 1200
2006003 2200
2006003 3982
2006003 1200

and then how do i insert the amount according to the respective date like:

TransDate Amount 2006001 2006002 2006003
2006001   1000   1000        0 0
2006001   1200   1200        0 0
2006001   1300   1300      0 0
2006002   1200   0    1200 0
2006002   2600   0    2600 0
2006002   9800   0    9800 0
2006002   3540   0    3540 0
2006003   1200   0    0 1200
2006003   2200   0    0 2200
2006003   3982   0    0 3982
2006003   1200   0    0 1200

sorry for the untidy layout but basically those months that do not have any value should be 0 and if there is any value it should be place according to the column. Thanks

Your reply is greatly much appreciated. Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-18 : 23:45:33

As Ryan pointed. This is PIVOT / CROSSTAB

declare @table table
(
	TransDate 	int,
	Amount		int
)
insert into @table
select	2006001, 1000	union all
select	2006001, 1200	union all
select	2006001, 1300	union all
select	2006002, 1200	union all
select	2006002, 2600	union all
select	2006002, 9800	union all
select	2006002, 3540	union all
select	2006003, 1200	union all
select	2006003, 2200	union all
select	2006003, 3982	union all
select	2006003, 1200 

select	*,
	case when TransDate = 2006001 then Amount else 0 end as [2006001],
	case when TransDate = 2006002 then Amount else 0 end as [2006002],
	case when TransDate = 2006003 then Amount else 0 end as [2006003]
from	@table
/*
TransDate   Amount      2006001     2006002     2006003     
----------- ----------- ----------- ----------- ----------- 
2006001     1000        1000        0           0
2006001     1200        1200        0           0
2006001     1300        1300        0           0
2006002     1200        0           1200        0
2006002     2600        0           2600        0
2006002     9800        0           9800        0
2006002     3540        0           3540        0
2006003     1200        0           0           1200
2006003     2200        0           0           2200
2006003     3982        0           0           3982
2006003     1200        0           0           1200

(11 row(s) affected)
*/

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2006-07-19 : 00:01:19

Thank you guys so much. But one last question, i hope so.

I am work with a table with 300,000 rows of records and there are many dates, not just 2006001,2006002 and 2006003. How do i loop thru all those value instead of using this.

insert into @table
select 2006001, 1000 union all
select 2006001, 1200 union all
select 2006001, 1300 union all
select 2006002, 1200 union all
select 2006002, 2600 union all
select 2006002, 9800 union all
select 2006002, 3540 union all
select 2006003, 1200 union all
select 2006003, 2200 union all
select 2006003, 3982 union all
select 2006003, 1200

select *,
case when TransDate = 2006001 then Amount else 0 end as [2006001],
case when TransDate = 2006002 then Amount else 0 end as [2006002],
case when TransDate = 2006003 then Amount else 0 end as [2006003]
from @table

The date span from 2006001 to even next year and more.

Once again, thank you so much. I am greatly thankful to you guys.

khtan
In (Som, Ni, Yak)

17689 Posts

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

"I am work with a table with 300,000 rows of records and there are many dates, not just 2006001,2006002 and 2006003. How do i loop thru all those value instead of using this. "
The best way of-couse is do this in your front end application. Altenatively you can also use Dynamic SQL to do this.

Even that, there might be hundres or thousand of columns. Do you want to show all these columsn ? How many columns do you want to show ?

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2006-07-19 : 01:15:53

hi

so sorry again which I did not make my question clearly.

Basically i need to deal with only 2 columns even though i have 30 columns for a table. The columns that i want to deal with are TransDadte and Amount.

So instead of using select case statement for each date and amount and union all in TransDate and Amount columns, how do i loop thru these date and amount records?

For example 300,000 rows for TransDate and Amount columns:

TransDate Amount
2005001  1000
2005002  1200
thru
2006006  2000
and so on as the number of rows for TransDate and Amount will continue to increase.

As the number of rows for TransDate and Amount would increase, i cannot afford to keep on typing:

select 2006003, 1200 union all
select 2006003, 2200 union all
select 2006003, 3982 union all
select 2006003, 1200 union all
select 2006004, 1300 union all
select 2007001, 1100

and so on.

I will end up with a long list of select case, and amount and union all statement in my query.

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-19 : 01:48:50

the part on "insert into . . . select .. .union all" is basically for me to create some testing data to test and demostrate the query. You don't have to do this at all.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-07-19 : 02:37:44

For looping through you need to create a dynamic sql
Somthing like this may help..



-- Create the Temp Table..
Create table Tmp
(
	TransDate 	int,
	Amount		int
)
-- Insert Values in the Temp Table.. 
insert into Tmp
select	2006001, 1000	union all
select	2006001, 1200	union all
select	2006001, 1300	union all
select	2006002, 1200	union all
select	2006002, 2600	union all
select	2006002, 9800	union all
select	2006002, 3540	union all
select	2006003, 1200	union all
select	2006003, 2200	union all
select	2006003, 3982	union all
select	2006003, 1200 


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

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

-- Delete the Last , due to the loop.. 

Select @sQry = left(@sQry,len(@sQry)-1)
-- Create the From Cluase. 

Select @sQry = @sQry + ' From Tmp'
-- Execute the Query 
Exec(@sQry)

--Drop The Temp Table. 
Drop Table Tmp

Chirag

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2006-07-19 : 02:42:33

hi

so so so sorry, I get what you mean. It is this:

select *,
case when TransDate = 2006001 then Amount else 0 end as [2006001],
case when TransDate = 2006002 then Amount else 0 end as [2006002],
case when TransDate = 2006003 then Amount else 0 end as [2006003]
from @table

How do I do a dynamic statement when my TransDate is contiuing increasing. For example:

Declare @MyCol as varhcar(15)
While (something somthing)
Begin
case when TransDate = @MyCol then Amount else 0 end as [@MyCol ],
end

The above is just an example of what i want to achieve. it is not the actual code but some rough ideal. thanks

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-07-19 : 02:48:08

did you tried what i have posted?

Chirag

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-19 : 02:59:22

"I get what you mean. It is this:"
Yes.

"How do I do a dynamic statement when my TransDate is contiuing increasing. For example:"
Chirag has posted the query. Give it a try.

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2006-07-19 : 03:19:11

hi

forgive me for being a cause of inconvenience to you guys. i cannot locate chiragkhabaria posting. I have click his links but don't to have seems the posting i want. thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-19 : 03:25:10

quote:
Originally posted by sg2255551

hi

forgive me for being a cause of inconvenience to you guys. i cannot locate chiragkhabaria posting. I have click his links but don't to have seems the posting i want. thanks

He posted it in this thread at 07/19/2006 : 02:37:44

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2006-07-19 : 03:39:42

hi

Yes I found it. I guess i am probably to tired to even notice it. Hey guys thank you so much.

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2006-07-19 : 04:03:42

hi

Yes I found it. I guess i am probably to tired to even notice it. Hey guys thank you so much.

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2006-07-19 : 04:12:11

sorry one very, very,very last question. how do i use select into after excuting exec(@sQry) something like this:

Select * into tblfinal Exec(@sQry)

Thanks

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-07-19 : 04:30:37

Check out this script for inserting values into
tblfinal



-- Create the Temp Table..
Create table Tmp
(
	TransDate 	int,
	Amount		int
)
-- Insert Values in the Temp Table.. 
insert into Tmp
select	2006001, 1000	union all
select	2006001, 1200	union all
select	2006001, 1300	union all
select	2006002, 1200	union all
select	2006002, 2600	union all
select	2006002, 9800	union all
select	2006002, 3540	union all
select	2006003, 1200	union all
select	2006003, 2200	union all
select	2006003, 3982	union all
select	2006003, 1200 


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

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

-- Delete the Last , due to the loop.. 

Select @sQry = left(@sQry,len(@sQry)-1)

-- Now insert into the other Table 
Select @sQry = @sQry + 'Into tblfinal '
-- Create the From Cluase. 

Select @sQry = @sQry + ' From Tmp'
-- Execute the Query 
Exec(@sQry)

Select * From tblfinal
--Drop The Temp Table. 
Drop Table Tmp