How to eliminate multiple entries from SQL

Question

I have a query like this :

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)

This returns such a result :

IsId - RoomType

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

But I want to change this result to this : If IsID has 8 6 1 2 4 3, It will be like this :

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

I can do this (case when dbo.Floor.RoomType=1 then 1 else 0 end) as RoomType1,

But this doesn't prevent it from having multiple entries. How I should change query to make multiple entries as one entry as I explained above? Any help is appreciated. Thanks.

Answer 1

This should work :-)

Added one more column as discussed:

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

Answer 2

As you have a static number of RoomTypes, a pivot is best suited to this type of task. Simply substitute your query into where I have my 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;

---

Specifically in your case:

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;

Answer 3

try SQL Cursor Loop

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