Блог

Главная - Вопросы по программированию - Как объединить похожие столбцы внутри очень длинной таблицы?

Как объединить похожие столбцы внутри очень длинной таблицы?

#sql #aggregate

#sql #агрегировать

Вопрос:

У меня довольно сложная проблема, и я понятия не имею, как к ней подойти. Я буду рад получить несколько советов.

У меня есть очень длинная таблица, которая содержит сведения о конкретных жителях дома. Некоторые столбцы являются общими для всех резидентов, а некоторые индивидуальны. Различие заключается в наборе чисел. Например, все поля, содержащие 01 (CarMake01, Phone01 и так далее), представляют первое лицо. Все поля, содержащие 02, представляют второе лицо и так далее. Поля без номеров являются общими (город, улица и так далее).

Моя цель — запустить запрос, который берет эту длинную таблицу и создает новую таблицу, содержащую только общие поля и поля, содержащие определенные числа (01, 02 и так далее). Вот код для текущей таблицы. Надеюсь, я достаточно ясно выразился. должен ли я использовать pivot или какую-либо другую функцию?

 CREATE TABLE [dbo].[FamiliesStageA](
    [Hhid] [int] NOT NULL,
    [AssignedUser] [int] NULL,
    [Hhphone] [nvarchar](11) NULL,
    [FamilyName] [nvarchar](50) NULL,
    [City] [int] NULL,
    [Street] [int] NULL,
    [HouseNum] [int] NULL,
    [Hhsize] [int] NULL,
    [NumCar] [int] NULL,
    [CarYear] [int] NULL,
    [CarMake01] [int] NULL,
    [CarMake02] [int] NULL,
    [CarMake03] [int] NULL,
    [CarMake04] [int] NULL,
    [CarMake05] [int] NULL,
    [CarModel01] [int] NULL,
    [CarModel02] [int] NULL,
    [CarModel03] [int] NULL,
    [CarModel04] [int] NULL,
    [CarModel05] [int] NULL,
    [PersonID01] [int] NULL,
    [PersonID02] [int] NULL,
    [PersonID03] [int] NULL,
    [PersonID04] [int] NULL,
    [PersonID05] [int] NULL,
    [PersonID06] [int] NULL,
    [PersonID07] [int] NULL,
    [PersonID08] [int] NULL,
    [PesonID09] [int] NULL,
    [PesonID10] [int] NULL,
    [PersonID11] [int] NULL,
    [PersonID12] [int] NULL,
    [PersonID13] [int] NULL,
    [PersonID14] [int] NULL,
    [PersonID15] [int] NULL,
    [Phone01] [int] NULL,
    [Phone02] [int] NULL,
    [Phone03] [int] NULL,
    [Phone04] [int] NULL,
    [Phone05] [int] NULL,
    [Phone06] [int] NULL,
    [Phone07] [int] NULL,
    [Phone08] [int] NULL,
    [Phone09] [int] NULL,
    [Phone10] [int] NULL,
    [Phone11] [int] NULL,
    [Phone12] [int] NULL,
    [Phone13] [int] NULL,
    [Phone14] [int] NULL,
    [Phone15] [int] NULL,
    [FirstName01] [int] NULL,
    [FirstName02] [int] NULL,
    [FirstName03] [int] NULL,
    [FirstName04] [int] NULL,
    [FirstName05] [int] NULL,
    [FirstName06] [int] NULL,
    [FirstName07] [int] NULL,
    [FirstName08] [int] NULL,
    [FirstName09] [int] NULL,
    [FirstName10] [int] NULL,
    [FirstName11] [int] NULL,
    [FirstName12] [int] NULL,
    [FirstName13] [int] NULL,
    [FirstName14] [int] NULL,
    [FirstName15] [int] NULL,
    [SleepHome01] [int] NULL,
    [SleepHome02] [int] NULL,
    [SleepHome03] [int] NULL,
    [SleepHome04] [int] NULL,
    [SleepHome05] [int] NULL,
    [SleepHome06] [int] NULL,
    [SleepHome07] [int] NULL,
    [SleepHome08] [int] NULL,
    [SleepHome09] [int] NULL,
    [SleepHome10] [int] NULL,
    [SleepHome11] [int] NULL,
    [SleepHome12] [int] NULL,
    [SleepHome13] [int] NULL,
    [SleepHome14] [int] NULL,
    [SleepHome15] [int] NULL,
    [DateOfBirth01] [date] NULL,
    [DateOfBirth02] [date] NULL,
    [DateOfBirth03] [date] NULL,
    [DateOfBirth04] [date] NULL,
    [DateOfBirth05] [date] NULL,
    [DateOfBirth06] [date] NULL,
    [DateOfBirth07] [date] NULL,
    [DateOfBirth08] [date] NULL,
    [DateOfBirth09] [date] NULL,
    [DateOfBirth10] [date] NULL,
    [DateOfBirth11] [date] NULL,
    [DateOfBirth12] [date] NULL,
    [DateOfBirth13] [date] NULL,
    [DateOfBirth14] [date] NULL,
    [DateOfBirth15] [date] NULL,
    [License01] [int] NULL,
    [License02] [int] NULL,
    [License03] [int] NULL,
    [License04] [int] NULL,
    [License05] [int] NULL,
    [License06] [int] NULL,
    [License07] [int] NULL,
    [License08] [int] NULL,
    [License09] [int] NULL,
    [License10] [int] NULL,
    [License11] [int] NULL,
    [License12] [int] NULL,
    [License13] [int] NULL,
    [License14] [int] NULL,
    [License15] [int] NULL,
    [MainTransportMode01] [int] NULL,
    [MainTransportMode02] [int] NULL,
    [MainTransportMode03] [int] NULL,
    [MainTransportMode04] [int] NULL,
    [MainTransportMode05] [int] NULL,
    [MainTransportMode06] [int] NULL,
    [MainTransportMode07] [int] NULL,
    [MainTransportMode08] [int] NULL,
    [MainTransportMode09] [int] NULL,
    [MainTransportMode10] [int] NULL,
    [MainTransportMode11] [int] NULL,
    [MainTransportMode12] [int] NULL,
    [MainTransportMode13] [int] NULL,
    [MainTransportMode14] [int] NULL,
    [MainTransportMode15] [int] NULL

Комментарии:

1. Не понял вопрос должным образом. Что вы имеете в виду, говоря «создает новую таблицу, содержащую только общие поля и поля, содержащие определенные числа (01, 02 и так далее)».? Как должен выглядеть результат?

Ответ №1:

На самом деле это не создаст таблицу, но предоставит вам данные: я исправил несколько орфографических ошибок в вашем исходном коде (PersonID, а не PesonID), но в остальном все то же самое.

Обратите внимание, что, поскольку вы не предоставили никаких выборочных данных, и я не собирался пытаться загрузить все это, я не смог проверить это на что-либо — просмотрите его на точность и дайте мне знать, если возникнут какие-либо проблемы. Если вам не нужна временная таблица, закомментируйте ее и замените ссылки на нее вашей реальной таблицей.

 --DROP TABLE #FamiliesStageA 

CREATE TABLE #FamiliesStageA(
    [Hhid] [int] NOT NULL,
    [AssignedUser] [int] NULL,
    [Hhphone] [nvarchar](11) NULL,
    [FamilyName] [nvarchar](50) NULL,
    [City] [int] NULL,
    [Street] [int] NULL,
    [HouseNum] [int] NULL,
    [Hhsize] [int] NULL,
    [NumCar] [int] NULL,
    [CarYear] [int] NULL,
    [CarMake01] [int] NULL,
    [CarMake02] [int] NULL,
    [CarMake03] [int] NULL,
    [CarMake04] [int] NULL,
    [CarMake05] [int] NULL,
    [CarModel01] [int] NULL,
    [CarModel02] [int] NULL,
    [CarModel03] [int] NULL,
    [CarModel04] [int] NULL,
    [CarModel05] [int] NULL,
    [PersonID01] [int] NULL,
    [PersonID02] [int] NULL,
    [PersonID03] [int] NULL,
    [PersonID04] [int] NULL,
    [PersonID05] [int] NULL,
    [PersonID06] [int] NULL,
    [PersonID07] [int] NULL,
    [PersonID08] [int] NULL,
    [PersonID09] [int] NULL,
    [PersonID10] [int] NULL,
    [PersonID11] [int] NULL,
    [PersonID12] [int] NULL,
    [PersonID13] [int] NULL,
    [PersonID14] [int] NULL,
    [PersonID15] [int] NULL,
    [Phone01] [int] NULL,
    [Phone02] [int] NULL,
    [Phone03] [int] NULL,
    [Phone04] [int] NULL,
    [Phone05] [int] NULL,
    [Phone06] [int] NULL,
    [Phone07] [int] NULL,
    [Phone08] [int] NULL,
    [Phone09] [int] NULL,
    [Phone10] [int] NULL,
    [Phone11] [int] NULL,
    [Phone12] [int] NULL,
    [Phone13] [int] NULL,
    [Phone14] [int] NULL,
    [Phone15] [int] NULL,
    [FirstName01] [int] NULL,
    [FirstName02] [int] NULL,
    [FirstName03] [int] NULL,
    [FirstName04] [int] NULL,
    [FirstName05] [int] NULL,
    [FirstName06] [int] NULL,
    [FirstName07] [int] NULL,
    [FirstName08] [int] NULL,
    [FirstName09] [int] NULL,
    [FirstName10] [int] NULL,
    [FirstName11] [int] NULL,
    [FirstName12] [int] NULL,
    [FirstName13] [int] NULL,
    [FirstName14] [int] NULL,
    [FirstName15] [int] NULL,
    [SleepHome01] [int] NULL,
    [SleepHome02] [int] NULL,
    [SleepHome03] [int] NULL,
    [SleepHome04] [int] NULL,
    [SleepHome05] [int] NULL,
    [SleepHome06] [int] NULL,
    [SleepHome07] [int] NULL,
    [SleepHome08] [int] NULL,
    [SleepHome09] [int] NULL,
    [SleepHome10] [int] NULL,
    [SleepHome11] [int] NULL,
    [SleepHome12] [int] NULL,
    [SleepHome13] [int] NULL,
    [SleepHome14] [int] NULL,
    [SleepHome15] [int] NULL,
    [DateOfBirth01] [date] NULL,
    [DateOfBirth02] [date] NULL,
    [DateOfBirth03] [date] NULL,
    [DateOfBirth04] [date] NULL,
    [DateOfBirth05] [date] NULL,
    [DateOfBirth06] [date] NULL,
    [DateOfBirth07] [date] NULL,
    [DateOfBirth08] [date] NULL,
    [DateOfBirth09] [date] NULL,
    [DateOfBirth10] [date] NULL,
    [DateOfBirth11] [date] NULL,
    [DateOfBirth12] [date] NULL,
    [DateOfBirth13] [date] NULL,
    [DateOfBirth14] [date] NULL,
    [DateOfBirth15] [date] NULL,
    [License01] [int] NULL,
    [License02] [int] NULL,
    [License03] [int] NULL,
    [License04] [int] NULL,
    [License05] [int] NULL,
    [License06] [int] NULL,
    [License07] [int] NULL,
    [License08] [int] NULL,
    [License09] [int] NULL,
    [License10] [int] NULL,
    [License11] [int] NULL,
    [License12] [int] NULL,
    [License13] [int] NULL,
    [License14] [int] NULL,
    [License15] [int] NULL,
    [MainTransportMode01] [int] NULL,
    [MainTransportMode02] [int] NULL,
    [MainTransportMode03] [int] NULL,
    [MainTransportMode04] [int] NULL,
    [MainTransportMode05] [int] NULL,
    [MainTransportMode06] [int] NULL,
    [MainTransportMode07] [int] NULL,
    [MainTransportMode08] [int] NULL,
    [MainTransportMode09] [int] NULL,
    [MainTransportMode10] [int] NULL,
    [MainTransportMode11] [int] NULL,
    [MainTransportMode12] [int] NULL,
    [MainTransportMode13] [int] NULL,
    [MainTransportMode14] [int] NULL,
    [MainTransportMode15] [int] NULL)


