Combine fields

Question

Post reply

Combine fields

Golden_Eye

SSC-Addicted

Points: 463
More actions
September 11, 2013 at 6:23 am

#277115

Hello,
I have to create a table and fill it with data from another tables.
My question is can you held me with a script for a field in this table, which should be sum of the values of 7 other fields.
Thanks,

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

kapil_kk SSC-Insane Points: 21316 More actions · Answer 1

Please provide the tables structure and desired output.....

you can check the link in my signature about posting the question

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 2

Assuming the NewTable has at least 1 column - in it's simplset form:

INSERT INTO NewTable

SELECT SUM(col1 + col2 + col3 + col4 + col5 + col6 + col7) FROM OtherTable

Test

D-ECLARE @OldTable TABLE( col1 int, col2 int, col3 int, col4 int, col5 int, col6 int, col7 int);

D-ECLARE @NewTable TABLE( col1 int);

INSERT @OldTable VALUES (4,7,6,9,8,1,2)

INSERT INTO @NewTable

SELECT SUM(col1 + col2 + col3 + col4 + col5 + col6 + col7) FROM @OldTable

SELECT * FROM @NewTable

Result:37

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Golden_Eye SSC-Addicted Points: 463 More actions · Answer 3

For example if I have this table:

Column 1Column 2Column 3Column 4Column 5

A 22 12 14 44

B 33 35 32 46

C 34 11 87 46

And I need to create a new one which should contain:

Column 1_newColumn 2_newColumn 3_new combined

A 22 70

B 33 113

C 34 144

So basically in the new table column 3 is the sum of the old 3,4 and 5. I need the script to create the new table.

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 4

So...just tweak what I already posted? Something like:D-ECLARE @OldTable TABLE( col1 varchar(1), col2 int, col3 int, col4 int, col5 int);

D-ECLARE @NewTable TABLE( col1 varchar(1), col2 int, col3 int);

INSERT @OldTable VALUES ('A',22,12,14,44)

INSERT @OldTable VALUES ('B',33,35,32,46)

INSERT @OldTable VALUES ('C',34,11,87,46)

INSERT INTO @NewTable

SELECT col1, col2, SUM(col3 + col4 + col5) FROM @OldTable

GROUP BY col1, col2

SELECT * FROM @NewTable "D-ECLARE" << remove the hypen...

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Sean Lange SSC Guru Points: 286573 More actions · Answer 5

Do you really need this in another table? This sounds like a great place for a computed column in your original table. That way you don't have to duplicate all the other data but you still get a single column with the sum of the other columns.

Here is an example using your posted data.

create table #SomeTable

(

Col1 char(1),

Col2 int,

Col3 int,

Col4 int,

Col5 int,

Col6 as isnull(Col3, 0) + isnull(Col4, 0) + isnull(Col5, 0) persisted

)

insert #SomeTable

select 'A', 22, 12, 14, 44 union all

select 'B', 33, 35, 32, 46 union all

select 'C', 34, 11, 87, 46

select * from #SomeTable

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Golden_Eye SSC-Addicted Points: 463 More actions · Answer 6

Hello and thanks for the replies!

My problem is that I need to use this transformation in SSIS.

Basically Table 1 is my source and table 2 is my destination.

If I use a computed column, I get an error in the SSIS Variable setting.