Output Header concatenation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Output Header concatenation

Author

Topic

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2008-06-02 : 01:52:08

In one of my stored procedue,I am selecting a column (Bookings) from a table between two dates.
I need the header of output in the following format.
'Bookings ' + ' between ' + <Date 1 > + ' and ' + <Date 2>
If i use dynamic sql, then i will be required to do lot of changes in my exising SP.

I tried lot of ways but in vain.
Can anybody help me.

Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-02 : 02:39:29

Is this for a report? Its always better to do this display & formatting at your front end application.

nr
SQLTeam MVY

12543 Posts

Posted - 2008-06-02 : 02:41:09

Not sure what your problem is.
Is it formatting the header? If so look at convert ( convert(varchar(8),@dte,112) )
Or is it getting the header into the resultset of a different format?
If so there are a few choices.
Pick a character column and put the header text in that - null for the rest.
Add a new column which is only populated for the header.
Make the output a single character column and format all rows within that.

For all of these you will need a sequnce to order the columns.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2008-06-02 : 03:15:03

I will make my query simple. For all the selected columns, you can use an alias which is displayed as header.
I want the header in following format.
'Bookings ' + ' between ' + <Date 1 > + ' and ' + <Date 2>

I tried multiple ways but SQL is not allowing to do concatenation for header name.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-02 : 03:31:53

quote:
Originally posted by pk_bohra

I will make my query simple. For all the selected columns, you can use an alias which is displayed as header.
I want the header in following format.
'Bookings ' + ' between ' + <Date 1 > + ' and ' + <Date 2>

I tried multiple ways but SQL is not allowing to do concatenation for header name.

just give a SELECT statement as

SELECT 'Bookings between' + CAST(DateField1 AS varchar(11)) + ' and ' + CAST(DateField2 AS varchar(11))

nr
SQLTeam MVY

12543 Posts

Posted - 2008-06-02 : 03:33:58

>> I will make my query simple. For all the selected columns, you can use an alias which is displayed as header
No idea what this means.

Do you mean you want the column names to appear as that text and you have 5 columns?

select [Bookings] = col1,
[between] = col2 ,
[<Date 1>] = col3 ,
[and] = col4 ,
[<Date 2>] = col5
from ...

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2008-06-02 : 03:47:16

I feel that i have not posted my requirement clearly. I am sorry for the same.

--Below is the extract of SP, i am using.

DECLARE @dFirstDate datetime
DECLARE @dLastDate datetime

SELECT @dFirstDate = '2008-05-01', @dLastDate = '2008-06-02'

SELECT * FROM bookings WHERE booking_date between @dFirstDate and @dLastDate

The output OF above query will be:
Booking_No, Booking_By, Booking_Date.
---------------------------------------------
1 157 '2008-06-01'
2 158 '2008-06-02'

--There is no issue with the resultset returned.
--The header, i want should be
Booking_No, Booking_By, Booking_Date BETWEEN '2008-05-01' AND '2008-06-02'
---------------------------------------------
1 157 '2008-06-01'
2 158 '2008-06-02'

Thanks.

nr
SQLTeam MVY

12543 Posts

Posted - 2008-06-02 : 04:18:33

Hah

declare @sql varchar(200))
select @sql =
'SELECT Booking_No, Booking_By,
[Booking_Date BETWEEN ''' + convert(varchar(8),@dFirstDate,112) + ''' AND ''' + convert(varchar(8),@dLastDate,112) + '''] = Booking_Date
FROM bookings WHERE booking_date between @dFirstDate and @dLastDate
'
exec (@sql)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources