现在有这些数据bookid type grade
1 01 0
1 02 2
1 03 1
2 02 1
2 01 0
..............
..........
想输出结果:
bookid type
1 01:03:02
2 01:02
type是按grade升序连接的。-----------请高手帮忙!!!现在我根据网友们提供的结果但是已然在我这里不能实现,请高手帮我改进一下。
网友提供:
if not object_id('tb') is null
drop table tb
Go
Create table tb([bookid] int,[type] nvarchar(2),[grade] int)
Insert tb
select 1,N'01',0 union all
select 1,N'02',2 union all
select 1,N'03',1 union all
select 2,N'02',1 union all
select 2,N'01',0
Go
Select [bookid],
[type]=stuff((select ':'+[type]
from tb
where [bookid]=t.[bookid] order by [grade]
for xml path('')),1,1,'')
from tb t
group by [bookid]我用的sql 2008 报错:在关键字 'for' 附近有语法错误。
请帮忙解决,谢谢各位了!
1 01 0
1 02 2
1 03 1
2 02 1
2 01 0
..............
..........
想输出结果:
bookid type
1 01:03:02
2 01:02
type是按grade升序连接的。-----------请高手帮忙!!!现在我根据网友们提供的结果但是已然在我这里不能实现,请高手帮我改进一下。
网友提供:
if not object_id('tb') is null
drop table tb
Go
Create table tb([bookid] int,[type] nvarchar(2),[grade] int)
Insert tb
select 1,N'01',0 union all
select 1,N'02',2 union all
select 1,N'03',1 union all
select 2,N'02',1 union all
select 2,N'01',0
Go
Select [bookid],
[type]=stuff((select ':'+[type]
from tb
where [bookid]=t.[bookid] order by [grade]
for xml path('')),1,1,'')
from tb t
group by [bookid]我用的sql 2008 报错:在关键字 'for' 附近有语法错误。
请帮忙解决,谢谢各位了!
IF OBJECT_ID('tempdb.dbo.#tb2') IS NOT NULL DROP TABLE #tb2
CREATE TABLE #tb2 (qid INT,mid VARCHAR(1))
INSERT INTO #tb2
SELECT 1,'h' UNION ALL
SELECT 1,'s' UNION ALL
SELECT 1,'a' UNION ALL
SELECT 1,'b' UNION ALL
SELECT 3,'c' UNION ALL
SELECT 3,'d' UNION ALL
SELECT 3,'e' UNION ALL
SELECT 4,'f' UNION ALL
SELECT 4,'g' UNION ALL
SELECT 6,'h' DECLARE @sql nvarchar(4000);
SET @sql = N'(';
SELECT @sql = @sql + ' MAX(CASE WHEN b.rowid = ' + RTRIM(number+1) + ' THEN b.MID ELSE ''''END) +'' ''+' FROM master.dbo.spt_values
WHERE type = 'p' AND number <(SELECT TOP 1 COUNT(1) FROM #tb2 GROUP BY qid ORDER BY COUNT(1) DESC)SET @sql = (SUBSTRING(@sql,1,LEN(@sql)-6));
--PRINT @sql
EXECUTE('SELECT qid, '+@sql+') as [cellid] FROM (SELECT rowid=ROW_NUMBER() OVER(PARTITION BY qid ORDER BY qid),*
FROM #tb2) AS B group by b.qid')--RESULT
/*
qid cellid
----------- -------
1 h s a b
3 c d e
4 f g
6 h
(4 行受影响)
*/
http://topic.csdn.net/u/20100826/09/95f853a0-b21c-42ea-90d4-d9dcb4ff343f.html?32444
这里已近解决了。