Pull field based on column name

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

General SQL Server Forums

New to SQL Server Programming

Pull field based on column name

Author

Topic

jaimealvarez
Starting Member

31 Posts

Posted - 2013-05-17 : 12:05:10

Hi, I have two sets of data

ClientID RateCode
450 1
451 1
452 3
453 12

and

ClientID 1 2 3 4 5 ..... all the way to 100
450 500 550 650 780 1000
451 200 652 800 1500 3000
452 600 700 800 900 1000
453 250 500 750 1000 1200

I will like to pull the ClientID, RateCode and then depending on the clientID and RateCode pull the appropiate value. So for example for client ID 452 would be:

ClientID RateCode Rate
452 3 800

I had a CASE statement but it is very long because I have 100 different rate codes and had to go one by one, I was wondering if you could see an easier way to pull this.

Any help would be appreciated. Thank you!

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-17 : 12:55:58

Try this:
[CODE]

CREATE TABLE Clients(CustID INT, RateCode INT);
CREATE TABLE Rates(RateID INT, [1] INT, [2] INT, [3] INT, [4] INT, [5] INT);
INSERT INTO Clients(CustID, RateCode) VALUES (450, 1), (451, 1), (452, 3), (453, 5);
INSERT INTO Rates(RateID, [1], [2], [3], [4], [5]) VALUES (450, 500, 550, 650, 780, 1000),
(451, 200, 652, 800, 1500, 3000),
(452, 600, 700, 800, 900, 1000),
(453, 250, 500, 750, 1000, 1200);

DECLARE @ColumnName VARCHAR(10) = '';

SELECT @ColumnName = LTRIM(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS, Clients, Rates
WHERE TABLE_NAME = 'Rates' and COLUMN_NAME = CAST(RateCode as Varchar(2))
and CustID = 452 and CustID = RateID;

EXEC ('SELECT CustID, RateID, RateCode,' + 'Rates.' + @ColumnName + '
as Rate FROM Clients, Rates
WHERE CustID = 452 and CustID = RateID;');

DROP TABLE Clients;
DROP TABLE Rates;
[/CODE]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources