Блог

У меня есть такой запрос :

 SELECT        dbo.[Work].WorkId ,  dbo.Floor.RoomType
FROM            dbo.Municipality INNER JOIN
                         dbo.[Work] ON dbo.Municipality .MunicipalityID = dbo.[Work].MunicipalityId INNER JOIN
                         dbo.Look ON dbo.[Is].LookWorkId = dbo.Look.LookId INNER JOIN
                         dbo.Kat ON dbo.Look.LookId = dbo.Kat.Look_LookId
WHERE        (dbo.Look.LocationIS NOT NULL)
  

Это возвращает такой результат :

IsId — RoomType

• 5402 — 8
• 5402 — 6
• 5402 — 1
• 5402 — 2
• 5402 — 4
• 5402 — 3
• 5404 — 8
• 5404 — 6
• 5404 — 1
• 5404 — 2
• 5404 — 4

Но я хочу изменить этот результат на следующий: если IsID имеет 8 6 1 2 4 3, это будет так :

 IsId  => 5402  RoomType1 => 1  RoomType2 =>1 RoomType3 => 1 RoomType4 =>1 RoomType5 =>0 Room Type6 =>1  RoomType7 => 0  RoomType8 =>1
  

Я могу сделать это (case when dbo.Floor.RoomType=1 then 1 else 0 end) as RoomType1,

Но это не мешает ему иметь несколько записей. Как я должен изменить запрос, чтобы сделать несколько записей одной записью, как я объяснил выше? Любая помощь приветствуется. Спасибо.

Это должно сработать 🙂

Добавлен еще один столбец, как обсуждалось:

 SELECT         IsId, KayitTarihi, sum(RoomType1) As RoomType1, sum(RoomType2) As RoomType2, sum(RoomType3) As RoomType3, sum(RoomType4) As RoomType4, sum(RoomType5) As RoomType5, sum(RoomType6) As RoomType6, sum(RoomType7) As RoomType7, sum(RoomType8) As RoomType8
FROM (

SELECT     dbo.[Is].IsId, dbo.[Is].KayitTarihi, 

case dbo.Kat.OdaTipi when 1 then 1 else 0 end as RoomType1,
        case dbo.Kat.OdaTipi when 2 then 1 else 0 end as RoomType2,
        case dbo.Kat.OdaTipi when 3 then 1 else 0 end as RoomType3,
        case dbo.Kat.OdaTipi when 4 then 1 else 0 end as RoomType4,
        case dbo.Kat.OdaTipi when 5 then 1 else 0 end as RoomType5,
        case dbo.Kat.OdaTipi when 6 then 1 else 0 end as RoomType6,
        case dbo.Kat.OdaTipi when 7 then 1 else 0 end as RoomType7,
        case dbo.Kat.OdaTipi when 8 then 1 else 0 end as RoomType8

FROM            dbo.Belediye INNER JOIN
                         dbo.[Is] ON dbo.Belediye.BelediyeId = dbo.[Is].BelediyeIsId INNER JOIN
                         dbo.YerGorme ON dbo.[Is].YerGormeIsId = dbo.YerGorme.YerGormeId INNER JOIN
                         dbo.Kat ON dbo.YerGorme.YerGormeId = dbo.Kat.YerGorme_YerGormeId
WHERE        (dbo.YerGorme.Lokasyon IS NOT NULL)

) E
GROUP BY IsId, KayitTarihi
  

Поскольку у вас есть статическое количество RoomTypes, a pivot лучше всего подходит для этого типа задач. Просто замените свой запрос на то, где у меня есть select .. from @a :

 declare @a table (ID int, RoomType int);
insert into @a values
 (1,1)
,(1,3)
,(1,7)
,(1,8)
,(2,1)
,(2,2)
,(2,4)
,(2,5)
,(3,6)
,(3,8);

select ID
        ,case when [1] is not null then 1 else 0 end as RoomType1
        ,case when [2] is not null then 1 else 0 end as RoomType2
        ,case when [3] is not null then 1 else 0 end as RoomType3
        ,case when [4] is not null then 1 else 0 end as RoomType4
        ,case when [5] is not null then 1 else 0 end as RoomType5
        ,case when [6] is not null then 1 else 0 end as RoomType6
        ,case when [7] is not null then 1 else 0 end as RoomType7
        ,case when [8] is not null then 1 else 0 end as RoomType8
from(
    select ID
            ,RoomType
    from @a
    ) src
pivot
(
    max(RoomType)
    for RoomType in([1],[2],[3],[4],[5],[6],[7],[8])
) pvt;
  

В частности, в вашем случае:

 select IsId
        ,case when [1] is not null then 1 else 0 end as RoomType1
        ,case when [2] is not null then 1 else 0 end as RoomType2
        ,case when [3] is not null then 1 else 0 end as RoomType3
        ,case when [4] is not null then 1 else 0 end as RoomType4
        ,case when [5] is not null then 1 else 0 end as RoomType5
        ,case when [6] is not null then 1 else 0 end as RoomType6
        ,case when [7] is not null then 1 else 0 end as RoomType7
        ,case when [8] is not null then 1 else 0 end as RoomType8
from(
    select i.IsId
            ,k.OdaTipi as RoomType

    from dbo.Belediye b
        inner join dbo.[Is] i
            on b.BelediyeId = i.BelediyeIsId
        inner join dbo.YerGorme y
            ON i.YerGormeIsId = y.YerGormeId
        inner join dbo.Kat k
            ON y.YerGormeId = k.YerGorme_YerGormeId

    where y.Lokasyon IS NOT NULL
    ) src
pivot
(
    max(RoomType)
    for RoomType in([1],[2],[3],[4],[5],[6],[7],[8])
) pvt;
  

попробуйте цикл курсора SQL

 declare @ID int
declare @ODATIPI nvarchar(100)
DECLARE db_cursor CURSOR FOR  
SELECT        dbo.[Is].IsId as ID,  dbo.Kat.OdaTipi as ODATIPI
FROM            dbo.Belediye INNER JOIN
                         dbo.[Is] ON dbo.Belediye.BelediyeId = dbo.[Is].BelediyeIsId INNER JOIN
                         dbo.YerGorme ON dbo.[Is].YerGormeIsId = dbo.YerGorme.YerGormeId INNER JOIN
                         dbo.Kat ON dbo.YerGorme.YerGormeId = dbo.Kat.YerGorme_YerGormeId
WHERE        (dbo.YerGorme.Lokasyon IS NOT NULL)
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @ID,@ODATIPI

WHILE @@FETCH_STATUS = 0   
BEGIN   

       -- Do work

       FETCH NEXT FROM db_cursor INTO @ID,@ODATIPI
END   

CLOSE db_cursor   
DEALLOCATE db_cursor