September 4, 2012 at 3:18 pm
Table Script
CREATE TABLE [dbo].[currency](
[currency_code] [varchar](3) NOT NULL,
[country] [varchar](256) NOT NULL,
[currency_name] [varchar](256) NULL,
[active] [int] NULL,
CONSTRAINT [PK_currency] PRIMARY KEY CLUSTERED
(
[currency_code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Insert Script
INSERT INTO [dbo].[currency]
([currency_code]
,[country]
,[currency_name]
,[active])
VALUES
('AED' ,'United Arab Emirates',NULL,0)
INSERT INTO [dbo].[currency]
([currency_code]
,[country]
,[currency_name]
,[active])
VALUES
('AFN' ,'AFN',NULL,0)
INSERT INTO [dbo].[currency]
([currency_code]
,[country]
,[currency_name]
,[active])
VALUES
('ALL' ,'ALL',NULL,0)
INSERT INTO [dbo].[currency]
([currency_code]
,[country]
,[currency_name]
,[active])
VALUES
('AMD' ,'Armenia, Drams',NULL,0)
INSERT INTO [dbo].[currency]
([currency_code]
,[country]
,[currency_name]
,[active])
VALUES
('ANG' ,'Netherlands Antilles, Guilders (also called Florins)',NULL,0)
I need to concatenate country and currency_code with equal space
My desired output is
Combined string
------------------------------------------------------------
United Arab Emirates, Dirhams AED
Afghanistan, Afghanis AFN
Albania, Leke ALL
Armenia, Drams AMD
Netherlands Antilles, Guilders (also called Florins) ANG
Space between the two string should depends on max(len(country))
Any one help me. Thanks in advance..
Regards,
Guru
September 4, 2012 at 3:25 pm
GuruGPrasad (9/4/2012)
Table ScriptCREATE TABLE [dbo].[currency](
[currency_code] [varchar](3) NOT NULL,
[country] [varchar](256) NOT NULL,
[currency_name] [varchar](256) NULL,
[active] [int] NULL,
CONSTRAINT [PK_currency] PRIMARY KEY CLUSTERED
(
[currency_code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Insert Script
INSERT INTO [dbo].[currency]
([currency_code]
,[country]
,[currency_name]
,[active])
VALUES
('AED' ,'United Arab Emirates',NULL,0)
INSERT INTO [dbo].[currency]
([currency_code]
,[country]
,[currency_name]
,[active])
VALUES
('AFN' ,'AFN',NULL,0)
INSERT INTO [dbo].[currency]
([currency_code]
,[country]
,[currency_name]
,[active])
VALUES
('ALL' ,'ALL',NULL,0)
INSERT INTO [dbo].[currency]
([currency_code]
,[country]
,[currency_name]
,[active])
VALUES
('AMD' ,'Armenia, Drams',NULL,0)
INSERT INTO [dbo].[currency]
([currency_code]
,[country]
,[currency_name]
,[active])
VALUES
('ANG' ,'Netherlands Antilles, Guilders (also called Florins)',NULL,0)
I need to concatenate country and currency_code with equal space
My desired output is
Combined string
------------------------------------------------------------
United Arab Emirates, Dirhams AED
Afghanistan, Afghanis AFN
Albania, Leke ALL
Armenia, Drams AMD
Netherlands Antilles, Guilders (also called Florins) ANG
Space between the two string should depends on max(len(country))
Any one help me. Thanks in advance..
Regards,
Guru
I'm sorry, but I am lost on what you are asking for here.
September 4, 2012 at 3:26 pm
Nice job posting ddl and sample data. I don't understand what you want for output.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 4, 2012 at 3:33 pm
I need to display country and currency_code in a single column.
select country + space(46-Len(country)) + CONVERT(varchar(max), currency_code ) from currency order by country
Afghanistan, Afghanis -AFN
Albania, Leke -ALL
Algeria, Algeria Dinars -DZD
Angola, Kwanza -AOA
Argentina, Pesos -ARS
Armenia, Drams -AMD
Regards
Guru
September 4, 2012 at 3:40 pm
GuruGPrasad (9/4/2012)
I need to display country and currency_code in a single column.select country + space(46-Len(country)) + CONVERT(varchar(max), currency_code ) from currency order by country
Afghanistan, Afghanis -AFN
Albania, Leke -ALL
Algeria, Algeria Dinars -DZD
Angola, Kwanza -AOA
Argentina, Pesos -ARS
Armenia, Drams -AMD
Regards
Guru
You do know that the longest country in your table is 53 characters long, right?
September 4, 2012 at 3:43 pm
Two things. One, your code would simply look like this:
select
country + replicate(' ', 56 - len(country)) + currency_code from dbo.currency order by country;
Two, be sure to use a fixed space font, not a proportional font, to display the output.
September 4, 2012 at 3:45 pm
FYI, I posted the output I generated into Notepad and it looks good, the currency codes are all lined up nicely.
September 4, 2012 at 3:54 pm
Thanks Lynn Pettis
Max column length may vary.
And your code
select
country + replicate(' ', 56 - len(country)) + currency_code from dbo.currency order by country;
does not suit my needs.
Please find the attachment
Regards
Guru
September 4, 2012 at 4:06 pm
GuruGPrasad (9/4/2012)
Thanks Lynn PettisMax column length may vary.
And your code
select
country + replicate(' ', 56 - len(country)) + currency_code from dbo.currency order by country;
does not suit my needs.
Please find the attachment
Regards
Guru
What attachment, and I just followed what you posted:
GuruGPrasad (9/4/2012)
I need to display country and currency_code in a single column.select country + space(46-Len(country)) + CONVERT(varchar(max), currency_code ) from currency order by country
Afghanistan, Afghanis -AFN
Albania, Leke -ALL
Algeria, Algeria Dinars -DZD
Angola, Kwanza -AOA
Argentina, Pesos -ARS
Armenia, Drams -AMD
Regards
Guru
September 5, 2012 at 7:29 am
GuruGPrasad (9/4/2012)
Thanks Lynn PettisMax column length may vary.
And your code
select
country + replicate(' ', 56 - len(country)) + currency_code from dbo.currency order by country;
does not suit my needs.
Please find the attachment
Regards
Guru
This screams of a report to me. This type of formatting should be done in the front end. It is far easier in the front end than using t-sql to format data. If you have to use t-sql for this you will need to use dynamic sql.
Something like this:
declare @MaxLen int
select @MaxLen = MAX(len(country)) + 2 from currency
declare @sql varchar(max)
select @sql = 'select CAST(country as CHAR(' + cast(@MaxLen as varchar(3)) + ')) + currency_code from currency order by country'
exec (@sql)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply