Error when displaying all the column names in one single column

Question

Error when displaying all the column names in one single column

SQL Prog

SSCrazy

Points: 2020
More actions
December 7, 2011 at 9:31 am

#247903

Hello All,
I have a requirement like this. I have 15 columns. I need to get my result set like this
Name|Address|Phone.........|Total
Tom|333 red oak Dr|666666........
sam|624 St Mictchel ln|768888......
I need to get all this in one single column. I am getting the data part, But for the column headers I have give alias name as 'Name|Address|Phone.......|Total' and it is throwing an error as "The identifier is too long. The maximun size is 128". Please help me with this.
Thanks

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

SQL Prog SSCrazy Points: 2020 More actions · Answer 1

The requirement just changed. I want all the column names in my first row. How to get all the column names in the first row and then the data?...Please help. Its really urgent.

Thanks

toddasd SSCertifiable Points: 6298 More actions · Answer 2

Oh, it's urgent. Well then, let me start right away. :Whistling:

Really, what have you tried so far? Maybe if we can see an effort at a query we can piece together what you are trying to accomplish.

______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

drew.allen SSC Guru Points: 77014 More actions · Answer 3

If you are planning to export these, you're better off doing this in SSIS rather than T-SQL. In SSIS, you can specify the delimiter and automatically include headers.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

SQL Prog SSCrazy Points: 2020 More actions · Answer 4

Thanks for replying.

My code looks something like this

select

convert(varchar(500),upper(substring(ea2.last_name,1,1))+lower(substring(ea2.last_name,2,499)))

+', '+

convert(varchar(500),upper(substring(ea2.FIRST_NAME,1,1))+lower(substring(ea2.FIRST_NAME,2,499)))

+'|'+upper(ea2.Mail_LN1_ADDR)

+'|'+upper(isnull(ea2.Mail_LN2_ADDR,''))

+'|'+upper(ea2.Mail_CITY_NAME)

+'|'+upper(ea2.Mail_STATE_CODE)

+'|'+left(ea2.Mail_POSTAL_CODE,5)

+'|'+agrpt.tdu

+'|'+ea2.esiid

+'|'+agrpt.account_number

+'|'+convert(varchar,ea2.flow_start_date,101)

+'|'+convert(varchar,ea2.flow_end_date,101)

+'|'+'$'+ Convert(varchar,Convert(money,agrpt.unstatemented),1)

+'|'+'$'+ Convert(varchar,Convert(money,agrpt.not_due),1)

+'|'+'$'+ Convert(varchar,Convert(money,aged_1_10 + aged_11_30 + aged_31_60 + aged_61_90 + aged_91_120 + aged_121plus),1)

+'|'+'$'+ Convert(varchar,Convert(money,agrpt.unstatemented+ agrpt.not_due + aged_1_10 + aged_11_30 + aged_31_60 + aged_61_90 + aged_91_120 + aged_121plus),1)

from rlk_test.dbo.clean_cust_account

Thanks

SQL Prog SSCrazy Points: 2020 More actions · Answer 5

That is not possible actually.I have to do it in T-SQL :(. The requirement is like that.

drew.allen SSC Guru Points: 77014 More actions · Answer 6

srik.kotte (12/7/2011)
That is not possible actually.I have to do it in T-SQL :(. The requirement is like that.

Why is that the requirement? Is this for a class?

You want to use the best tool for the job. If you are exporting this data, the best tool for the job is SSIS hands down. Requiring you to use a tool that is not the best tool for the job is counterproductive.

If you're not exporting the data, then why are you formatting the data in this way? You're losing information like datatypes, precision, and scale by converting everything into this format. It simply doesn't make sense to format the data this way unless you are planning to export it.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

SQL Prog SSCrazy Points: 2020 More actions · Answer 7

It is actually work related. They are very rigid about this and they just want to do it in T-SQL and they need to change the date parameters in the code whenever they want to.If they change the dates then the result set of this code should give in that format so that they can export the resultset to a excel file.

Lowell SSC Guru Points: 323546 More actions · Answer 8

without an ORDER BY, there's no guarantee that this "header" will be in the order you are expecting, but it's simple:

UNION ALL

SELECT

CONVERT(VARCHAR(500),UPPER(SUBSTRING(ea2.last_name,1,1)) + LOWER(SUBSTRING(ea2.last_name,2,499)))

+ ', '

+ CONVERT(VARCHAR(500),UPPER(SUBSTRING(ea2.FIRST_NAME,1,1)) + LOWER(SUBSTRING(ea2.FIRST_NAME,2,499)))

+ '|' + UPPER(ea2.Mail_LN1_ADDR)

+ '|' + UPPER(ISNULL(ea2.Mail_LN2_ADDR,''))

+ '|' + UPPER(ea2.Mail_CITY_NAME)

+ '|' + UPPER(ea2.Mail_STATE_CODE)

+ '|' + LEFT(ea2.Mail_POSTAL_CODE,5)

+ '|' + agrpt.tdu

+ '|' + ea2.esiid

+ '|' + agrpt.account_number

+ '|' + CONVERT(VARCHAR,ea2.flow_start_date,101)

+ '|' + CONVERT(VARCHAR,ea2.flow_end_date,101)

+ '|' + '$' + CONVERT(VARCHAR,CONVERT(MONEY,agrpt.unstatemented),1)

+ '|' + '$' + CONVERT(VARCHAR,CONVERT(MONEY,agrpt.not_due),1)

+ '|' + '$' + CONVERT(VARCHAR,CONVERT(MONEY,aged_1_10 + aged_11_30 + aged_31_60 + aged_61_90 + aged_91_120 + aged_121plus),1)

+ '|' + '$' + CONVERT(VARCHAR,CONVERT(MONEY,agrpt.unstatemented + agrpt.not_due + aged_1_10 + aged_11_30 + aged_31_60 + aged_61_90 + aged_91_120 + aged_121plus),1)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

toddasd SSCertifiable Points: 6298 More actions · Answer 9

Append this before your query:

union all

Edit: Lowell got there first.

______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

SQL Prog SSCrazy Points: 2020 More actions · Answer 10

Ohh. Thanks a lot. That worked :).

I have an other issue. This is in my other code. I have declared a variable at the beginning and it works fine till certain point. After that point any statements which is using that variable is not recognizing it and it is throwing an error as must declare the variable.

I have something like this

use test

go

DECLARE @Date Date

SET @Date = '30-nov-2011'

select name, address

FROM dbo.cust

where cut_date = @date

....

Select amount, charge

FROM dbo.ldc

where cut_date=@date

The variable works for first statement but when it comes to second statement it is asking to declare. Please assist.

Thanks

Lowell SSC Guru Points: 323546 More actions · Answer 11

in the code you posted but snipped out, there is a GO statement.

variables are desroyed at every GO, so you need to remove the GO, or re-declare and re-assign the values of the variables.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQL Prog SSCrazy Points: 2020 More actions · Answer 12

Thanks again Lowell. That worked. It is a very big code and I didn't look for a GO. That was my mistake.

Thanks for your time.