SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Pull field based on column name
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jaimealvarez
Starting Member

31 Posts

Posted - 05/17/2013 :  12:05:10  Show Profile  Reply with Quote
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

547 Posts

Posted - 05/17/2013 :  12:55:58  Show Profile  Reply with Quote
Try this:


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;
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000