Solución alternativa de SQL de tabla dinámica de consulta directa

Un usuario Pregunto ✅

STS_Joshua

Estoy extrayendo datos de una conexión de servidor SQL y la fuente de datos debe permanecer como consulta directa. Desafortunadamente, ahora no puedo desvincular varias columnas debido a esto. Como solución alternativa, estoy tratando de realizar la desvinculación en mi consulta SQL de la siguiente manera:

SELECT 
f.[item_number],
-(f.[mth1yearc]) AS [1/31/19],
-(f.[mth2yearc]) AS [2/28/19],
-(f.[mth3yearc]) AS [3/31/19],
-(f.[mth4yearc]) AS [4/30/19],
-(f.[mth5yearc]) AS [5/31/19],
-(f.[mth6yearc]) AS [6/30/19],
-(f.[mth7yearc]) AS [7/31/19],
-(f.[mth8yearc]) AS [8/31/19],
-(f.[mth9yearc]) AS [9/30/19],
-(f.[mth10yearc]) AS [10/31/19],
-(f.[mth11yearc]) AS [11/30/19],
-(f.[mth12yearc]) AS [12/31/19],
-(f.[mth1yearf]) AS [1/31/20],
-(f.[mth2yearf]) AS [2/29/20],
-(f.[mth3yearf]) AS [3/31/20],
-(f.[mth4yearf]) AS [4/30/20],
-(f.[mth5yearf]) AS [5/31/20],
-(f.[mth6yearf]) AS [6/30/20],
-(f.[mth7yearf]) AS [7/31/20],
-(f.[mth8yearf]) AS [8/31/20],
-(f.[mth9yearf]) AS [9/30/20],
-(f.[mth10yearf]) AS [10/31/20],
-(f.[mth11yearf]) AS [11/30/20],
-(f.[mth12yearf]) AS [12/31/20]

FROM VPP_2006SBO_STS_PROD3.dbo.FORECST1 f

UNPIVOT([Change] FOR [ChangeDate] IN ('1/31/19','2/28/19','3/31/19','4/30/19','5/31/19','6/30/19','7/31/19','8/31/19','9/30/19','10/31/19','11/30/19','12/31/19','1/31/20','2/28/20','3/31/20','4/30/20','5/31/20','6/30/20','7/31/20','8/31/20','9/30/20','10/31/20','11/30/20','12/31/20'))

Recibo el siguiente error de fuente de datos al cargar:

DataSource.Error: Microsoft SQL: Incorrect syntax near '1/31/19'.
Details:
    DataSourceKind=SQL
    DataSourcePath=stssvrsap01;sts_prod3
    Message=Incorrect syntax near '1/31/19'.
    Number=102
    Class=15

Se agradece cualquier ayuda.

Hola STS_Joshua,

Aquí está mi muestra:

CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,  
    Emp3 int, Emp4 int, Emp5 int);  
GO  
INSERT INTO pvt VALUES (1,4,3,5,4,4);  
INSERT INTO pvt VALUES (2,4,1,5,5,5);  
INSERT INTO pvt VALUES (3,4,3,5,4,4);  
INSERT INTO pvt VALUES (4,4,2,5,5,4);  
INSERT INTO pvt VALUES (5,5,1,5,5,5);  
GO  






select *  from (select VendorID, -emp1  as [2019/1/1],-Emp2  as [2019/1/2],-Emp3  as [2019/1/3],-Emp4  as [2019/1/4], -Emp5  as [2019/1/5]  from pvt) p

UNPIVOT([Change] FOR [ChangeDate] IN ([2019/1/1],[2019/1/2],[2019/1/3],[2019/1/4],[2019/1/5]))as t

Debe agregar select * from en su sintaxis, que debería funcionar.

Atentamente,
Zoe Zhi

Si esta publicación ayuda, considere Aceptarla como la solución para ayudar a los demás miembros a encontrarla más rápidamente.

Hola STS_Joshua,

Aquí está mi muestra:

CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,  
    Emp3 int, Emp4 int, Emp5 int);  
GO  
INSERT INTO pvt VALUES (1,4,3,5,4,4);  
INSERT INTO pvt VALUES (2,4,1,5,5,5);  
INSERT INTO pvt VALUES (3,4,3,5,4,4);  
INSERT INTO pvt VALUES (4,4,2,5,5,4);  
INSERT INTO pvt VALUES (5,5,1,5,5,5);  
GO  






select *  from (select VendorID, -emp1  as [2019/1/1],-Emp2  as [2019/1/2],-Emp3  as [2019/1/3],-Emp4  as [2019/1/4], -Emp5  as [2019/1/5]  from pvt) p

UNPIVOT([Change] FOR [ChangeDate] IN ([2019/1/1],[2019/1/2],[2019/1/3],[2019/1/4],[2019/1/5]))as t

Debe agregar select * from en su sintaxis, que debería funcionar.

Atentamente,
Zoe Zhi

Si esta publicación ayuda, considere Aceptarla como la solución para ayudar a los demás miembros a encontrarla más rápidamente.

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *