表数据
id result number
1 2.0 3.1
2 2.5 3.5
3 2.7 2.8想查出来
result1 number1 result2 number2 result3 number3
2.0 3.1 2.5 3.5 2.7 2.8
有没人给我解决方案呀
id result number
1 2.0 3.1
2 2.5 3.5
3 2.7 2.8想查出来
result1 number1 result2 number2 result3 number3
2.0 3.1 2.5 3.5 2.7 2.8
有没人给我解决方案呀
gocreate table t
(
id int ,
result decimal(18,1),
number decimal(18,1)
)goinsert into t
select 1, 2.0, 3.1 UNION ALL
select 2 ,2.5, 3.5 UNION ALL
select 3 ,2.7, 2.8
GOSELECT * FROM TGO
DECLARE @COLUMNS NVARCHAR(MAX) SET @COLUMNS=''
SELECT @COLUMNS=@COLUMNS+','+LTRIM(STR(result,6,1))+' AS result'+LTRIM(STR(ID)),@COLUMNS=@COLUMNS+','+LTRIM(STR(result))+' AS result'+LTRIM(STR(ID)) from T
SET @COLUMNS=SUBSTRING(@COLUMNS,2,LEN(@COLUMNS)-1)
SET @COLUMNS='SELECT '+@COLUMNS
EXEC (@COLUMNS)
IF OBJECT_ID('TEMP_TB') IS NOT NULL
DROP TABLE TEMP_TB
CREATE TABLE TEMP_TB (ID INT, Result Decimal(9,2), Number Decimal(9,2))INSERT INTO TEMP_TB (ID, Result, Number)
Select 1, 2.0, 3.1
Union All Select 2, 2.5, 3.5
Union All Select 3, 2.7, 2.8
DECLARE @SQL VARCHAR(8000)
SET @SQL = 'SELECT '
SELECT @SQL = @SQL + 'MAX(CASE ID WHEN ''' + CAST(ID AS VARCHAR) + ''' THEN Result ELSE 0 END) [Result' + CAST(ID AS VARCHAR) + '], '
+ 'MAX(CASE ID WHEN ''' + CAST(ID AS VARCHAR) + ''' THEN Number ELSE 0 END) [Number' + CAST(ID AS VARCHAR) + '],'
FROM (SELECT DISTINCT ID from TEMP_TB) AS A
SET @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) - 1) + ' FROM TEMP_TB'
EXEC(@SQL) /*
Result1 Number1 Result2 Number2 Result3 Number3
----------- ------------ ----------- ------------ ----------- ------------
2.00 3.10 2.50 3.50 2.70 2.80*/
a.Numbers Numbers1,
b.Results Result2,
b.Numbers Numbers1,
c.Results Result3,
c.Numbers Numbers3
FROM (SELECT Id, r.Results, r.Numbers FROM Re r WHERE Id = 1) a,
(SELECT Id, r.Results, r.Numbers FROM Re r WHERE Id = 2) b,
(SELECT Id, r.Results, r.Numbers FROM Re r WHERE Id = 3) c
CREATE TABLE t1
(
id INT,
result DECIMAL(5,1),
number DECIMAL(5,1)
)
INSERT INTO t1
SELECT 1, 2.0, 3.1 UNION ALL
SELECT 2, 2.5, 3.5 UNION ALL
SELECT 3, 2.7, 2.8
SELECT * FROM t1SELECT MAX(CASE WHEN id=1 THEN result ELSE 0 END) AS [result1],
MAX(CASE WHEN id=1 THEN number ELSE 0 END) AS [number1],
MAX(CASE WHEN id=2 THEN result ELSE 0 END) AS [result2],
MAX(CASE WHEN id=2 THEN number ELSE 0 END) AS [number2],
MAX(CASE WHEN id=3 THEN result ELSE 0 END) AS [result3],
MAX(CASE WHEN id=3 THEN number ELSE 0 END) AS [number3]
FROM t1--------------------------------
result1 number1 result2 number2 result3 number3
2.0 3.1 2.5 3.5 2.7 2.8
a.Number Numbers1,
b.Result Result2,
b.Number Numbers1,
c.Result Result3,
c.Number Numbers3
FROM (SELECT Id, r.Result, r.Number FROM dbo.rowandcolumn r WHERE Id = 1) a,
(SELECT Id, r.Result, r.Number FROM dbo.rowandcolumn r WHERE Id = 2) b,
(SELECT Id, r.Result, r.Number FROM dbo.rowandcolumn r WHERE Id = 3) c