May 6, 2006 at 4:43 am
Step1: I have two tables. I need to insert a set of records from source to destination.
Step2: Insert a single row to a table from another table. The other table has multiple records. Each record has to populate one column value to the destination table.
That is
I have a table as source table Source1 with columns like this
Pat
Adm
Element
EleVal
And I have one more table as destination table Dest1 with columns as
Pat
Adm
Col1
Col2
Col3
I need to insert the records from the Source1 to Dest1
That is for example,
Source1 has the following:
Pat Adm Elem EleVal
------------------------------
1 1 A1 01
1 1 A2 02
1 1 A3 03
1 2 A1 05
1 2 A2 08
1 2 A3 13
The above records has to be put it in the dest1 table as
Pat Adm Col1 Col2 Col3
1 1 01 02 03
1 2 05 08 13
Is there any way to insert the records from the source1 to Dest1 like above. Please help
May 6, 2006 at 5:11 am
INSERT INTO [Dest1] (Pat, Adm, Col1, Col2, Col3)
SELECT Pat, Adm,
MAX(CASE WHEN Element = 'A1' THEN EleVal ELSE '' END),
MAX(CASE WHEN Element = 'A2' THEN EleVal ELSE '' END),
MAX(CASE WHEN Element = 'A3' THEN EleVal ELSE '' END)
FROM [Source1]
GROUP BY Pat, Adm
Far away is close at hand in the images of elsewhere.
Anon.
May 6, 2006 at 10:50 am
Thanks David,
It is working but I have a problem, instead of inserting a record it is inserting more than one record
That is if the above has three columns, it is inserting three rows instead of one row
That is for the above example it is doing like this:
Pat Adm Col1 Col2 Col3
1 1 01 NULL NULL
1 1 NULL 02 NULL
1 1 NULL NULL 03
1 2 05 NULL NULL
1 2 NULL 08 NULL
1 2 NULL NULL 13
Can you please help me to resolve this
Thanks
May 6, 2006 at 11:23 am
The query works fine for me using your test data.
Are you sure you aer grouping by Pat & Adm only
if you include Element then you will get multiple rows as you stated
Far away is close at hand in the images of elsewhere.
Anon.
May 6, 2006 at 11:30 am
Yes David I am using the Element Code in the Group by clause
Because first up all the Element Code and the value are of Char type and I am not using the Max function in the above quersy and
if I take it out it the Element Code from the Group By Clasue it is throwing the following error:
"Column 'Element_Code' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."
May 6, 2006 at 12:04 pm
You will have to use MAX to PIVOT the data.
Is there a reason you removed MAX, ie
More than 3 columns?
Multiple values per Element Code?
Far away is close at hand in the images of elsewhere.
Anon.
May 8, 2006 at 11:21 pm
Yes Boss it is working fine
Thanks a Lot
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply