This is relatively trivial to do with a correlated subquery. You can't use the COALESCE method highlighted in the blog post you mention unless you extract that to a user-defined function (or unless you only want to return one row at a time). Here is how I typically do this:
DECLARE @x TABLE
(
id INT,
row_num INT,
customer_code VARCHAR(32),
comments VARCHAR(32)
);
INSERT @x SELECT 1,1,'Dilbert','Hard'
UNION ALL SELECT 1,2,'Dilbert','Worker'
UNION ALL SELECT 2,1,'Wally','Lazy';
SELECT id, customer_code, comments = STUFF((SELECT ' ' + comments
FROM @x AS x2 WHERE id = x.id
ORDER BY row_num
FOR XML PATH('')), 1, 1, '')
FROM @x AS x
GROUP BY id, customer_code
ORDER BY id;
If you have a case where the data in comments could contain unsafe-for-XML characters (>, <, &), you should change this:
FOR XML PATH('')), 1, 1, '')
To this more elaborate approach:
FOR XML PATH(''), TYPE).value(N'(./text())[1]', N'varchar(max)'), 1, 1, '')
(Be sure to use the right destination data type, varchar or nvarchar, and the right length, and prefix all string literals with N if using nvarchar.)
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) |
Best Answer
You must pivot your data.
This can be done using the Pivot operator:
You are using SQL Server 2005 and therefore the total is calculated using
CROSS APPLY. See SQL Fiddle.Without
PIVOT, you can also use aGROUP BYwithCASEs:See SQL Fiddle.
With SQL Server 2012 you could easily calculate the total using the
SUM(...) OVER(...)window function:See SQL Fiddle.
Note that a window function with an aggregate such as
SUM(...) OVER(...)also works with SQL Server 2005 when theOVER(...)clause only contains aPARTITION BY ...and noORDER BY .... See OVER Clause (2005) (Thanks Andriy M for the link).Output: