sqlite 学习中 draft

#!/bin/bash
# -*- tcl -*-
#The next line is executed by /bin/sh, but not tcl \
exec tclsh "$0" ${1+"$@"}

#lappend auto_path /usr/local/lib
package require sqlite3

sqlite3 db testdb
db eval {CREATE TABLE testdetails(testid, rhelid, result TEXT)}
db eval {INSERT INTO testdetails VALUES('test1', 'rhel7', 'pass')}
db eval {INSERT INTO testdetails VALUES('test2', 'rhel7', 'pass')}
db eval {INSERT INTO testdetails VALUES('test3', 'rhel7', 'pass')}
db eval {INSERT INTO testdetails VALUES('test1', 'rhel6', 'fail')}
db eval {INSERT INTO testdetails VALUES('test2', 'rhel6', 'pass')}
db eval {INSERT INTO testdetails VALUES('test1', 'rhel5', 'pass')}

db eval {select * from testdetails} {
    puts "testid=$testid rhelid=$rhelid result=$result"
}

db eval {CREATE TABLE testinfo(testid, name)}
db eval {INSERT INTO testinfo VALUES('test1', '/os/nfs')}
db eval {INSERT INTO testinfo VALUES('test2', '/os/nfs-utils')}
db eval {INSERT INTO testinfo VALUES('test3', '/os/autofs')}

set rhelList [db eval {select td.rhelid from testdetails td}]
set rhelList [lsort -unique $rhelList]
puts $rhelList
puts "======>"

set code "select
    ti.testid,
    ti.name,\n"
    foreach rhelid [lrange $rhelList 0 end-1] {
        append code "      group_concat (case when td.rhelid = '$rhelid' then td.result || ' x'   else NULL end),\n"
    }
    set rhelid [lindex $rhelList end]
    append code "       group_concat (case when td.rhelid = '$rhelid' then td.result || ' x'   else NULL end)\n"
append code "from testinfo ti
join testdetails td on
        td.testid = ti.testid
group by ti.testid, ti.name"

puts $code
puts "======>"
set x [concat {testid testname} $rhelList]
set columnNum [llength $x]

lappend x {*}[db eval $code]
set num [llength $x]
for {set i 0} {$i < $num} {incr i} {
    set c [lindex $x $i]
    puts -nonewline [format "%-20s" "{$c}"]
    if {$i%$columnNum == $columnNum-1} { puts "" }
}

列转行，，学习中，，
待解决：
Q: 为什么取出来result值都是 "0.0"  ??!!
A: 原因刚搜索 sum string , 知道了 因为SUM是处理数字的，改用group_concat就好了 ; )