Post date: Feb 27, 2016 9:51:25 AM
Ми маємо таблицю
-------------------------------------------------- | col1 | col2 | col3 | ------------------------------------------------- | A1 | abcd | 123|456|789 | ------------------------------------------------- | B2 | efgh | 246|369 | -------------------------------------------------- | C3 | ijk | | --------------------------------------------------
Використовуючи SQL, я хочу змінити таблцю що показана (просто поділити записи на декілька використовуючи поле col3 на декілька рядків згідно роздільника "|" ) до вигляду, як показано нижче:
-------------------------------------------------- | col1 | col2 | col3 | ------------------------------------------------- | A1 | abcd | 123 | ------------------------------------------------- | A1 | abcd | 456 | ------------------------------------------------- | A1 | abcd | 789 | ------------------------------------------------- | B2 | efgh | 246 | -------------------------------------------------- | B2 | efgh | 369 | ------------------------------------------------- | C3 | ijk | | --------------------------------------------------
Для цього В Access створюємо функціїю
Public Function Splitter(col As String, pos As Integer) As String Dim arr() As String arr = Split(col, "|") If UBound(arr) >= pos Then Splitter = arr(pos) Else Splitter = "" End If End Function
Називаємо її як
PublicFunction
Створюємо SQL запит згідно цього шаблону:
SELECT table1.col1, table1.col2, Splitter(col3,0) AS Col3
FROM table1 where Splitter(col3,0) <> ""
union
SELECT table1.col1, table1.col2, Splitter(col3,1) AS Col3
FROM table1 where Splitter(col3,1) <> ""
union
SELECT table1.col1, table1.col2, Splitter(col3,2) AS Col3
FROM table1 where Splitter(col3,2) <> ""
Отимуємо результат. Бажано щоб лишніх роздільників на початку рядка не було, того що злітає відповідність рядків.
Ось зразок робочого запиту
SELECT Da2dil.Seria, Da2dil.Number, Da2dil.Name, Da2dil.IdNumber, Da2dil.TotalArea, Da2dil.Address, Da2dil.Foundation, Da2dil.Location, Da2dil.RegNumber, Da2dil.RegDate, Da2dil.StateOrg, Da2dil.CouncilName, Da2dil.Notes, Da2dil.TimeStamp, Da2dil.LandCadNumber_prefix, Da2dil.LandArea, Da2dil.LandUnit, Da2dil.LandPurpose, Da2dil.LandPlanNumber, Da2dil.LandTotal, Da2dil.LandAgr, Da2dil.LandAreable, Da2dil.LandPlant, Da2dil.LandFeed, Da2dil.LandBuild, Da2dil.LandMeasureUnit, Da2dil.CoOwnerNumber, Da2dil.CoOwnerSeria, Da2dil.CoOwnerAnnexNumber, Da2dil.CoOwnerName, Da2dil.CoOwnerIdNumber, Da2dil.CoOwnerAddress, Da2dil.CoOwnerPart, Da2dil.Expr1, Splitter(Da2dil.LandCadNumber,0) AS Col3
FROM Da2dil where Splitter(Da2dil.LandCadNumber,0) <> ""
union
SELECT Da2dil.Seria, Da2dil.Number, Da2dil.Name, Da2dil.IdNumber, Da2dil.TotalArea, Da2dil.Address, Da2dil.Foundation, Da2dil.Location, Da2dil.RegNumber, Da2dil.RegDate, Da2dil.StateOrg, Da2dil.CouncilName, Da2dil.Notes, Da2dil.TimeStamp, Da2dil.LandCadNumber_prefix, Da2dil.LandArea, Da2dil.LandUnit, Da2dil.LandPurpose, Da2dil.LandPlanNumber, Da2dil.LandTotal, Da2dil.LandAgr, Da2dil.LandAreable, Da2dil.LandPlant, Da2dil.LandFeed, Da2dil.LandBuild, Da2dil.LandMeasureUnit, Da2dil.CoOwnerNumber, Da2dil.CoOwnerSeria, Da2dil.CoOwnerAnnexNumber, Da2dil.CoOwnerName, Da2dil.CoOwnerIdNumber, Da2dil.CoOwnerAddress, Da2dil.CoOwnerPart, Da2dil.Expr1, Splitter(Da2dil.LandCadNumber,1) AS Col3
FROM Da2dil where Splitter(Da2dil.LandCadNumber,1) <> ""
union
SELECT Da2dil.Seria, Da2dil.Number, Da2dil.Name, Da2dil.IdNumber, Da2dil.TotalArea, Da2dil.Address, Da2dil.Foundation, Da2dil.Location, Da2dil.RegNumber, Da2dil.RegDate, Da2dil.StateOrg, Da2dil.CouncilName, Da2dil.Notes, Da2dil.TimeStamp, Da2dil.LandCadNumber_prefix, Da2dil.LandArea, Da2dil.LandUnit, Da2dil.LandPurpose, Da2dil.LandPlanNumber, Da2dil.LandTotal, Da2dil.LandAgr, Da2dil.LandAreable, Da2dil.LandPlant, Da2dil.LandFeed, Da2dil.LandBuild, Da2dil.LandMeasureUnit, Da2dil.CoOwnerNumber, Da2dil.CoOwnerSeria, Da2dil.CoOwnerAnnexNumber, Da2dil.CoOwnerName, Da2dil.CoOwnerIdNumber, Da2dil.CoOwnerAddress, Da2dil.CoOwnerPart, Da2dil.Expr1, Splitter(Da2dil.LandCadNumber,2) AS Col3
FROM Da2dil where Splitter(Da2dil.LandCadNumber,2) <> ""
union
SELECT Da2dil.Seria, Da2dil.Number, Da2dil.Name, Da2dil.IdNumber, Da2dil.TotalArea, Da2dil.Address, Da2dil.Foundation, Da2dil.Location, Da2dil.RegNumber, Da2dil.RegDate, Da2dil.StateOrg, Da2dil.CouncilName, Da2dil.Notes, Da2dil.TimeStamp, Da2dil.LandCadNumber_prefix, Da2dil.LandArea, Da2dil.LandUnit, Da2dil.LandPurpose, Da2dil.LandPlanNumber, Da2dil.LandTotal, Da2dil.LandAgr, Da2dil.LandAreable, Da2dil.LandPlant, Da2dil.LandFeed, Da2dil.LandBuild, Da2dil.LandMeasureUnit, Da2dil.CoOwnerNumber, Da2dil.CoOwnerSeria, Da2dil.CoOwnerAnnexNumber, Da2dil.CoOwnerName, Da2dil.CoOwnerIdNumber, Da2dil.CoOwnerAddress, Da2dil.CoOwnerPart, Da2dil.Expr1, Splitter(Da2dil.LandCadNumber,3) AS Col3
FROM Da2dil where Splitter(Da2dil.LandCadNumber,3) <> ""
UNION SELECT Da2dil.Seria, Da2dil.Number, Da2dil.Name, Da2dil.IdNumber, Da2dil.TotalArea, Da2dil.Address, Da2dil.Foundation, Da2dil.Location, Da2dil.RegNumber, Da2dil.RegDate, Da2dil.StateOrg, Da2dil.CouncilName, Da2dil.Notes, Da2dil.TimeStamp, Da2dil.LandCadNumber_prefix, Da2dil.LandArea, Da2dil.LandUnit, Da2dil.LandPurpose, Da2dil.LandPlanNumber, Da2dil.LandTotal, Da2dil.LandAgr, Da2dil.LandAreable, Da2dil.LandPlant, Da2dil.LandFeed, Da2dil.LandBuild, Da2dil.LandMeasureUnit, Da2dil.CoOwnerNumber, Da2dil.CoOwnerSeria, Da2dil.CoOwnerAnnexNumber, Da2dil.CoOwnerName, Da2dil.CoOwnerIdNumber, Da2dil.CoOwnerAddress, Da2dil.CoOwnerPart, Da2dil.Expr1, Splitter(Da2dil.LandCadNumber,4) AS Col3
FROM Da2dil where Splitter(Da2dil.LandCadNumber,4) <> "";
Щоб зберегти результат в нову таблицю просто потрібно додати до запиту на початку і в кінці:
SELECT * INTO new_table FROM (
#QUERTY#
) a