Since you want to use PIVOT to get the result and you need to to this dynamically, I would always suggest writing a static PIVOT query first, this allows you to get the syntax correct before trying to convert it to dynamic SQL.
The desired output shows that you want to PIVOT on two columns, RoomNo and Beds - as a result you'll need to unpivot these columns first, then apply the pivot.
Your current query is on the right track, you do need to use row_number() so you can get the number of rooms/beds on each floor - but your unpivot is using Beds and HotelSeqID. You don't want to unpivot HotelSeqID because that doesn't have a value that you eventually want as a new column.
I'd start a static version the following way - first the subquery to get the data from your tables vertically with the row_number() included:
select h.HotelName,
r.FloorNo,
r.RoomNo,
r.Beds,
seq = row_number() over(partition by h.HotelName, r.FloorNo
order by r.RoomNo)
from dbo.Hotels h
inner join dbo.HotelRooms r
on h.seqid = r.hotelseqid
See SQL Fiddle with Demo. Your data will look like this with a new column that contains the sequence number based on the HotelName and FloorNo:
| HOTELNAME | FLOORNO | ROOMNO | BEDS | SEQ |
|---------------------------|---------|--------|------|-----|
| Hotel 1 | 1 | 101 | 1 | 1 |
| Hotel 1 | 1 | 102 | 2 | 2 |
| Hotel 1 | 1 | 103 | 1 | 3 |
| Hotel 1 | 1 | 104 | 2 | 4 |
| Hotel 1 | 2 | 201 | 1 | 1 |
| Hotel 1 | 2 | 202 | 2 | 2 |
Now, you can unpivot the RoomNo and Beds columns into multiple rows. Since you are using SQL Server 2008 you can use CROSS APPLY to get the result. The query will be:
select hr.HotelName,
hr.FloorNo,
col = c.col + '_' + cast(seq as varchar(2)),
c.val
from
(
select h.HotelName,
r.FloorNo,
r.RoomNo,
r.Beds,
seq = row_number() over(partition by h.HotelName, r.FloorNo
order by r.RoomNo)
from dbo.Hotels h
inner join dbo.HotelRooms r
on h.seqid = r.hotelseqid
) hr
cross apply
(
select 'RoomNo', RoomNo union all
select 'Beds', Beds
) c (col, val);
See SQL Fiddle with Demo. Your data has now been transformed into multiple columns:
| HOTELNAME | FLOORNO | COL | VAL |
|---------------------------|---------|----------|-----|
| Hotel 1 | 1 | RoomNo_1 | 101 |
| Hotel 1 | 1 | Beds_1 | 1 |
| Hotel 1 | 1 | RoomNo_2 | 102 |
| Hotel 1 | 1 | Beds_2 | 2 |
| Hotel 1 | 1 | RoomNo_3 | 103 |
| Hotel 1 | 1 | Beds_3 | 1 |
| Hotel 1 | 1 | RoomNo_4 | 104 |
| Hotel 1 | 1 | Beds_4 | 2 |
| Hotel 1 | 2 | RoomNo_1 | 201 |
Finally, you can pivot to get the final result.
select HotelName, FloorNo,
RoomNo_1, Beds_1, RoomNo_2, Beds_2,
RoomNo_3, Beds_3, RoomNo_4, Beds_4
from
(
select hr.HotelName,
hr.FloorNo,
col = c.col + '_' + cast(seq as varchar(2)),
c.val
from
(
select h.HotelName,
r.FloorNo,
r.RoomNo,
r.Beds,
seq = row_number() over(partition by h.HotelName, r.FloorNo
order by r.RoomNo)
from dbo.Hotels h
inner join dbo.HotelRooms r
on h.seqid = r.hotelseqid
) hr
cross apply
(
select 'RoomNo', RoomNo union all
select 'Beds', Beds
) c (col, val)
) d
pivot
(
max(val)
for col in (RoomNo_1, Beds_1, RoomNo_2, Beds_2,
RoomNo_3, Beds_3, RoomNo_4, Beds_4)
) piv
order by HotelName, FloorNo;
See SQL Fiddle with Demo. Once you've tested a static version to make sure it gets your the desired result, you can easily convert this into dynamic SQL.
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(col + '_' + cast(seq as varchar(2)))
from
(
select seq = row_number() over(partition by h.HotelName, r.FloorNo
order by r.RoomNo)
from dbo.Hotels h
inner join dbo.HotelRooms r
on h.seqid = r.hotelseqid
) d
cross apply
(
select 'RoomNo', 1 union all
select 'Beds', 2
) c (col, so)
group by col, so, seq
order by seq, so
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = N'SELECT HotelName, FloorNo,' + @cols + N'
from
(
select hr.HotelName,
hr.FloorNo,
col = c.col + ''_'' + cast(seq as varchar(2)),
c.val
from
(
select h.HotelName,
r.FloorNo,
r.RoomNo,
r.Beds,
seq = row_number() over(partition by h.HotelName, r.FloorNo
order by r.RoomNo)
from dbo.Hotels h
inner join dbo.HotelRooms r
on h.seqid = r.hotelseqid
) hr
cross apply
(
select ''RoomNo'', RoomNo union all
select ''Beds'', Beds
) c (col, val)
) x
pivot
(
max(val)
for col in (' + @cols + N')
) p
order by HotelName, FloorNo'
exec sp_executesql @query
See SQL Fiddle with Demo. This query will give you the result:
| HOTELNAME | FLOORNO | ROOMNO_1 | BEDS_1 | ROOMNO_2 | BEDS_2 | ROOMNO_3 | BEDS_3 | ROOMNO_4 | BEDS_4 | ROOMNO_5 | BEDS_5 | ROOMNO_6 | BEDS_6 |
|---------------------------|----------|-----------|--------|----------|--------|----------|--------|----------|--------|----------|--------|----------|--------|
| Hotel 1 | 1 | 101 | 1 | 102 | 2 | 103 | 1 | 104 | 2 | (null) | (null) | (null) | (null) |
| Hotel 1 | 2 | 201 | 1 | 202 | 2 | 203 | 1 | 204 | 2 | 205 | 1 | 206 | 2 |
| Hotel 2 | 1 | 101 | 4 | 102 | 6 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
| Hotel 2 | 2 | 201 | 2 | 202 | 7 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
Since the seconds are from 0 (1970-01-01 00:00:00 UTC), you should look for every multiple of 60
SELECT * FROM mytable WHERE MOD(TimeStamp,60)=0;
or if TimeStamp is indexed, you can do
SELECT T.* FROM
(SELECT TimeStamp FROM mytable WHERE MOD(TimeStamp,60)=0) M
INNER JOIN mytable T USING (TimeStamp);
Give it a Try !!!
SUGGESTION #1
You should store the timestamp of the minute and index it
ALTER TABLE mytable ADD COLUMN MinuteTimeStamp AFTER TimeStamp;
UPDATE mytable SET MinuteTimeStamp = TimeStamp - MOD(TimeStamp,60);
ALTER TABLE mytable ADD INDEX MinuteTimeStamp_UniqueKey_ndx (MinuteTimeStamp,UniqueKey);
Then, you can do MIN aggregation on MinuteTimeStamp.
SELECT MinuteTimeStamp,MIN(UniqueKey) UniqueKey
FROM mytable GROUP BY MinuteTimeStamp;
and use it get those records
SELECT B.* FROM
(SELECT MinuteTimeStamp,MIN(UniqueKey) UniqueKey
FROM mytable GROUP BY MinuteTimeStamp) A
INNER JOIN mytable B USING (UniqueKey);
It was tactfully pointed out that triggers would degrade performance
Perhaps doing INSERTs like this may help
INSERT INTO mytable (UniqueKey,TimeStamp,MinuteTimeStamp) VALUES
(
uniquevalue,
UNIX_TIMESTAMP(NOW()),
UNIX_TIMESTAMP(NOW() - INTERVAL SECOND(NOW()) SECOND)
);
SUGGESTION #2
Since you have over 1000 columns (Ugh), perhaps a table of those minute timestamps would be better.
CREATE TABLE MinuteKeys
(
MinuteTimeStamp INT UNSIGNED NOT NULL,
UniqueKey INT UNSIGNED NOT NULL,
PRIMARY KEY (UniqueKey)
KEY MinuteTimeStamp_UniqueKey_ndx (MinuteTimeStamp,UniqueKey)
) ENGINE=MyISAM;
ALTER TABLE MinuteKeys DISABLE KEYS;
INSERT INTO MinuteKeys SELECT TimeStamp - MOD(TimeStamp,60),UniqueKey FROM mytable;
ALTER TABLE MinuteKeys ENABLE KEYS;
Then, you could use that table for the aggregation
SELECT B.* FROM
(SELECT MinuteTimeStamp,MIN(UniqueKey) UniqueKey
FROM MinuteKeys GROUP BY MinuteTimeStamp) A
INNER JOIN mytable B USING (UniqueKey);
EPILOGUE
Other suggestions are possible but you should really consider normalization of the table
See my post Too many columns in MySQL as to why
Best Answer
Regarding your FULL OUTER JOIN question (not working in MySQL): you can probably achieve a result similar to that of a FULL OUTER join by coding a UNION of a LEFT and RIGHT join. However, looking at your raw data and your "Desired Output", it may be better to use pivoting.
Consider the following - just for generating a test table that resembles your "data" table (using MySQL version 5.1):
We get a table that looks like this: (middle section not displayed)
If we now "extend" and "pivot" (see http://stratosprovatopoulos.com/web-development/mysql/pivot-a-table-in-mysql/ ) and use MySQL's group_concat() function ( see https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html ), we get a result that may be useful for you.
Output of select * from qa_pivot;