Transpose and join one column SQL-CodePudding

I have a table which will be filled in the following manner:

ID	MODELID	PROPERTYID	V	Q	T	TYPE
1	LotNumber	NULL	1243582	NULL	2021-10-08 00:00:00.000	NULL
2	GoodStrips	NULL	39288	NULL	2021-10-08 00:00:00.000	NULL
3	StripProc	NULL	492	NULL	2021-10-08 00:00:00.000	NULL
4	StripRaw	NULL	883	NULL	2021-10-08 00:00:00.000	NULL
5	LabelProc	NULL	414	NULL	2021-10-08 00:00:00.000	NULL
6	LabelRaw	NULL	54	NULL	2021-10-08 00:00:00.000	NULL
7	SmallTips	NULL	101	NULL	2021-10-08 00:00:00.000	NULL
8	LongTips	NULL	65	NULL	2021-10-08 00:00:00.000	NULL

For each block of 8 rows, the timestamp will be identical.

Ideally, I'd like to make another table or view from this initial table where my lot number or timestamp would act as an ID column, and all the other values would be placed in the same row, like so:

LotNumber	GoodStrips	StripProc	StripRaw	LabelProc	LabelRaw	SmallTips	LongTips	T
1243582	39288	492	883	414	54	101	65	2021-10-08 00:00:00.000

I've been trying to get an inner join working to no avail.

My attempt at doing the first few as a test:

Select m1.T, m1.MODELID, m2.V, m3.V
from Rejects945 m1
inner join Rejects945 m2 on m2.T = m1.T 
inner join Rejects945 m3 on m3.T = m1.T
where m2.V = 'GoodStrips'
where m3.V = 'StripProc'
where MODELID = 'LotNumber'

I get the following error:

Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'where'

Any help is greatly appreciated.

CodePudding user response：

You can do this with the PIVOT function.

PIVOT & UNPIVOT

See sample code below:

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;