SQL行转列的问题!

[bluenight911]

用语句
select a.SN,a.Process_id,b.Data_id,b.MeasureData from cr_test a left join cr_data b on a.test_id = b.test_id order by a.sn,b.data_id,a.process_id
得到记录如下
sn process_id data_id measuredata
12770006 1 1 1.34
12770006 1 2 0.46
12770006 1 3 9.82
14061916 2 1 5.5
14061916 2 2 4.36
12770006 2 1 6.43
12770006 2 2 0.12
12770006 2 3 6.73
14061916 3 1 3.4
14061916 3 2 1.1

但是这不符合客户的要求,他们的要求是
sn process_id field1 field2 field3 field4
12770006 1 1.34 0.46 9.82 Null
14061916 2 5.5 4.36 Null Null
12770006 2 6.43 0.12 6.73 Null
14061916 3 3.4 1.1 Null Null

就是在原来查询记录的基础上,把measuredata按照data_id的顺序横向排列,但是需要注意的是不同的sn可能measuredata的数目不同.如12770006有3个,14061916只有2个,因此在field3,fiels4处置为Null.但最长到field4.

不知我表达清楚没有,如能解决高分相送,分有的是---------关键是项目催的紧!!!!

请给出具体的sql语句,谢谢!

[远方1609]

case........when

[bqliao]

SQL 2005 PIVOT

[bqliao]

Declare @cols nvarchar(max);

WITH CTECourse(Course)
As
(
Select Distinct Course From table1
)

Select @cols = IsNull(@cols + ',[','[') + Course + ']' From CTECourse

DECLARE @sql AS nvarchar(MAX)
SET @sql = N'SELECT *
FROM (SELECT [Name], [Course], [Grade]
FROM table1) as Header
PIVOT(SUM([Grade]) FOR [Course] IN(' + @cols + N')) AS Piv'
PRINT @sql -- for debugging
EXEC sp_executesql @sql

[云中客]

Select a.SN,a.Process_id,
[field1]=max(case when b.data_id=1 then b.measuredata end),
[field2]=max(case when b.data_id=2 then b.measuredata end),
[field3]=max(case when b.data_id=3 then b.measuredata end),
[field4]=max(case when b.data_id=4 then b.measuredata end)
From cr_test a
left join cr_data b on a.test_id = b.test_id
Group By a.SN,a.Process_id,
order by a.sn,b.data_id

[云中客]

不好意思,这里写错了:
Group By a.SN,a.Process_id,
order by a.sn,b.data_id
---------------------------
Group By a.SN,a.Process_id
order by a.SN,a.Process_id

[zlp321002]

declare @s varchar(2000)
set @s='select ta.sn,ta.process_id'
select @s=@s+',field'+ltrim(tb.data_id)+'=sum(case when tb.data_id='''+ltrim(tb.data_id)+''' then tb.measuredata else 0 end)'
from (select top 100 percent a.SN,a.Process_id,b.Data_id,b.MeasureData from cr_test a left join cr_data b on a.test_id = b.test_id order by a.sn,b.data_id,a.process_id) tb
group by tb.data_id
set @s=@s+' from (select a.SN,a.Process_id,b.Data_id,b.MeasureData from cr_test a left join cr_data b on a.test_id = b.test_id order by a.sn,b.data_id,a.process_id) ta group by ta.sn,ta.process_id'
exec(@s)

[---涛声依旧---]

同意sxycgxj(云中客)的

[云中客]

楼主的格式如果要求是固定的,而非是动态的,我想完全可以直接用一条语句来实现

[bluenight911]

select a.SN,a.Process_id,b.Data_id,b.MeasureData from cr_test a left join cr_data b on a.test_id = b.test_id order by a.sn,b.data_id,a.process_id
这个语句其实只是一个示例,其中还牵扯到很多表.因此我打算不改变原来的语句.而在原来语句查询结果的基础上再进行一次处理请问应该怎么解决???

[leo_lesley]

CREATE TABLE tb(sn varchar(20),process_id int,data_id int,measuredata numeric(9,2))
INSERT tb SELECT '12770006',1,1,1.34
UNION ALL SELECT '12770006',1,2,046
UNION ALL SELECT '12770006',1,3,9.82
UNION ALL SELECT '14061916',2,1,5.5
UNION ALL SELECT '14061916',2,2,4.36
UNION ALL SELECT '12770006',2,1,6.43
UNION ALL SELECT '12770006',2,2,0.12
UNION ALL SELECT '12770006',2,3,6.37
UNION ALL SELECT '14061916',3,1,3.4
UNION ALL SELECT '14061916',3,2,1.1

--查询处理
DECLARE @s nvarchar(4000)
--交叉报表处理代码头
SET @s='SELECT sn,process_id'
--生成列记录水平显示的处理代码拼接(处理Item列)
SELECT @s=@s
+','+QUOTENAME(data_id)
+N'=SUM(CASE data_id WHEN '+QUOTENAME(data_id,N'''')
+N' THEN measuredata END)'
FROM tb
GROUP BY data_id

--拼接交叉报表处理尾部,并且执行拼接后的动态SQL语句
EXEC(@s+N'
FROM tb
GROUP BY sn,process_id')

[zhang030219]

相当牛啊,学习

[wxh163000]

同意sxycgxj(云中客)的

[qidou71]

同意sxycgxj(云中客)

[xtra6714]

leo_lesley(leo) ,高手！

[wuhuiITren]

留名

[cool_roc]

上面有几个写得挺好的啊，为什么不给分呢？

[qq6649]

楼上又一牛人

[bnjbl]

汗 好多句看都看不懂

[kenny_appleso]

leo_lesley(leo)正解