Create a specific Pivot Table from a table output table

Question

Create a specific Pivot Table from a table output table

Weegee2017

SSC Eights!

Points: 994
More actions
October 18, 2018 at 9:56 am

#367355

Hi there
I have been asked to produce a specific pivot table from the following input table
HistoryDateTime2 HistoryDateTime          IndicatorID     Status   HoldingID
15/03/2018             15/03/2018 14:49         45914831       C         1013982981
15/05/2018            15/05/2018 09:42         45914831        U         1013982981
03/04/2018             03/04/2018 13:52         45986434       C         1104423403
01/05/2018             01/05/2018 09:53        45986434        U         1104423403
15/03/2018             15/03/2018 14:46        45911140       C         1067258113
23/03/2018             23/03/2018 13:31        45911140      U          1067258113
I want to sort this so that I have one entry per holding/indicator and then display the HistoryDateTime2 entries for
each pair as DateFrom and DateTo
So I have the following
HoldingID      IndicatorID    DateFrom     DateTo
1013982981 45914831     15/03/2018   15/05/2018
1104423403 45986434     03/04/2018   01/05/2018
1104811646 45911140     15/03/2018   23/03/2018
Can someone help me do the above?
to re-create the data i did the following
IF OBJECT_ID(N'TempDB.dbo.#OutputTable')    IS NOT NULL DROP TABLE #OutputTable

Create Table #OutputTable (
[HistoryDateTime2] [varchar](20) NULL,
[HistoryDateTime] [datetime] NOT NULL,
[IndicatorID] [int] NOT NULL,
[Status] [varchar](4) NOT NULL,
[HoldingID] [int] NULL
)
GO
INSERT #OutputTable ([HistoryDateTime2], [HistoryDateTime], [IndicatorID], [Status], [HoldingID]) VALUES (N'15/03/2018', CAST(N'2018-03-15T14:46:31.270' AS DateTime), 45911140, N'C', 1067258113)
GO
INSERT #OutputTable ([HistoryDateTime2], [HistoryDateTime], [IndicatorID], [Status], [HoldingID]) VALUES (N'23/03/2018', CAST(N'2018-03-23T13:31:20.233' AS DateTime), 45911140, N'U', 1067258113)
GO
INSERT #OutputTable ([HistoryDateTime2], [HistoryDateTime], [IndicatorID], [Status], [HoldingID]) VALUES (N'15/03/2018', CAST(N'2018-03-15T14:49:31.883' AS DateTime), 45914831, N'C', 1013982981)
GO
INSERT #OutputTable ([HistoryDateTime2], [HistoryDateTime], [IndicatorID], [Status], [HoldingID]) VALUES (N'15/05/2018', CAST(N'2018-05-15T09:42:56.420' AS DateTime), 45914831, N'U', 1013982981)
GO
INSERT #OutputTable ([HistoryDateTime2], [HistoryDateTime], [IndicatorID], [Status], [HoldingID]) VALUES (N'17/07/2018', CAST(N'2018-07-17T10:36:56.497' AS DateTime), 46675746, N'C', 1104811646)
GO
INSERT #OutputTable([HistoryDateTime2], [HistoryDateTime], [IndicatorID], [Status], [HoldingID]) VALUES (N'17/07/2018', CAST(N'2018-07-17T10:36:56.497' AS DateTime), 46675755, N'C', 1104811646)
GO

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Joe Torre SSChampion Points: 10248 More actions · Answer 1

SELECT Min(o.HistoryDateTime2) BegDt , Max(o.HistoryDateTime) EndDt , o.IndicatorID , o.HoldingID FROM #OutputTable o GROUP BY o.IndicatorID , o.HoldingID;

Phil Parkin SSC Guru Points: 246985 More actions · Answer 2

Try this:
SELECT ot.HoldingID , ot.IndicatorID , DateFrom = MIN(ot.HistoryDateTime2) , DateTo = MAX(ot.HistoryDateTime2) FROM #OutputTable ot GROUP BY ot.HoldingID , ot.IndicatorID;

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Weegee2017 SSC Eights! Points: 994 More actions · Answer 3

Phil Parkin - Thursday, October 18, 2018 10:20 AM
Try this:
SELECT ot.HoldingID , ot.IndicatorID , DateFrom = MIN(ot.HistoryDateTime2) , DateTo = MAX(ot.HistoryDateTime2) FROM #OutputTable ot GROUP BY ot.HoldingID , ot.IndicatorID;

Thanks Phil

Weegee2017 SSC Eights! Points: 994 More actions · Answer 4

Joe Torre - Thursday, October 18, 2018 10:19 AM
SELECT Min(o.HistoryDateTime2) BegDt , Max(o.HistoryDateTime) EndDt , o.IndicatorID , o.HoldingID FROM #OutputTable o GROUP BY o.IndicatorID , o.HoldingID;

Thank you Joe 🙂