Solution to TSQL Challenge 36 - Create a graph/Chart with TSQL By Stefan_G
use sg
go
-- generate the desired values as rows first, then use FOR XML PATH to concatenate the values
-- Use ORDER BY to guarantee the correct result
select s from (
select 5 as row, '5|'+(
select
case when t1.data=5 and t2.data=5 then '_' else ' ' end
from tc36_data t1 with (nolock)
join tc36_data t2 with (nolock)
on t1.seq = t2.seq-1
order by t1.seq
for xml path(''), type
).value('.[1]','varchar(max)') as s
union all
select 4 as row, '4|'+(
select
case when t1.data=4 and t2.data=4 then '_'
else case when t1.data=4 and t2.data=5 then '/'
else case when t1.data=5 and t2.data=4 then '\'
else ' '
end end end
from tc36_data t1 with (nolock)
join tc36_data t2 with (nolock)
on t1.seq = t2.seq-1
order by t1.seq
for xml path(''), type
).value('.[1]','varchar(max)') as s
union all
select 3 as row, '3|'+(
select
case when t1.data=3 and t2.data=3 then '_'
else case when t1.data=3 and t2.data=4 then '/'
else case when t1.data=4 and t2.data=3 then '\'
else ' '
end end end
from tc36_data t1 with (nolock)
join tc36_data t2 with (nolock)
on t1.seq = t2.seq-1
order by t1.seq
for xml path(''), type
).value('.[1]','varchar(max)') as s
union all
select 2 as row, '2|'+(
select
case when t1.data=2 and t2.data=2 then '_'
else case when t1.data=2 and t2.data=3 then '/'
else case when t1.data=3 and t2.data=2 then '\'
else ' '
end end end
from tc36_data t1 with (nolock)
join tc36_data t2 with (nolock)
on t1.seq = t2.seq-1
order by t1.seq
for xml path(''), type
).value('.[1]','varchar(max)') as s
union all
select 1 as row, '1|'+(
select
case when t1.data=1 and t2.data=1 then '_'
else case when t1.data=1 and t2.data=2 then '/'
else case when t1.data=2 and t2.data=1 then '\'
else ' '
end end end
from tc36_data t1 with (nolock)
join tc36_data t2 with (nolock)
on t1.seq = t2.seq-1
order by t1.seq
for xml path(''), type
).value('.[1]','varchar(max)') as s
union all
select 0 as row, '0|'+(
select
case when t1.data=0 and t2.data=0 then '_'
else case when t1.data=0 and t2.data=1 then '/'
else case when t1.data=1 and t2.data=0 then '\'
else '_'
end end end
from tc36_data t1 with (nolock)
left join tc36_data t2 with (nolock)
on t1.seq = t2.seq-1
order by t1.seq
for xml path(''), type
).value('.[1]','varchar(max)') as s
union all
select -1 as row, ' 0'+(
select cast(seq%10 as char(1))
from tc36_data t1 with (nolock)
order by t1.seq
for xml path(''), type
).value('.[1]','varchar(max)') as s
) dt
order by row desc
option (maxdop 1)
Sr# StartTime Reads Writes CPU Duration
--- ------------------- ----------- ----------- ----------- --------
1 Dec 13 2010 3:26PM 73217 1118 2184 2.364
2 Dec 13 2010 3:53PM 73216 1118 2153 2.209
3 Dec 13 2010 4:19PM 73211 1118 2059 2.231
4 Dec 13 2010 4:45PM 73216 1118 2153 2.331
5 Dec 13 2010 5:12PM 73226 1118 2122 2.267