CODEWITHSUNDEEP
sqlsql-serversql-server-2000
turbo88
turbo88

Reputation: 453

Single conditional select query in SQL

I have the following table say filenames.

filename   flag
fileA       1
fileB       0
fileC       0
fileD       1
fileA       1

I want all distinct filenames from this table AND If the flag is 1 for any file name, i want that file name to be replaced with 4 file names as FilaA_part_1, FilaA_part_2, FilaA_part_3, FilaA_part_4.

output should be 

FilaA_part_1
FilaA_part_2
FilaA_part_3
FilaA_part_4
fileB      
fileC
FilaD_part_1
FilaD_part_2
FilaD_part_3
FilaD_part_4

I am able to achieve this with temp table. I want to know if it is possible with single select query.

I could get as far as

SELECT DISTINCT CASE WHEN FLAG=1 THEN FILENAME + '_part_1' 
ELSE FILENAME END
FROM FILENAMES

Upvotes: 0

Views: 146

Answers (5)

Adamantiy
Adamantiy

Reputation: 11

Try this query.

SELECT filename + '_part' + num
FROM   FILENAMES
   CROSS JOIN (SELECT '_1' num
               UNION ALL
               SELECT '_2'
               UNION ALL
               SELECT '_3'
               UNION ALL
               SELECT '_4') A
WHERE  flag = 1
UNION ALL
SELECT filename
FROM   FILENAMES B
WHERE  NOT EXISTS (SELECT 1
               FROM   FILENAMES A
               WHERE  a.filename = b.filename
                      AND a.flag = 1)

Upvotes: 0

wiretext
wiretext

Reputation: 3342

this is a exact output which you post in Q i hope this will help you 

declare @temp table
(filenames nvarchar(44),   flag int
)
insert into @temp values ('fileA',      1)
insert into @temp values ('fileB',      0)
insert into @temp values ('fileC',      0)
insert into @temp values ('fileD',      1)
insert into @temp values ('fileA',      0);

; with cte as(
select filenames,flag from @temp t1 where not exists (select 1 from @temp t2 where t1.filenames = t2.filenames AND t2.flag = 1)
union 
select filenames + '_part_1',flag from @temp where flag = 1
union                       
select filenames + '_part_2',flag from @temp where flag = 1
union                       
select filenames + '_part_3',flag from @temp where flag = 1
union                       
select filenames + '_part_4',flag from @temp where flag = 1)

select * FROM cte

Upvotes: 0

Ashish Kumar
Ashish Kumar

Reputation: 157

declare @i varchar(10) 
set @i =1 
create table #temp ([filename] varchar(50)) 
while (@i <= 4) 
begin 
    insert into #temp([filename]) 
    SELECT distinct 
           case when flag = 1 then [filename]+'Part_'+@i 
                              else [filename] 
           end [filename] 
    FROM ( 
        SELECT 'fileA'[filename] ,1 flag 
        UNION ALL 
        SELECT 'fileB',0 
        UNION ALL 
        SELECT 'fileC', 0 
        UNION ALL 
        SELECT 'fileD', 1 
        UNION ALL 
        SELECT 'fileA' , 0 
    ) BASE 
    set @i= @i+1 
end 
select distinct [filename] from #temp

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93694

Try this should swork in sql server 2000

SELECT filename + '_part' + num
FROM   Yourtable
       CROSS JOIN (SELECT '_1' num
                   UNION ALL
                   SELECT '_2'
                   UNION ALL
                   SELECT '_3'
                   UNION ALL
                   SELECT '_4') A
WHERE  flag = 1
UNION ALL
SELECT filename
FROM   Yourtable B
WHERE  NOT EXISTS (SELECT 1
                   FROM   Yourtable A
                   WHERE  a.filename = b.filename
                          AND a.flag = 1)

SQLFIDDLE DEMO

Upvotes: 1

Paolo
Paolo

Reputation: 2254

a union should do:

SELECT FILENAME
FROM FILENAMES
WHERE FLAG = 0
UNION 
SELECT FILENAME + '_part_1'
FROM FILENAMES
WHERE FLAG = 1
UNION 
SELECT FILENAME + '_part_2'
FROM FILENAMES
WHERE FLAG = 1
UNION 
SELECT FILENAME + '_part_3'
FROM FILENAMES
WHERE FLAG = 1
UNION 
SELECT FILENAME + '_part_4'
FROM FILENAMES
WHERE FLAG = 1

Upvotes: 0

Related Questions