表1(Id,Name,Value)汇总后
得到
Id,Name,Value
1,小刚,2
1,小刚,50
1,小刚,10
2,小红,50
2,小红,10但是显示效果为
1,小刚,2
,50
,10
2,小红,50
,10
希望哪位大哥给点方法!
得到
Id,Name,Value
1,小刚,2
1,小刚,50
1,小刚,10
2,小红,50
2,小红,10但是显示效果为
1,小刚,2
,50
,10
2,小红,50
,10
希望哪位大哥给点方法!
-- Author: happyflystone
-- Date:2008-12-29 15:44:25
-------------------------------------- Test Data: TA
IF OBJECT_ID('TA') IS NOT NULL
DROP TABLE TA
Go
CREATE TABLE TA(Id INT,Name NVARCHAR(2),Value INT)
Go
INSERT INTO TA
SELECT 1,'小刚',2 UNION ALL
SELECT 1,'小刚',50 UNION ALL
SELECT 1,'小刚',10 UNION ALL
SELECT 2,'小红',50 UNION ALL
SELECT 2,'小红',10
GO
--Start
;with t
as(
SELECT
*,rowid = row_number() over (partition by id order by getdate())
FROM
TA)
select case when rowid = 1 then ltrim(id) else '' end as id,
case when rowid = 1 then [name] else '' end as [name],
value
from t--Result:
/*
id name value
------------ ---- -----------
1 小刚 2
50
10
2 小红 50
10(5 行受影响)
*/
--End
INSERT @TB
SELECT 1, N'小刚', 2 UNION ALL
SELECT 1, N'小刚', 50 UNION ALL
SELECT 1, N'小刚', 10 UNION ALL
SELECT 2, N'小红', 50 UNION ALL
SELECT 2, N'小红', 10SELECT *,ID2=IDENTITY(INT,1,1) INTO # FROM @TBSELECT CASE WHEN SEQ=1 THEN RTRIM(ID) ELSE '' END AS ID,
CASE WHEN SEQ=1 THEN NAME ELSE '' END AS NAME,
VALUE
FROM (
SELECT *,SEQ=ID2-(SELECT COUNT(*) FROM # WHERE ID<A.ID) FROM # AS A
) TDROP TABLE #
/*
ID NAME VALUE
------------ ---- -----------
1 小刚 2
50
10
2 小红 50
10
*/
-- Author: happyflystone
-- Date:2008-12-29 15:44:25
-------------------------------------- Test Data: TA
IF OBJECT_ID('TA') IS NOT NULL
DROP TABLE TA
Go
CREATE TABLE TA(Id INT,Name NVARCHAR(2),Value INT)
Go
INSERT INTO TA
SELECT 1,'小刚',2 UNION ALL
SELECT 1,'小刚',50 UNION ALL
SELECT 1,'小刚',10 UNION ALL
SELECT 2,'小红',50 UNION ALL
SELECT 2,'小红',10
GO
--Start
select rowid= identity(int,1,1),*
into #
from taselect
case when rowid=(select top 1 rowid from # where id = a.id order by rowid) then ltrim(id) else '' end as id,
case when rowid=(select top 1 rowid from # where id = a.id order by rowid) then [name] else '' end as [name],
value
from # as a
drop table #--Result:
/*
id name value
------------ ---- -----------
1 小刚 2
50
10
2 小红 50
10(5 行受影响)
*/
--End