查询到fromat表SQL Server 2005 [英] Query to fromat table SQL server 2005

问题描述

PCBA_FSN     MFGDATE       MACHINE POSITION    Vcc     Vcon        Test1       Test2       Test3       Test4
Hello, 

please help me to format this table from :

1124400708  8/3/2011 1:00:16 PM FCT01       1               5.015       NULL        NULL        NULL        NULL        NULL
1124400708  8/3/2011 1:00:17 PM FCT01       1               NULL        3.016       NULL        NULL        NULL        NULL
1124400708  8/3/2011 1:00:18 PM FCT01       1               NULL        NULL        OK      NULL        NULL        NULL
1124400708  8/3/2011 1:00:21 PM FCT01       1               NULL        NULL        NULL        OK      NULL        NULL
1124400708  8/3/2011 1:00:23 PM FCT01       1               NULL        NULL        NULL        NULL        OK      NULL
1124400708  8/3/2011 1:00:24 PM FCT01       1               NULL        NULL        NULL        NULL        NULL        OK


become :

PCBA_FSN	  MFGDATE		MACHINE	POSITION	Vcc		Vcon		Test1  		Test2  		Test3  		Test4 		
1124400708	8/3/2011 1:00:16 PM	FCT01     	1       	  	5.015		3.016		OK		OK		OK		OK
 
your help are much appreciated

Regards,
ateoka

推荐答案

创建一个临时表，并用所需数据填充每个记录.完成之后，截断上面的表并从您的临时表中插入.放下您的临时表.

创建临时表后，您可以通过首先插入
来创建新记录

create a temporary table and fill each record with the wanted data. After that as been done, truncate the above table and insert from your temporary table. Drop your temporary table.

After the temporary table is created you can create a new record by first inserting

INSERT INTO TEMPTABLE
Select distinct PCBA_FSN from Original Table

对于每个PCBA_FSN的每个字段，您可以通过声明
来更新值

for each field per PCBA_FSN you be update the value by stating

UPDATE TempTable
   SET field = (Select field from OriginalTable where PCBA_FSN = OriginalTable.PCBA_FSN and OriginalTable.field is not null)

设置完所有字段后:

after all fields have been set:

truncate originalTable

然后:

Then:

<pre>INSERT INTO OriginalTable
Select * from TEMPTABLE</pre>

然后:

Then:

DROP TABLE TEMPTABLE

这篇关于查询到fromat表SQL Server 2005的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！