100分：求拆字符串组合sql语句

Question

--这样试一下！
CREATE TABLE TEST(ID INT,NAME VARCHAR(10),PartNumber VARCHAR(50),description VARCHAR(10))
INSERT TEST SELECT 1,'A','12;34;33;34','测试'
UNION ALL SELECT 2,'B','23;33;34;23','测试'CREATE PROC P_test
AS
--生成临时表
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b
--分拆处理
SELECT 
   A.id, 
   Name,
   'PartNumber'=SUBSTRING(A.PartNumber, B.id, CHARINDEX(';', A.PartNumber + ';', B.id) - B.id),
   description
INTO  #TEMP
FROM test A, # B
WHERE SUBSTRING(';' + A.PartNumber, B.id, 1) = ';'
--汇总
SELECT
  ID,
  NAME,
  PartNumber,
  COUNT(PartNumber) as Qty,
  MIN(description) AS description
FROM #TEMP 
GROUP BY ID,NAME,PartNumber
--执行存储过程
EXEC P_test 

Answer 1

xeqtrl982(Visual C# .NET) ，你的方法是可以的，感谢你；不过我把这个过程修改了一下，因为在我的应用中，还有个;字符结尾，所以会产生空格的行。
CREATE PROC P_test
AS
--生成临时表
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b
--分拆处理
SELECT 
   A.id, 
   Name,
'PartNumber'=SUBSTRING(Left(A.PartNumber,Len(A.PartNumber)-1), B.id, CHARINDEX(';', Left(A.PartNumber,Len(A.PartNumber)-1) + ';', B.id) - B.id),
   description
INTO  #TEMP
FROM test A, # B
WHERE SUBSTRING(';' + Left(A.PartNumber,Len(A.PartNumber)-1), B.id, 1) = ';'
--汇总
SELECT
  ID,
  NAME,
  PartNumber,
  COUNT(PartNumber) as Qty,
  MIN(description) AS description
FROM #TEMP 
GROUP BY ID,NAME,PartNumber
GO

Answer 2

polestarxu(一点星光) 说的对

Answer 3

DECLARE @ TABLE(Id int, Name varchar(10), PartNumber varchar(100), description nvarchar(100))
INSERT @  SELECT 1, 'a', '12;34;33;34', N'测试'
UNION ALL SELECT 2, 'b', '23;33;34;23', N'测试'SELECT 
A.Id, A.Name, B.PartNumber,
Qty = COUNT(*),
A.description
FROM(
SELECT
Id, Name, 
PartNumber = CONVERT(xml, 
N'<t>' 
+ N'<c><![CDATA['  -- 如果数据的开始有;, 则去掉这句
+ REPLACE(PartNumber, N';', N']]></c><c><![CDATA[')
+ N']]></c>'       -- 如果数据的结束有;, 则去掉这句
+ N'</t>' ),
description
FROM @
)A
OUTER APPLY(
SELECT PartNumber = T.c.value(N'.[1]', N'varchar(10)')
FROM A.PartNumber.nodes(N'/t/c') T(c)
)B
GROUP BY A.Id, A.Name, B.PartNumber, A.description-- 结果:
Id          Name       PartNumber Qty         description
----------- ---------- ---------- ----------- -------------
1           a          12         1           测试
1           a          33         1           测试
1           a          34         2           测试
2           b          23         2           测试
2           b          33         1           测试
2           b          34         1           测试(6 行受影响)

Answer 4

我写的方法只适用于 sql 2005