表a结构
------------------
sn id
------------------
0003 a
0003 b
0004 a
-------------------使用SQL 语句实现如下:
----------------------
sn id id2
0003 a b
0004 a null
_____________________
谢谢!
------------------
sn id
------------------
0003 a
0003 b
0004 a
-------------------使用SQL 语句实现如下:
----------------------
sn id id2
0003 a b
0004 a null
_____________________
谢谢!
INSERT @TB
SELECT '0003', 'a' UNION ALL
SELECT '0003', 'b' UNION ALL
SELECT '0004', 'a'SELECT sn,
MAX(CASE WHEN SEQ=1 THEN id END) AS ID,
MAX(CASE WHEN SEQ=2 THEN id END) AS ID2
FROM (
SELECT *,SEQ=ROW_NUMBER() OVER (PARTITION BY sn ORDER BY id) FROM @TB
) T
GROUP BY sn
/*
sn ID ID2
---- ---- ----
0003 a b
0004 a NULL
*/
(case when id='b' then id end ) as id2
from tb
group by sn
ROW_NUMBER' 不是可以识别的 函数名。 另想请问下,OVER是什么意思,SEQ有时什么?你功底比较深,但愿能给赐教。谢谢!
DECLARE @TB TABLE([sn] VARCHAR(4), [id] VARCHAR(1))
INSERT @TB
SELECT '0003', 'a' UNION ALL
SELECT '0003', 'b' UNION ALL
SELECT '0004', 'a'SELECT IDNEW=IDENTITY(INT,1,1),* INTO #T FROM @TBSELECT SN,
MIN(CASE WHEN IDNEW=1 OR IDNEW=3 THEN ID END)ID,
MAX(CASE WHEN IDNEW=2 THEN ID END)ID2
FROM #T
GROUP BY SN SN ID ID2
---- ---- ----
0003 a b
0004 a NULL(所影响的行数为 2 行)
MAX(CASE WHEN ID='A'THEN ID END)ID,
MAX(CASE WHEN ID='B'THEN ID END)ID2
FROM @TB
GROUP BY SN
drop table TB
gocreate TABLE TB ([sn] VARCHAR(4), [id] VARCHAR(1))
INSERT into TB
SELECT '0003', 'a' UNION ALL
SELECT '0003', 'b' UNION ALL
SELECT '0004', 'a'
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([id])+'=max(case when [id]='+quotename([id],'''')+' then [id] else null end)'
from TB group by [id]exec('select [sn] '+@s+' from TB group by [sn]')/*sn a b
---- ---- ----
0003 a b
0004 a NULL
*/
if object_id('TB') is not null
drop table TB
gocreate TABLE TB ([sn] VARCHAR(4), [id] VARCHAR(1))
INSERT into TB
SELECT '0003', 'a' UNION ALL
SELECT '0003', 'b' UNION ALL
SELECT '0004', 'a'
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([id])+'=max(case when [id]='+quotename([id],'''')+' then [id] else null end)'
from TB group by [id]exec('select [sn] '+@s+' from TB group by [sn]')/*sn a b
---- ---- ----
0003 a b
0004 a NULL
*/