Please Help Iain J Unpivoted Pivot Check Each Column Succession-problem X

hi i need help problem common character "X"or"Z"
the problem is that i must use the character "X"or"Z"
i explain i need to do this
if i put the day1 = "X"or"Z" instead of day1 = 'A' in day1 it be calculated as day1 = 'A'
if i put the day11 ="X"or"Z" instead of day11 = 'B' in day11 it be calculated as day11 = 'B'
if i put the day1 = "X"or"Z" instead of day111 = 'C' in day111 it be calculated as day111 = 'C'
.....
i put example below






Iain J wrote:









Here's a query that will get you the result row you desire




Code Snippet
-- Set up sample data
declare @data table (id int, fname nvarchar(10), val int, day1 nvarchar(1), day11 nvarchar(1), day111 nvarchar(1), day2 nvarchar(1), day22 nvarchar(1), day222 nvarchar(1), day3 nvarchar(1), day33 nvarchar(1), day333 nvarchar(1), day4 nvarchar(1), day44 nvarchar(1), day444 nvarchar(1), day5 nvarchar(1), day55 nvarchar(1), day555 nvarchar(1))

insert into @data
select 111, 'aaaa', 2, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null
union all select 111, 'aaaa', 1, 'A', null, null, 'B', null, null, 'C', null, null, '-', 'C', '-', '-', '-', 'C'
union all select 222, 'bbbb', 2, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null
union all select 222, 'bbbb', 1, 'B', null, null, 'C', null, null, 'B', null, null, 'B', null, null, 'C', null, null
union all select 333, 'cccc', 2, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null
union all select 333, 'cccc', 1, 'C', null, null, '*', null, null, null, null, null, null, null, null, null, null, null
union all select 444, 'dddd', 2, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null
union all select 444, 'dddd', 1, '*', null, null, 'A', null, null, null, null, null, null, null, null, 'B', null, null
union all select 555, 'eeee', 2, '-', '-', '-', null, null, null, null, null, null, null, null, null, '-', null, 'Q'
union all select 555, 'eeee', 1, '*', '*', '*', null, null, null, null, null, null, null, null, null, 'Q', null, null

-- Query starts here
select null as id, null as fname, 0 as val,
case day1A when 0 then 'NO' when 1 then 'ok' else cast(day1A as nvarchar) end + '-A' as day1,
case day1B when 0 then 'NO' when 1 then 'ok' else cast(day1B as nvarchar) end + '-B' as day11,
case day1C when 0 then 'NO' when 1 then 'ok' else cast(day1C as nvarchar) end + '-C' as day111,
case day2A when 0 then 'NO' when 1 then 'ok' else cast(day2A as nvarchar) end + '-A' as day2,
case day2B when 0 then 'NO' when 1 then 'ok' else cast(day2B as nvarchar) end + '-B' as day22,
case day2C when 0 then 'NO' when 1 then 'ok' else cast(day2C as nvarchar) end + '-C' as day222,
case day3A when 0 then 'NO' when 1 then 'ok' else cast(day3A as nvarchar) end + '-A' as day3,
case day3B when 0 then 'NO' when 1 then 'ok' else cast(day3B as nvarchar) end + '-B' as day33,
case day3C when 0 then 'NO' when 1 then 'ok' else cast(day3C as nvarchar) end + '-C' as day333,
case day4A when 0 then 'NO' when 1 then 'ok' else cast(day4A as nvarchar) end + '-A' as day4,
case day4B when 0 then 'NO' when 1 then 'ok' else cast(day4B as nvarchar) end + '-B' as day44,
case day4C when 0 then 'NO' when 1 then 'ok' else cast(day4C as nvarchar) end + '-C' as day444,
case day5A when 0 then 'NO' when 1 then 'ok' else cast(day5A as nvarchar) end + '-A' as day5,
case day5B when 0 then 'NO' when 1 then 'ok' else cast(day5B as nvarchar) end + '-B' as day55,
case day5C when 0 then 'NO' when 1 then 'ok' else cast(day5C as nvarchar) end + '-C' as day555
from (select
sum(case when character = 'A' then 1 else 0 end)
+ sum(case when day11 = 'A' then 1 else 0 end)
+ sum(case when day111 = 'A' then 1 else 0 end) as day1A,
sum(case when day1 = 'B' then 1 else 0 end)
+ sum(case when day11 = 'B' then 1 else 0 end)
+ sum(case when day111 = 'B' then 1 else 0 end) as day1B,
sum(case when day1 = 'C' then 1 else 0 end)
+ sum(case when day11 = 'C' then 1 else 0 end)
+ sum(case when day111 = 'C' then 1 else 0 end) as day1C,
sum(case when day2 = 'A' then 1 else 0 end)
+ sum(case when day22 = 'A' then 1 else 0 end)
+ sum(case when day222 = 'A' then 1 else 0 end) as day2A,
sum(case when day2 = 'B' then 1 else 0 end)
+ sum(case when day22 = 'B' then 1 else 0 end)
+ sum(case when day222 = 'B' then 1 else 0 end) as day2B,
sum(case when day2 = 'C' then 1 else 0 end)
+ sum(case when day22 = 'C' then 1 else 0 end)
+ sum(case when day222 = 'C' then 1 else 0 end) as day2C,
sum(case when day3 = 'A' then 1 else 0 end)
+ sum(case when day33 = 'A' then 1 else 0 end)
+ sum(case when day333 = 'A' then 1 else 0 end) as day3A,
sum(case when day3 = 'B' then 1 else 0 end)
+ sum(case when day33 = 'B' then 1 else 0 end)
+ sum(case when day333 = 'B' then 1 else 0 end) as day3B,
sum(case when day3 = 'C' then 1 else 0 end)
+ sum(case when day33 = 'C' then 1 else 0 end)
+ sum(case when day333 = 'C' then 1 else 0 end) as day3C,
sum(case when day4 = 'A' then 1 else 0 end)
+ sum(case when day44 = 'A' then 1 else 0 end)
+ sum(case when day444 = 'A' then 1 else 0 end) as day4A,
sum(case when day4 = 'B' then 1 else 0 end)
+ sum(case when day44 = 'B' then 1 else 0 end)
+ sum(case when day444 = 'B' then 1 else 0 end) as day4B,
sum(case when day4 = 'C' then 1 else 0 end)
+ sum(case when day44 = 'C' then 1 else 0 end)
+ sum(case when day444 = 'C' then 1 else 0 end) as day4C,
sum(case when day5 = 'A' then 1 else 0 end)
+ sum(case when day55 = 'A' then 1 else 0 end)
+ sum(case when day555 = 'A' then 1 else 0 end) as day5A,
sum(case when day5 = 'B' then 1 else 0 end)
+ sum(case when day55 = 'B' then 1 else 0 end)
+ sum(case when day555 = 'B' then 1 else 0 end) as day5B,
sum(case when day5 = 'C' then 1 else 0 end)
+ sum(case when day55 = 'C' then 1 else 0 end)
+ sum(case when day555 = 'C' then 1 else 0 end) as day5C
from @data) x






Hope that helps

Iain