sql question - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

sql question

Author

Topic

ntn104
Posting Yak Master

175 Posts

Posted - 2011-02-22 : 10:07:45

I have to display 14 fields data for 4 columns, and to make sure 14 fields data will fall in the right column. For example:

FIELD NAME COL1 COL2 COL3 COL4
1 20 0 25 40
2 0 0 0 0
3 40 50 0 0
.
.
.
14 50 20 10 0

I tried to use case statement to display, but don't know how to display multiple fields for each column. please see below is samle of my sql...so you can figure out the way to help me...thanks,

select acount,
year,
type,
case when type='1' then line1, line 2, line3, line4, line5, line6, line7, line8, line9, line10, line11, line12, line13, line14 end as col1,
case when type='2' then line1, line 2, line3, line4, line5, line6, line7, line8, line9, line10, line11, line12, line13, line14 end as col2,
case when type='3' then line1, line 2, line3, line4, line5, line6, line7, line8, line9, line10, line11, line12, line13, line14 end as col3,
case when type='4' then line1, line 2, line3, line4, line5, line6, line7, line8, line9, line10, line11, line12, line13, line14 end as col4,
line1, line 2, line3, line4, line5, line6, line7, line8, line9, line10, line11, line12, line13, line14
from table1

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-22 : 12:42:01

use some thing like this

(case when type='1' then line1+','+line 2+','+line3+','+line4+','+line5+','+line6+','+line7+','+line8+','+line9+','+line10+','+line11+','+line12+','+line13+','+line14 end) as col1

Cheers
MIK

ntn104
Posting Yak Master

175 Posts

Posted - 2011-02-22 : 13:14:23

I got error message: "Can not convert to numeric (126,38)" what does that mean? thanks,

quote:
Originally posted by MIK_2008

use some thing like this

(case when type='1' then line1+','+line 2+','+line3+','+line4+','+line5+','+line6+','+line7+','+line8+','+line9+','+line10+','+line11+','+line12+','+line13+','+line14 end) as col1

Cheers
MIK

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-02-22 : 13:20:36

what data types are line fields?

If you don't have the passion to help people, you have no passion

ntn104
Posting Yak Master

175 Posts

Posted - 2011-02-22 : 13:23:39

data type of all the line fields is decimal (11,0)
and data type for type=varchar(1)

Thanks,

quote:
Originally posted by ntn104

I got error message: "Can not convert to numeric (126,38)" what does that mean? thanks,

quote:
Originally posted by MIK_2008

use some thing like this

(case when type='1' then line1+','+line 2+','+line3+','+line4+','+line5+','+line6+','+line7+','+line8+','+line9+','+line10+','+line11+','+line12+','+line13+','+line14 end) as col1

Cheers
MIK

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-02-22 : 13:41:19

so if line fields are decimal why do you think you get
"Can not convert to numeric (126,38)" when you do

line1+','+line 2+','+line3+','+line4+','+line5+','+line6+','+line7+','+line8+','+line9+','+line10+','+line11+','+line12+','+line13+','+line14

If you don't have the passion to help people, you have no passion

ntn104
Posting Yak Master

175 Posts

Posted - 2011-02-22 : 14:11:24

I believe when it is trying to combine comma and addition together, and I have numeric fields, SQL using plus, so it can not recognize what to do.
I got a solution, however take a lot of time for typing:
Case when type='1' then line1 else 0 end as col1_line1,
Case when type='2' then line2 else 0 end as col1_line2,
Case when type='3' then line3 else 0 end as col1_line3,
Case when type='4' then line4 else 0 end as col1_line4,
etc....

thanks everyone!

quote:
Originally posted by yosiasz

so if line fields are decimal why do you think you get
"Can not convert to numeric (126,38)" when you do

line1+','+line 2+','+line3+','+line4+','+line5+','+line6+','+line7+','+line8+','+line9+','+line10+','+line11+','+line12+','+line13+','+line14

If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources