April 11, 2010 at 6:49 am
Hi,
Believe me this is a hugely scaled-down version of want I need...
I have a SQL table (dbo.LSR_Prep_xml) that contains 2 columns that I need to group by (Element_Name and Field_Id). I need to output to 2 existing columns with values based on this data. In the following example there are 18 rows (imported from an xml file). Note that there are repeating Field_Id values for each Element_Name (3 x 6).
Example
Element_Name, Field_Id
Expected, 62
Expected, 66
Expected, 75
Expected, 79
Expected, 88
Expected, 92
Minimum, 62
Minimum, 66
Minimum, 75
Minimum, 79
Minimum, 88
Minimum, 92
Maximum, 62
Maximum, 66
Maximum, 75
Maximum, 79
Maximum, 88
Maximum, 92
Step 1
Based on values in the Field_Id column, I need to ouput to the Target_Value field a value of either '1' or '2' for each occurence (there should always be an even number). So, the 1st occurence should be '1', the next should be '2', the next should be '1' etc..
Element_Name, Field_Id, Target_Value
Expected, 62, 1
Expected, 66, 2
Expected, 75, 1
Expected, 79, 2
Expected, 88, 1
Expected, 92, 2
Minimum, 62, 1
Minimum, 66, 2
Minimum, 75, 1
Minimum, 79, 2
Minimum, 88, 1
Minimum, 92, 2
Maximum, 62, 1
Maximum, 66, 2
Maximum, 75, 1
Maximum, 79, 2
Maximum, 88, 1
Maximum, 92, 2
Step 2
Each set of Element_Name and Field_Id values should be grouped with each '1' and '2' in the Target_Value field to create values in the Line_Id field.
Element_Name, Field_Id, Target_Value, Line_Id
Expected, 62, 1, 1
Expected, 66, 2, 1
Expected, 75, 1, 2
Expected, 79, 2, 2
Expected, 88, 1, 3
Expected, 92, 2, 3
Minimum, 62, 1, 1
Minimum, 66, 2, 1
Minimum, 75, 1, 2
Minimum, 79, 2, 2
Minimum, 88, 1, 3
Minimum, 92, 2, 3
Maximum, 62, 1, 1
Maximum, 66, 2, 1
Maximum, 75, 1, 2
Maximum, 79, 2, 2
Maximum, 88, 1, 3
Maximum, 92, 2, 3
Step 3
Once this is actioned, I need the insert these values into another table (dbo.LSR_Prep) based on the Line_Id count. In this example it would be 3 inserts as MAX(Line_Id) = 3 (in reality there could be any number of inserts).
If the value of Target_Value is 1, then the T1 field is loaded in dbo.LSR_Prep field is loaded and T2 filed is loaded where Target_Value = 2
--FOR EACH 'Line_Id'
--in this example MAX(Line_Id) = 3
--Therefore, I need the following 6 queries performed x 3
--NOTE: Additional field in the INSERT and UPDATE statements based on the values from the WHERE clause
--Query 1
INSERT INTO dbo.LSR_Prep
(
[File_Name],
Element_Name,
Field_Id,
Tar_1
)
SELECT
[File_Name],
Element_Name,
Field_Id,
Field_Value
FROM dbo.LSR_Prep_xml
WHERE
Target_Value = 1
AND
Element_Name = 'Expected'
AND
Line_Id = 1
--Query 2
UPDATE dbo.LSR_Prep
SET
Tar_2 = Field_Value
FROM dbo.LSR_Prep_xml A JOIN dbo.LSR_Prep B ON
A.[File_Name] = B.[File_Name]
WHERE
Target_Value = 2
AND
Element_Name = 'Expected'
AND
Line_Id = 2
--Query 3
UPDATE dbo.LSR_Prep
SET
T1_LO = Field_Value
FROM dbo.LSR_Prep_xml A JOIN dbo.LSR_Prep B ON
A.[File_Name] = B.[File_Name]
WHERE
Target_Value = 1
AND
Element_Name = 'Minimum'
AND
Line_Id = 1
--Query 4
UPDATE dbo.LSR_Prep
SET
T2_LO = Field_Value
FROM dbo.LSR_Prep_xml A JOIN dbo.LSR_Prep B ON
A.[File_Name] = B.[File_Name]
WHERE
Target_Value = 2
AND
Element_Name = 'Minimum'
AND
Line_Id = 2
--Query 5
UPDATE dbo.LSR_Prep
SET
T1_HI = Field_Value
FROM dbo.LSR_Prep_xml A JOIN dbo.LSR_Prep B ON
A.[File_Name] = B.[File_Name]
WHERE
Target_Value = 1
AND
Element_Name = 'Maximum'
AND
Line_Id = 1
--Query 6
UPDATE dbo.LSR_Prep
SET
T2_HI = Field_Value
FROM dbo.LSR_Prep_xml A JOIN dbo.LSR_Prep B ON
A.[File_Name] = B.[File_Name]
WHERE
Target_Value = 2
AND
Element_Name = 'Maximum'
AND
Line_Id = 2
How do I perform this?
Thanks in advance,
April 11, 2010 at 11:33 am
Unfortunately, your description is not very clear nor do we have any (runable) code that would support your question nor any expected result based on your sample data...
Therefore, I had to make something up by guessing what you're looking for.
If that's not what you're looking for, please provide more details so we can better understand what you're trying to do. If you'd provide ready to use test data together with the expected result as described in the first link in my signature I'm sure you'll get a tested solution pretty soon.
DECLARE @tbl TABLE
(
Element_Name VARCHAR(30), Field_Id INT
)
INSERT INTO @tbl
SELECT 'Expected', 62 UNION ALL
SELECT 'Expected', 66 UNION ALL
SELECT 'Expected', 75 UNION ALL
SELECT 'Expected', 79 UNION ALL
SELECT 'Expected', 88 UNION ALL
SELECT 'Expected', 92 UNION ALL
SELECT 'Minimum', 62 UNION ALL
SELECT 'Minimum', 66 UNION ALL
SELECT 'Minimum', 75 UNION ALL
SELECT 'Minimum', 79 UNION ALL
SELECT 'Minimum', 88 UNION ALL
SELECT 'Minimum', 92 UNION ALL
SELECT 'Maximum', 62 UNION ALL
SELECT 'Maximum', 66 UNION ALL
SELECT 'Maximum', 75 UNION ALL
SELECT 'Maximum', 79 UNION ALL
SELECT 'Maximum', 88 UNION ALL
SELECT 'Maximum', 92
;WITH cte AS
(SELECT Element_Name , Field_Id,
ROW_NUMBER() OVER(PARTITION BY Element_Name ORDER BY Field_Id ) AS ROW
FROM @tbl
),
cte2 AS
(SELECT
Element_Name , Field_Id,
2 - ROW % 2 AS Target_Value,
CEILING(ROW/2.0) AS Line_Id
FROM cte
)
SELECT
Element_Name,
Field_Id,
MAX(
CASE WHEN Target_Value = 1
AND Element_Name = 'Expected'
AND Line_Id = 1
THEN 'Field_Value1' ELSE '' END
) AS Tar_1,
MAX(
CASE WHEN
Target_Value = 2
AND Element_Name = 'Expected'
AND Line_Id = 2
THEN 'Field_Value2' ELSE '' END
) AS Tar_2,
MAX(
CASE WHEN Target_Value = 1
AND Element_Name = 'Minimum'
AND Line_Id = 1
THEN 'Field_Value3' ELSE '' END
) AS T1_LO,
MAX(
CASE WHEN Target_Value = 2
AND Element_Name = 'Minimum'
AND Line_Id = 2
THEN 'Field_Value3' ELSE '' END
) AS T2_LO,
MAX(
CASE WHEN Target_Value = 1
AND Element_Name = 'Maximum'
AND Line_Id = 1
THEN 'Field_Value4' ELSE '' END
) AS T1_HI,
MAX(
CASE WHEN Target_Value = 2
AND Element_Name = 'Maximum'
AND Line_Id = 2
THEN 'Field_Value4' ELSE '' END
) AS T2_HI
FROM cte2
GROUP BY Element_Name , Field_Id
April 11, 2010 at 1:06 pm
Sorry if you felt that there was a lack of detail to my original post. I do appreciate your assistance.
One area that may have been not communicated correctly is the Field_Id. These values in my example may change (or increase in numbers) from each imported xml file. Therefore, I have to be flexible about these values.
Due to a problem with alignment when posting the items below, I have had to rename several columns:
L_Id represents Line_Id
T_Set represents Target_Values
F_Id represents Field_Id
Element_Names are always a single string (where each word is joined by a '_' i.e. AA_Id)
Field_Values may be blank (in this example, Id's 3 and 10 have blank Field_Values)
dbo.LSR_Prep_xml - at present
IdL_IdT_SetF_IdElement_NameField_Value
1000AA_IdZXA101
2000Batch_NoRun No. 089971
3000GRN_No
4000Logged_In_ByZFILE
5000Sample_Date20100401 05:42
42000PriorityRoutine
400028Customer_CodeAB
7000Format_Code7464
8000Formula_Version45
60031Product_Code172FP1
10000Additive_Code
110031Lab_Product_GroupF2
120031Lab_Product_GroupDC
90031Product_DescriptionG Case
19000Analysis_NameNIR Prod
20000Analysis_NameNIR Mem
21000Analysis_NameNIR Fro
410025Customer_Code22521
16000Analysis_Code900
17000Analysis_Code901
18000Analysis_Code902
220062Expected12.48
230066Expected12.48
240075Expected3.18
250079Expected3.18
260088Expected18.4
270092Expected18.4
280062Minimum11.98
290066Minimum0
300075Minimum2.68
310079Minimum2.38
320088Minimum17.4
330092Minimum16.56
340062Maximum12.98
350066Maximum14.5
360075Maximum3.68
370079Maximum4.78
380088Maximum19.4
390092Maximum22.08
15000Load_Quantity39.0391
13000Pack_FormP
14000Pack_Size1
I need to apply changes to the L_Id and T_Set columns so that it looks like this:
(NOTE: The Analysis_Code and Analysis_Names also have a Line_Id)
dbo.LSR_Prep_xml - After changes
IdL_IdT_SetF_IdElement_NameField_Value
1000AA_IdZXA101
2000Batch_NoRun No. 089971
3000GRN_No
4000Logged_In_ByZFILE
5000Sample_Date20100401 05:42
42000PriorityRoutine
400028Customer_CodeAB
7000Format_Code7464
8000Formula_Version45
60031Product_Code172FP1
10000Additive_Code
110031Lab_Product_GroupF2
120031Lab_Product_GroupDC
90031Product_DescriptionG Case
19100Analysis_NameNIR Prod
20200Analysis_NameNIR Mem
21300Analysis_NameNIR Fro
410025Customer_Code22521
16100Analysis_Code900
17200Analysis_Code901
18300Analysis_Code902
221162Expected12.48
231266Expected12.48
242175Expected3.18
252279Expected3.18
263188Expected18.4
273292Expected18.4
281162Minimum11.98
291266Minimum0
302175Minimum2.68
312279Minimum2.38
323188Minimum17.4
333292Minimum16.56
341162Maximum12.98
351266Maximum14.5
362175Maximum3.68
372279Maximum4.78
383188Maximum19.4
393292Maximum22.08
15000Load_Quantity39.0391
13000Pack_FormP
14000Pack_Size1
Then, these fields have to populate another SQL table. The example above would create 3 rows.
The example below shows the dbo.LSR_Prep table (after insert) in simplified terms as there are additional fields which need to be populated. Hopefully, you are able to see my expected outputs in relation to the 'Expected', 'Minimum' and 'Maximum' values.
dbo.LSR_Prep - After insert
AA_IdTar_1Tar_2T1_LOT2_LOT1_HIT2_HIAnalysis_NameAnalysis_Code
ZXA10112.4812.4811.98012.9814.5NIR Prod900
ZXA1013.183.182.682.383.684.78NIR Mem901
ZXA10118.418.417.416.5619.422.08NIR Fro902
April 11, 2010 at 2:12 pm
May I point you to the first link in my signature again?
You can also have a look at my first reply to see how I created the test table and the sample data.
Please provide ready to use data so we can focus on the solution instead of trying to set up a test environment. Please help us help you.
April 11, 2010 at 2:21 pm
When thinking about the data you provided it might be easier to start from scratch.
It might be a lot easier to deal with the xml data instead of the intermediate table, since the whole structure is lost.
Would you please provide a sample of the xml file?
April 12, 2010 at 2:11 am
Lutz - Thank you for your time and effort. (Unfortunately?) this posting was raised to pose several questions regarding change requirements focusing on 2 SQL tables, using T-SQL, so I won't be supplying a XML file.
However, I will endeavour to resolve this.
Many, many thanks again.
April 12, 2010 at 10:38 am
DerbyNeal (4/12/2010)
Lutz - Thank you for your time and effort. (Unfortunately?) this posting was raised to pose several questions regarding change requirements focusing on 2 SQL tables, using T-SQL, so I won't be supplying a XML file.However, I will endeavour to resolve this.
Many, many thanks again.
I was asking for the xml file to apply some XQuery code, which is T-SQL (at least to some degree). If you have a look at BOL (the SQL Server help system usually installed together with SQL Server) under "XQuery" you'll find some code snippets.
I don't mind if you bother to supply the original xml data. It would just have been a lot easier to build the final table you're asking for. It's all up to you. 😉
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply