Sql union help

Transact-SQL
1

Hi Guys,

i am new on here , how is everyone,

i am new with sql , i am trying to create a query that will change the data structure from the below table

Date Location group Start Time End Time
09/08/2017 london Solid 01/01/1900 03:00 10/08/2017 01:42
09/08/2017 london Solid 01/01/1900 07:00 10/08/2017 05:31

to the structure below ( in a nut shell the data structure below just pivots the start and end date fields)

Date Location group type date
09/08/2017 london Solid Start Time 01/01/1900 03:00
09/08/2017 london Solid End Time 10/08/2017 01:42
10/08/2017 london Solid Start Time 01/01/1900 07:00
10/08/2017 london Solid End Time 10/08/2017 05:31

how can i create a union to do this?
i would be grateful for any help.

thank you

2

Union solution:

select [date]
      ,localtion
      ,[group]
      ,'Start Time' as [type]
      ,[start time] as [datetime]
  from yourtable
union all
select [date]
      ,localtion
      ,[group]
      ,'End Time' as [type]
      ,[end time] as [datetime]
  from yourtable
;

Non union solution:

select a.[date]
      ,a.localtion
      ,a.[group]
      ,case when b.n=0 then 'Start Time' else 'End Time' end as [type]
      ,case when b.n=0 then [start time] else [end time] end as [datetime]
  from yourtable as a
       cross apply (select * from (values(0),(1)) as tally(n)) as b
;
1 Like
3

thank you sir

4

would this also work on orcle

5

The following method should be far more efficient than the UNION ALL method...

IF OBJECT_ID('tempdb..#OriginalTable', 'U') IS NOT NULL 
DROP TABLE #OriginalTable;

CREATE TABLE #OriginalTable (
	[Date] DATE NOT NULL,
	[Location] VARCHAR(20) NOT NULL,
	[Group] VARCHAR(20) NOT NULL,
	[Start Time] DATETIME NOT NULL,
	[End Time] datetime
	);
INSERT #OriginalTable (Date, Location, [Group], [Start Time], [End Time]) VALUES 
	('09/08/2017', 'london', 'Solid', '01/01/1900 03:00', '10/08/2017 01:42'),
	('09/08/2017', 'london', 'Solid', '01/01/1900 07:00', '10/08/2017 05:31');

--========================================================================

SELECT 
	ot.Date, ot.Location, ot.[Group], d.DateType, d.NewDate
FROM
	#OriginalTable ot
	CROSS APPLY ( VALUES ('Start', ot.[Start Time]), ('End', ot.[End Time]) ) d (DateType, NewDate);

Results...

Date       Location             Group                DateType NewDate
---------- -------------------- -------------------- -------- -----------------------
2017-09-08 london               Solid                Start    1900-01-01 03:00:00.000
2017-09-08 london               Solid                End      2017-10-08 01:42:00.000
2017-09-08 london               Solid                Start    1900-01-01 07:00:00.000
2017-09-08 london               Solid                End      2017-10-08 05:31:00.000
1 Like
6

thanks only problem is i have a lot more data than the example i have shown

7

First one (union) should work on Oracle as it's pretty much gereric sql.

1 Like
8

I saw that it had the Transact SQL label... didn't notice the Oracle until you mentioned it..
I don't use Oracle, so I don't know what is or isn't valid syntax.
That said, if the CROSS APPLY ( VALUES method works, the fact that only hits the base table once, should make it more efficient there as well.

9

By "more data", do you mean more rows or more columns that need to be unpivoted?

10

thank you everyone for the help