数据库版本：SQL2000，同一类材料最多有10种可能
原始数据
OrderID TypeID Material QTY
1306160001 3 3075A1 4010
1306160001 3 3075B1 2011
1306160001 3 3090A1 3024
1306160001 2 3105N1 12196
1306160001 4 4100A1 1023
1306160001 A A150B2 2314
1306160001 A A150B3 14431
1306160001 K K220A3 4162
目的数据
OrderID TypeID Material1 QTY1 Material2 QTY2 Material3 QTY3
1306160001 3 3075A1 4010 3075B1 2011 3090A1 3024
1306160001 2 3105N1 12196
1306160001 4 4100A1 1023
1306160001 A A150B2 2314 A150B3 14431
1306160001 K K220A3 4162
------解决思路----------------------
IF OBJECT_ID('tempdb.dbo.#TB')IS NOT NULL
DROP TABLE tempdb.dbo.#TB
CREATE TABLE tempdb.dbo.#TB(
ID INT IDENTITY(1,1)
,OrderID VARCHAR(50)
,TypeID INT
,Material VARCHAR(50)
,QTY INT
)
INSERT INTO #TB
SELECT * FROM 表名
DECLARE @MAXCOUNT INT
SELECT @MAXCOUNT=MAX(COUNTN)FROM(SELECT SUM(1)COUNTN FROM #TB GROUP BY OrderID,TypeID)T
DECLARE @SQL VARCHAR(8000)
SET @SQL='SELECT OrderID,TypeID'
SELECT @SQL=@SQL+',MAX(CASE WHEN RN='+CAST(number AS VARCHAR(10))+' THEN Material END)[Material'+CAST(number AS VARCHAR(10))+']'
+',MAX(CASE WHEN RN='+CAST(number AS VARCHAR(10))+' THEN QTY END)[QTY'+CAST(number AS VARCHAR(10))+']'
FROM master..spt_values
WHERE type='P'AND number>0 AND number<=@MAXCOUNT
SET @SQL=@SQL+'FROM (SELECT(SELECT COUNT(1)FROM #TB WHERE OrderID=T1.OrderID AND TypeID=T1.TypeID AND ID<=T1.ID)RN,* FROM #TB T1)T'
SET @SQL=@SQL+' GROUP BY OrderID,TypeID'
--PRINT @SQL
EXEC(@SQL)这部分在SQL2000那试下，表名改成你的表名