SQLTeam.com | Weblogs | Forums

Sql union help

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

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

thank you sir

would this also work on orcle

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

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

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

1 Like

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.

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

thank you everyone for the help