GuruGPrasad (9/4/2012)

---

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

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

GuruGPrasad (9/4/2012)

---

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

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

GuruGPrasad (9/4/2012)

---

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

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)