转换并连接一列SQL

Question

我有一个表格，将以以下方式填写：

>类型NULL2021-10-08 00:00:00.000>NULLNULLNULLNULLNULL

ID	MODELID	PROPERTYID	V	Q	T
1	LotNumber	NULL	1243582	NULL		2021-10-08 00:00:00.000
2	GoodStrips	NULL	39288	NULL	NULL
3	StripProc	NULL	492	NULL[/td>	2021-10-08 00:00:00.000
4	StripRaw	NULL	883	NULL		2021-10-08 00:00:00.000	空
5	LabelProc	NULL	414	2021-10-08 00:00:00.000	NULL
	LabelRaw	NULL	54	NULL	2021-10-08 00:00:00.000
7	小提示	NULL	101	2021-10-08 00:00:00.000	NULL
8	长提示	NULL	65	2021-10-08 00:00:00.000		NULL

您可以使用PIVOT函数来完成此操作。

PIVOT&UNPIVOT

参见下面的示例代码：

CREATE TABLE #Test
(
ID INT
,MODELID VARCHAR(100)
,V INT
,T DATETIME
)
INSERT  #Test (ID, MODELID, V, T)
VALUES (1,'LotNumber',1243582,'8/10/2021  12:00:00 AM')
,(2,'GoodStrips',39288,'8/10/2021  12:00:00 AM')
,(3,'StripProc',492,'8/10/2021  12:00:00 AM')
,(4,'StripRaw',883,'8/10/2021  12:00:00 AM')
,(5,'LabelProc',414,'8/10/2021  12:00:00 AM')
,(6,'LabelRaw',54,'8/10/2021  12:00:00 AM')
,(7,'SmallTips',101,'8/10/2021  12:00:00 AM')
,(8,'LongTips',65,'8/10/2021  12:00:00 AM')
,(9,'LotNumber',2345234,'9/10/2021  12:00:00 AM')
,(10,'GoodStrips',4543,'9/10/2021  12:00:00 AM')
,(11,'StripProc',455,'9/10/2021  12:00:00 AM')
,(12,'StripRaw',43,'9/10/2021  12:00:00 AM')
,(13,'LabelProc',24,'9/10/2021  12:00:00 AM')
,(14,'LabelRaw',5,'9/10/2021  12:00:00 AM')
,(15,'SmallTips',2,'9/10/2021  12:00:00 AM')
,(16,'LongTips',666,'9/10/2021  12:00:00 AM')

select LotNumber
,GoodStrips
,StripProc
,StripRaw
,LabelProc
,LabelRaw
,SmallTips
,LongTips
,t
from
(
select v, MODELID, t
from #Test
) d
pivot
(
max(v)
for MODELID in (LotNumber
,GoodStrips
,StripProc
,StripRaw
,LabelProc
,LabelRaw
,SmallTips
,LongTips
)
) piv;