SELECT 
    Family.HHID,
    AssignedUser,
    HHPhone,
    FamilyName,
    City,
    Street,
    HouseNum,
    HHSize,
    NumCar,
    CarYear,
    CarMake.CarMake,
    CarModel.CarModel,
    PersonID.PersonID,
    Phone.Phone,
    FirstName.FirstName,
    SleepHome.SleepHome,
    DateOfBirth.DateOfBirth,
    License.License,
    MainTransportMode.MainTransportMode
FROM 
    #FamiliesStageA Family
     INNER JOIN 
      (
        SELECT 1 AS Number UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION 
        SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION 
        SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15  
      ) Nums ON 1=1
     LEFT JOIN 
      (
        SELECT 
            HHID,
            RIGHT(UP.COL,2) AS PersonNum,
            CarMake
        FROM #FamiliesStageA
        UNPIVOT
            (CarMake FOR COL IN 
              ([CarMake01], [CarMake02], [CarMake03], [CarMake04], [CarMake05])
              ) UP
      ) CarMake ON 
        Family.HHID = CarMake.Hhid AND
        Nums.Number = CarMake.PersonNum
     LEFT JOIN 
      (
        SELECT 
            HHID,
            RIGHT(UP.COL,2) AS PersonNum,
            CarModel
        FROM #FamiliesStageA
        UNPIVOT
            (CarModel FOR COL IN 
              (
              [CarModel01], [CarModel02], [CarModel03], [CarModel04], [CarModel05])
              ) UP
      ) CarModel ON 
        Family.HHID = CarModel.Hhid AND
        Nums.Number = CarModel.PersonNum
     LEFT JOIN 
      (
        SELECT 
            HHID,
            RIGHT(UP.COL,2) AS PersonNum,
            PersonID
        FROM #FamiliesStageA
        UNPIVOT
            (PersonID FOR COL IN 
              (
              [PersonID01], [PersonID02], [PersonID03], [PersonID04], [PersonID05],
              [PersonID06], [PersonID07], [PersonID08], [PersonID09], [PersonID10],
              [PersonID11], [PersonID12], [PersonID13], [PersonID14], [PersonID15])
              ) UP
      ) PersonID ON 
        Family.HHID = PersonID.Hhid AND
        Nums.Number = PersonID.PersonNum
     LEFT JOIN 
      (
        SELECT 
            HHID,
            RIGHT(UP.COL,2) AS PersonNum,
            Phone
        FROM #FamiliesStageA
        UNPIVOT
            (Phone FOR COL IN 
              (
              [Phone01], [Phone02], [Phone03], [Phone04], [Phone05],
              [Phone06], [Phone07], [Phone08], [Phone09], [Phone10],
              [Phone11], [Phone12], [Phone13], [Phone14], [Phone15])
              ) UP
      ) Phone ON 
        Family.HHID = Phone.Hhid AND
        Nums.Number = Phone.PersonNum
     LEFT JOIN 
      (
        SELECT 
            HHID,
            RIGHT(UP.COL,2) AS PersonNum,
            FirstName
        FROM #FamiliesStageA
        UNPIVOT
            (FirstName FOR COL IN 
              (
              [FirstName01], [FirstName02], [FirstName03], [FirstName04], [FirstName05],
              [FirstName06], [FirstName07], [FirstName08], [FirstName09], [FirstName10],
              [FirstName11], [FirstName12], [FirstName13], [FirstName14], [FirstName15])
              ) UP
      ) FirstName ON 
        Family.HHID = FirstName.Hhid AND
        Nums.Number = FirstName.PersonNum
     LEFT JOIN 
      (
        SELECT 
            HHID,
            RIGHT(UP.COL,2) AS PersonNum,
            SleepHome
        FROM #FamiliesStageA
        UNPIVOT
            (SleepHome FOR COL IN 
              (
              [SleepHome01], [SleepHome02], [SleepHome03], [SleepHome04], [SleepHome05],
              [SleepHome06], [SleepHome07], [SleepHome08], [SleepHome09], [SleepHome10],
              [SleepHome11], [SleepHome12], [SleepHome13], [SleepHome14], [SleepHome15])
              ) UP
      ) SleepHome ON 
        Family.HHID = SleepHome.Hhid AND
        Nums.Number = SleepHome.PersonNum
     LEFT JOIN 
      (
        SELECT 
            HHID,
            RIGHT(UP.COL,2) AS PersonNum,
            DateOfBirth
        FROM #FamiliesStageA
        UNPIVOT
            (DateOfBirth FOR COL IN 
              (
              [DateOfBirth01], [DateOfBirth02], [DateOfBirth03], [DateOfBirth04], [DateOfBirth05],
              [DateOfBirth06], [DateOfBirth07], [DateOfBirth08], [DateOfBirth09], [DateOfBirth10],
              [DateOfBirth11], [DateOfBirth12], [DateOfBirth13], [DateOfBirth14], [DateOfBirth15])
              ) UP
      ) DateOfBirth ON 
        Family.HHID = DateOfBirth.Hhid AND
        Nums.Number = DateOfBirth.PersonNum
     LEFT JOIN 
      (
        SELECT 
            HHID,
            RIGHT(UP.COL,2) AS PersonNum,
            License
        FROM #FamiliesStageA
        UNPIVOT
            (License FOR COL IN 
              (
              [License01], [License02], [License03], [License04], [License05],
              [License06], [License07], [License08], [License09], [License10],
              [License11], [License12], [License13], [License14], [License15])
              ) UP
      ) License ON 
        Family.HHID = License.Hhid AND
        Nums.Number = License.PersonNum
     LEFT JOIN 
      (
        SELECT 
            HHID,
            RIGHT(UP.COL,2) AS PersonNum,
            MainTransportMode
        FROM #FamiliesStageA
        UNPIVOT
            (MainTransportMode FOR COL IN 
              (
              [MainTransportMode01], [MainTransportMode02], [MainTransportMode03], [MainTransportMode04], [MainTransportMode05],
              [MainTransportMode06], [MainTransportMode07], [MainTransportMode08], [MainTransportMode09], [MainTransportMode10],
              [MainTransportMode11], [MainTransportMode12], [MainTransportMode13], [MainTransportMode14], [MainTransportMode15])
      ) UP
      ) MainTransportMode ON 
        Family.HHID = MainTransportMode.Hhid AND
        Nums.Number = MainTransportMode.PersonNum
Метки: