急!!!!SQL(高手) http://topic.csdn.net/u/20090406/18/f6fd915f-dbe7-48bd-a6ac-1f7d32abd66e.html 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 /*表TBQID Answer1 1,3,4,5,2 22,233 33,35,38,394 8轉換成表TBQID Answer1 11 31 41 52 222 233 333 353 383 394 8*/create table #TB(QID int , Answer varchar(50))insert into #TBSELECT 1, '1,3,4,5' UNION ALL SELECT 2, '22,23' UNION ALLSELECT 3, '33,35,38,39' UNION ALL SELECT 4, '8'select * from #tb/*QID Answer1 1,3,4,52 22,233 33,35,38,394 8*/--1. sql 2KSELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b --(8000 行受影响)SELECT A.QID, SUBSTRING(A.Answer, B.ID, CHARINDEX(',', A.Answer + ',', B.id) - B.id) AnswerFROM #TB A, # B WHERE SUBSTRING(',' + A.Answer, B.id, 1) = ',' /*QID Answer1 11 31 41 52 222 233 333 353 383 394 8*/DROP TABLE # --2. sql 2005 SELECT A.QID, B.value FROM( SELECT QID, Answer = CONVERT(xml,' <root> <v>' + REPLACE(Answer, ',', ' </v> <v>') + ' </v> </root>') FROM #TB )A OUTER APPLY( SELECT value = N.v.value('.', 'varchar(100)') FROM A.Answer.nodes('/root/v') N(v) )B /*QID value1 1 1 3 1 4 1 5 2 22 2 23 3 33 3 35 3 38 3 39 4 8 */drop table #TB SQL列转行的问题 请问如何将"1,2,3,"替换成对应ID的文本"张三,李四,王五" 请教一个SQL语句 查询一天中每个小时内销售数量与总数比的问题 在前台打开该存储过程,记录集rs始终待不开,从没遇到过这种问题,帮忙看看阿!急阿,谢了! 哪里有建模工具,下载给个链接好吗谢?谢! MSSQL7在WIN2000下安装的问题!(急急急!!!) 问一个关于E-R图的问题 在SQL中如何使用数组? 存储过程中取得当前插入行的自动递增列的值的问题?? 哪位仁兄用过msdb.dbo.sp_send_dbmail?麻烦进来帮忙看看 表字段合并的问题
表TB
QID Answer
1 1,3,4,5,
2 22,23
3 33,35,38,39
4 8轉換成
表TB
QID Answer
1 1
1 3
1 4
1 5
2 22
2 23
3 33
3 35
3 38
3 39
4 8
*/create table #TB(QID int , Answer varchar(50))
insert into #TB
SELECT 1, '1,3,4,5' UNION ALL
SELECT 2, '22,23' UNION ALL
SELECT 3, '33,35,38,39' UNION ALL
SELECT 4, '8'select * from #tb
/*
QID Answer
1 1,3,4,5
2 22,23
3 33,35,38,39
4 8
*/
--1. sql 2K
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b
--(8000 行受影响)
SELECT A.QID, SUBSTRING(A.Answer, B.ID, CHARINDEX(',', A.Answer + ',', B.id) - B.id) Answer
FROM #TB A, # B
WHERE SUBSTRING(',' + A.Answer, B.id, 1) = ','
/*
QID Answer
1 1
1 3
1 4
1 5
2 22
2 23
3 33
3 35
3 38
3 39
4 8
*/
DROP TABLE # --2. sql 2005
SELECT A.QID, B.value
FROM(
SELECT QID, Answer = CONVERT(xml,' <root> <v>' + REPLACE(Answer, ',', ' </v> <v>') + ' </v> </root>') FROM #TB
)A
OUTER APPLY(
SELECT value = N.v.value('.', 'varchar(100)') FROM A.Answer.nodes('/root/v') N(v)
)B
/*
QID value
1 1
1 3
1 4
1 5
2 22
2 23
3 33
3 35
3 38
3 39
4 8
*/
drop table #TB