July 24, 2014 at 5:21 pm
Hi,
I need help with the sql to update column values in a table. I have a table of imported data from a flat file. Each row in the flat file starts with 2 characters that indicates what type of information the row contains. The rows starting with 'HH' are header rows containing the MID for the entire group of data.
Please see the sample table below. I want to write a sql statement to update the MID column for each grouping of rows. For example, all of the MIDs should be the same for ImportIDs 1-18, 19-37, and 38-44.
Fyi, I added the rank column to the table based on the following:
SELECT RANK() OVER (PARTITION BY MID ORDER BY ImportID) AS 'Rank', ImportID, Indicator, MID,NumRows
FROM #MIDs1
ORDER BY ImportID
Thanks for your help
RankImportIDIndicatorMIDNumRows
11HHA497BEC3-9FDF-4D73-B3E0-96B11D453B5CNULL
12G1NULLNULL
23G2NULLNULL
34G3NULLNULL
45ADNULLNULL
56CGNULLNULL
67PLNULLNULL
78ICNULLNULL
89N1NULLNULL
910N2NULLNULL
1011N3NULLNULL
1112N4NULLNULL
1213A1NULLNULL
1314C1NULLNULL
1415S1NULLNULL
1516S2NULLNULL
1617T1NULLNULL
1718STNULLNULL
119HHBFAD7D75-6AFF-4BCE-9589-30EE0CF52D9BNULL
1820G1NULLNULL
1921G2NULLNULL
2022G3NULLNULL
2123ADNULLNULL
2224CGNULLNULL
2325PLNULLNULL
2426ICNULLNULL
2527N1NULLNULL
2628N2NULLNULL
2729N3NULLNULL
2830N4NULLNULL
2931A1NULLNULL
3032A1NULLNULL
3133C1NULLNULL
3234S1NULLNULL
3335S2NULLNULL
3436T1NULLNULL
3537STNULLNULL
138HHD1C573AE-58C2-48D3-BB18-45C9403CF525NULL
3739G1NULLNULL
3840G2NULLNULL
3941G3NULLNULL
4042ADNULLNULL
4143CGNULLNULL
4244CGNULLNULL
July 24, 2014 at 10:52 pm
Not certain what the purpose of the RANK is there as it is not needed in the solution. Also, writing the code took a lot less time than formatting the data;-)
This is a quick solution, will work but I am certain that someone will come up with a more efficient code
😎
USE tempdb;
GO
;WITH BASE_DATA AS
(
SELECT Rank,ImportID,Indicator,MID,NumRows FROM (VALUES
(1,1,'HH','A497BEC3-9FDF-4D73-B3E0-96B11D453B5C',NULL)
,(1,2,'G1',NULL,NULL)
,(2,3,'G2',NULL,NULL)
,(3,4,'G3',NULL,NULL)
,(4,5,'AD',NULL,NULL)
,(5,6,'CG',NULL,NULL)
,(6,7,'PL',NULL,NULL)
,(7,8,'IC',NULL,NULL)
,(8,9,'N1',NULL,NULL)
,(9,10,'N2',NULL,NULL)
,(10,11,'N3',NULL,NULL)
,(11,12,'N4',NULL,NULL)
,(12,13,'A1',NULL,NULL)
,(13,14,'C1',NULL,NULL)
,(14,15,'S1',NULL,NULL)
,(15,16,'S2',NULL,NULL)
,(16,17,'T1',NULL,NULL)
,(17,18,'ST',NULL,NULL)
,(1,19,'HH','BFAD7D75-6AFF-4BCE-9589-30EE0CF52D9B',NULL)
,(18,20,'G1',NULL,NULL)
,(19,21,'G2',NULL,NULL)
,(20,22,'G3',NULL,NULL)
,(21,23,'AD',NULL,NULL)
,(22,24,'CG',NULL,NULL)
,(23,25,'PL',NULL,NULL)
,(24,26,'IC',NULL,NULL)
,(25,27,'N1',NULL,NULL)
,(26,28,'N2',NULL,NULL)
,(27,29,'N3',NULL,NULL)
,(28,30,'N4',NULL,NULL)
,(29,31,'A1',NULL,NULL)
,(30,32,'A1',NULL,NULL)
,(31,33,'C1',NULL,NULL)
,(32,34,'S1',NULL,NULL)
,(33,35,'S2',NULL,NULL)
,(34,36,'T1',NULL,NULL)
,(35,37,'ST',NULL,NULL)
,(1,38,'HH','D1C573AE-58C2-48D3-BB18-45C9403CF525',NULL)
,(37,39,'G1',NULL,NULL)
,(38,40,'G2',NULL,NULL)
,(39,41,'G3',NULL,NULL)
,(40,42,'AD',NULL,NULL)
,(41,43,'CG',NULL,NULL)
,(42,44,'CG',NULL,NULL)
) AS X(Rank,ImportID,Indicator,MID,NumRows)
)
,BASE_GROUP AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY ImportID) AS BD_RID
,ImportID
,MID
FROM BASE_DATA BD
WHERE BD.Indicator = 'HH'
)
,TO_FROM_GROUP AS
(
SELECT
BG1.ImportID
,ISNULL(BG2.ImportID - 1,LAST_ID.ImportID) AS TO_ImportID
,BG1.MID
FROM BASE_GROUP BG1
LEFT OUTER JOIN BASE_GROUP BG2
ON BG1.BD_RID = BG2.BD_RID - 1
OUTER APPLY
(SELECT MAX(ImportID) AS ImportID FROM BASE_DATA) AS LAST_ID
)
SELECT
BD.ImportID
,BD.Indicator
,TFG.MID
,BD.NumRows
FROM BASE_DATA BD
OUTER APPLY TO_FROM_GROUP TFG
WHERE BD.ImportID BETWEEN TFG.ImportID AND TFG.TO_ImportID
Results
ImportID Indicator MID NumRows
----------- --------- ------------------------------------ -----------
1 HH A497BEC3-9FDF-4D73-B3E0-96B11D453B5C NULL
2 G1 A497BEC3-9FDF-4D73-B3E0-96B11D453B5C NULL
3 G2 A497BEC3-9FDF-4D73-B3E0-96B11D453B5C NULL
4 G3 A497BEC3-9FDF-4D73-B3E0-96B11D453B5C NULL
5 AD A497BEC3-9FDF-4D73-B3E0-96B11D453B5C NULL
6 CG A497BEC3-9FDF-4D73-B3E0-96B11D453B5C NULL
7 PL A497BEC3-9FDF-4D73-B3E0-96B11D453B5C NULL
8 IC A497BEC3-9FDF-4D73-B3E0-96B11D453B5C NULL
9 N1 A497BEC3-9FDF-4D73-B3E0-96B11D453B5C NULL
10 N2 A497BEC3-9FDF-4D73-B3E0-96B11D453B5C NULL
11 N3 A497BEC3-9FDF-4D73-B3E0-96B11D453B5C NULL
12 N4 A497BEC3-9FDF-4D73-B3E0-96B11D453B5C NULL
13 A1 A497BEC3-9FDF-4D73-B3E0-96B11D453B5C NULL
14 C1 A497BEC3-9FDF-4D73-B3E0-96B11D453B5C NULL
15 S1 A497BEC3-9FDF-4D73-B3E0-96B11D453B5C NULL
16 S2 A497BEC3-9FDF-4D73-B3E0-96B11D453B5C NULL
17 T1 A497BEC3-9FDF-4D73-B3E0-96B11D453B5C NULL
18 ST A497BEC3-9FDF-4D73-B3E0-96B11D453B5C NULL
19 HH BFAD7D75-6AFF-4BCE-9589-30EE0CF52D9B NULL
20 G1 BFAD7D75-6AFF-4BCE-9589-30EE0CF52D9B NULL
21 G2 BFAD7D75-6AFF-4BCE-9589-30EE0CF52D9B NULL
22 G3 BFAD7D75-6AFF-4BCE-9589-30EE0CF52D9B NULL
23 AD BFAD7D75-6AFF-4BCE-9589-30EE0CF52D9B NULL
24 CG BFAD7D75-6AFF-4BCE-9589-30EE0CF52D9B NULL
25 PL BFAD7D75-6AFF-4BCE-9589-30EE0CF52D9B NULL
26 IC BFAD7D75-6AFF-4BCE-9589-30EE0CF52D9B NULL
27 N1 BFAD7D75-6AFF-4BCE-9589-30EE0CF52D9B NULL
28 N2 BFAD7D75-6AFF-4BCE-9589-30EE0CF52D9B NULL
29 N3 BFAD7D75-6AFF-4BCE-9589-30EE0CF52D9B NULL
30 N4 BFAD7D75-6AFF-4BCE-9589-30EE0CF52D9B NULL
31 A1 BFAD7D75-6AFF-4BCE-9589-30EE0CF52D9B NULL
32 A1 BFAD7D75-6AFF-4BCE-9589-30EE0CF52D9B NULL
33 C1 BFAD7D75-6AFF-4BCE-9589-30EE0CF52D9B NULL
34 S1 BFAD7D75-6AFF-4BCE-9589-30EE0CF52D9B NULL
35 S2 BFAD7D75-6AFF-4BCE-9589-30EE0CF52D9B NULL
36 T1 BFAD7D75-6AFF-4BCE-9589-30EE0CF52D9B NULL
37 ST BFAD7D75-6AFF-4BCE-9589-30EE0CF52D9B NULL
38 HH D1C573AE-58C2-48D3-BB18-45C9403CF525 NULL
39 G1 D1C573AE-58C2-48D3-BB18-45C9403CF525 NULL
40 G2 D1C573AE-58C2-48D3-BB18-45C9403CF525 NULL
41 G3 D1C573AE-58C2-48D3-BB18-45C9403CF525 NULL
42 AD D1C573AE-58C2-48D3-BB18-45C9403CF525 NULL
43 CG D1C573AE-58C2-48D3-BB18-45C9403CF525 NULL
44 CG D1C573AE-58C2-48D3-BB18-45C9403CF525 NULL
July 27, 2014 at 8:35 pm
Great! Thanks Eirikur - that is exactly the result I was trying to achieve. I appreciate it!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply